Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Yannick.

Asked: March 29, 2006 - 8:04 am UTC

Last updated: February 13, 2007 - 10:01 am UTC

Version: 9.2

Viewed 1000+ times

You Asked

Hi Tom,

Just a simple question for you please :

How would you go to make sure a varchar2 column contains only let's says 2 letters (any letters) followed by 3 numbers ?
(AA123 is allowed but not 12AAA...)

Would you use check constraints ? Triggers ?
Could you please give us an example!

Thanks a lot for your time and your great site!



and Tom said...

check constraint - never never use a trigger if you can do it declaritively.

In 10g, regular expressions make this easier, but in 9i, you could use:

ops$tkyte@ORA9IR2> create table t
2 ( x varchar2(5) check ( translate(upper(substr( x,1,2)),
3 '1ABCDEFGHIJKLMNOPQRSTUVWXYZ','1') is null
4 and
5 translate(substr(x,3,3),'A0123456789', 'A')
is null )
6 )
7 /

Table created.

Elapsed: 00:00:00.02
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 'ab123' );

1 row created.

Elapsed: 00:00:00.00
ops$tkyte@ORA9IR2> insert into t values ( 'b123' );
insert into t values ( 'b123' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C002400) violated


Elapsed: 00:00:00.00
ops$tkyte@ORA9IR2> insert into t values ( 'ab23' );

1 row created.

Elapsed: 00:00:00.01
ops$tkyte@ORA9IR2> insert into t values ( '12abc' );
insert into t values ( '12abc' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C002400) violated


If you need to make sure the number is 3 digits long at least - you'd add a length check.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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
Tom Kyte
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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library