Is There A Database Engine that Allows for Queriab

Question

I'm looking for a database engine that can handle data constraints specified via RegEx. So in addition to the datatype, I want to be able to control the format of the data. E.g. a varchar(255) field could be further restrained to be like [a-zA-Z0-9 ].

I need the RegEx to be able to be queried too, so I can share those constraints throughout the n-tier system to enforce on several levels. E.g. MySQL allows for querying of information_schema to get meta data, and other database engines have similar ways.

I did a post yesterday (MySQL Queriable Field Constraint by RegEx), referencing things I read, but doesn't look promising with MySQL, so I'm opening this up to any db engine, although I would prefer MS SQL, Oracle, DB2 or MySQL, as it'll be easier to sell the business on.

Is there a database engine out there that allows for these regex restrictions? If so, which one is it and how do the constraints get set and queried?

Answer 1

In Oracle you can specify custom constraints, in which you can use functions that evaluate regexp; for example:

SQL> create table test_pattern ( txt varchar2(1000))
  2  /

Table created.

SQL> alter table test_pattern add constraint check_pattern check (regexp_instr(txt, '^START') != 0)
  2  /

Table altered.

SQL> insert into test_pattern values ('START a d f  g ')
  2  /

1 row created.

SQL> insert into test_pattern values ('_START a d f  g ')
  2  /
insert into test_pattern values ('_START a d f  g ')
*
ERROR at line 1:
ORA-02290: check constraint (SIUINTEGRA.CHECK_PATTERN) violated

You can get informations on constraints you set with something like:

select *
from dba_constraints       
where table_name = 'TEST_PATTERN'

Answer 2

Here is a wildcard example in sql server for this.

create table #Something
(
    SomeValue varchar(255)
    , constraint MyCheck CHECK (SomeValue like '[a-z][a-z][0-9]%')
)

insert #Something
select 'ab3adoofnod'
--(1 row(s) affected)

insert #Something
select 'a3b3adoofnod'

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "MyCheck". The conflict occurred in database "tempdb", table "dbo.#Something__________________________________________________________________________________________________________0000000000DD", column 'SomeValue'.
The statement has been terminated.

If you want to use some t-sql to view the definitions of your check constraints you can use the sys.check_constraints catalog view.

Here is an example to view ALL the check constraints for the table above. The definition columns will provide the wildcard searching as defined in the constraint.

select * 
from tempdb.sys.check_constraints
where parent_object_id = object_id('tempdb..#Something')