I want to make a constraint on a column so i can only insert the letters M, F or T. I tried to make one with REGEXP_LIKE but i get errors.
ALTER TABLE [TABLE_NAME] ADD CONSTRAINT [CONSTR_NAME] CHECK (REGEXP_LIKE (COL, '^[M-M,F-F,T-T])\w+')); 
Add a comment |
1 Answer
You can do this as:
ALTER TABLE [TABLE_NAME] ADD CONSTRAINT [CONSTR_NAME] CHECK (REGEXP_LIKE(COL, '^[MFT]*$')); That is, the column has any of the letters M, F, or T occurring any number of times. These are the only characters allowed from the beginning of the string (^) to the end ($).
Note: An empty string does not match this pattern because it is equivalent to NULL (by default) in Oracle.
I should also note that your question is a little ambiguous. If the value can only be one of M, F, or T, then use IN:
ALTER TABLE [TABLE_NAME] ADD CONSTRAINT [CONSTR_NAME] CHECK (COL IN ('M', 'F', 'T') );