Adding constraint on an existing table with CHECK

Question

Can anyone have a look at my SQL statement and tell me what is wrong with it. I keep getting error when I tried to add a new constraint on an existing table.

Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"
*Cause:    
*Action:

Note: these expressions look suspiciously like regular expressions: [^A-L] - they will not work with the LIKE operator how you may think they will. You may need to use REGEXP_LIKE (and remove the %s). — Jeffrey Kemp
– Jeffrey Kemp, Commented Dec 16, 2014 at 6:31

Gordon Linoff · Accepted Answer · 2014-12-16 03:25:37Z

3

You need parentheses around the entire condition for check:

alter table seat 
    add constraint check_seats
        CHECK ((BLOCKNO = 'FRONT' AND ROWNO NOT LIKE '%[^A-J]%'  AND SEATNO <=40) OR 
               (BLOCKNO = 'MIDDLE' AND ROWNO NOT LIKE '%[^A-L]%' AND SEATNO <=50) OR
               (BLOCKNO = 'BACK' AND ROWNO NOT LIKE '%[^A-N]%' AND SEATNO <=60)
              );

answered Dec 16, 2014 at 3:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@NashTamu . . . It is unusual that you thanked me for this answer but chose the other answer. Of course, as the OP, you have the right to accept whichever answer you like.

Ferose · Accepted Answer · 2014-12-16 03:43:09Z

3

Add one more parenthesis for the check constraint.

     CHECK ((BLOCKNO = 'FRONT' AND ROWNO NOT LIKE '%[^A-J]%'  AND SEATNO <=40) OR 
            (BLOCKNO = 'MIDDLE' AND ROWNO NOT LIKE '%[^A-L]%' AND SEATNO <=50) OR
            (BLOCKNO = 'BACK' AND ROWNO NOT LIKE '%[^A-N]%' AND SEATNO <=60)
           );

answered Dec 16, 2014 at 3:43

Ferose

3845 silver badges13 bronze badges

1 Comment

APC Over a year ago

'%[^A-J]%' mixes regex and non-regex syntax. Consequently the NOT LIKE will always be true, regardless of the value of ROWNO.

Collectives™ on Stack Overflow

Adding constraint on an existing table with CHECK

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related