As Tom suggested, in 10G regexp_like will make it easier...
Kirill, March 29, 2006 - 4:22 pm UTC
Here is the 10G version:
alter table t add constraint wwddd check (regexp_like (x, '[[:alpha:]]{2}[[:digit:]]{3}'));
SQL> insert into t values ( 'ab123' );
1 row created.
SQL> insert into t values ( 'b123' );
insert into t values ( 'b123' )
*
ERROR at line 1:
ORA-02290: check constraint (WWDDD) violated
SQL> insert into t values ( 'ab23' );
insert into t values ( 'ab23' )
*
ERROR at line 1:
ORA-02290: check constraint (WWDDD) violated
Note that this does enforce match of exactly 2 letters and 3 numbers.
If you want to relax the match count enforcement, you can use
{m,} Matches at least m times or
{m,n} Matches at least m times but no more than n times
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_posix001.htm#i690819
Speedwise, it appears to be the same.
I ran sqlldr with 200000 rows, all failing the constraint, with rows=10000 (so no actual inserts).
With translate-based constraint the times reported by sqlldr were:
Elapsed time was: 00:01:04.43
CPU time was: 00:00:14.54
With regexp-based constraint:
Elapsed time was: 00:01:08.32
CPU time was: 00:00:14.11
Kirill
Great!
Yannick, April 01, 2006 - 1:15 pm UTC
Thanks Tom, Thanks Kirill!
Yannick
Check Constraint Vs Master Table
Sandeep, February 13, 2007 - 6:51 am UTC
Hi Tom,
I have a basic question on which I would like to get your views.
For a table
CREATE TABLE BANK (PAYMENT_MODE VARCHAR2(10));
I could either have a check constraint to say that the values would be BNK,CHQ,SWT etc
or
I could create a new table as below. populate it with BNK,CHQ,SWT and then reference it in the BANK table as foreign key.
CREATE TABLE Payment
(
payment_mode VARCHAR2(10) primary key
,description VARCHAr2(100)
,upd_by VARCHAR2(30) default user
,upd_on date default sysdate
);
Which one of the two are better, or which one would you choose. Is there any rule by which you decide when to just have a constraint and when to have a master table and a reference to it ?
Regards
Sandeep
February 13, 2007 - 10:01 am UTC
do you anticipate the need to add more payment_modes over time?
if so, the table makes sense.
why not use check constraints
GMA, October 12, 2009 - 12:53 pm UTC
I have a question about check constraints.
CREATE TABLE X
(VENDOR_ID NUMBER(10),
RANK NUMBER);
ALTER TABLE X ADD CONSTRAINT ABC CHECK (VENDOR_ID = 0)
Two years later, the business says they now have three more permissible vendor IDs for this table(and of course as with most things, this can increase over time)
We have two options -
a) Change the check constraint so it now allows four values
b) Create a mapping table that has the permissible vendor IDs.
I cannot use a foreign key to the vendor table because it will have all vendors.
My gut tells me that we should not use a check constraint here, but I don't have a technical reason why. The only thing I can think of is that a change to this involves DDL, which in turn invalidates all calling objects.