0

I am new at SQLPlus and I want to enforce a CHECK CONSTRAINT on a column that stores names.

I want the constraint to not allow for names to be entered that starts with a Q.

this is what I have:

ALTER TABLE table1 ADD CONSTRAINT table1_name_ck CHECK( name, substr(1,1) ='q');

I am getting the errors:

cannot validate (USER1.TABLE1_NAME_CK) - check constraint violated

AND errors

CHECK( name, substr(1,1) ='q')
        *
ERROR at line 2:
ORA-00920: invalid relational operator.

I cannot figure out how to fix these errors or find the correct way to implement this. Any advice would be appreciated! Thanks!. I don't think this code work for lowercase q and uppercase Q but I'm just trying that out for now.

truffle
  • 455
  • 1
  • 9
  • 17

1 Answers1

2

You need to pass the column to the substr call:

ALTER TABLE table1 
   ADD CONSTRAINT table1_name_ck CHECK( substr(name, 1,1) ='q' );

To make this work for upper and lowercase you need to enhance this a bit:

ALTER TABLE table1 
   ADD CONSTRAINT table1_name_ck CHECK( substr(name, 1,1) NOT IN ('q','Q') );
Dumpcats
  • 453
  • 5
  • 21
  • `not in`, surely? The question says *not* allow starting with Q, despite the attempts using `=`. The OP's use of `=` instead of `!=` might cause the 'cannot validate' error too, at least if that constraint had been valid... – Alex Poole Jul 15 '14 at 22:26
  • Thank you! I tried that and it is still giving me the cannot validate -check constraint violated error? Do you know how to resolve that? – truffle Jul 15 '14 at 22:27
  • That means you have values in that column that do not start with `q` or `Q` –  Jul 15 '14 at 22:30