Wednesday, March 15, 2017

How to add a check constraint to a table using ALTER TABLE

The following statement adds a check constraint to the table and allows Oracle to give it a system generated name:
ALTER TABLE MYTABLE ADD 
 CHECK (COL1 IS NOT NULL)
 ENABLE
 VALIDATE;

Note that when you execute the following DDL, Oracle will throw ORA-02438: Column check constraint cannot reference other columns:
ALTER TABLE MYTABLE ADD
  CONSTRAINT CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;

The above error only happens when you let Oracle give the constraint a system generated name.


If you are explisitly naming your constraint, the use of the CONSTRAINT keyword will work:
ALTER TABLE MYTABLE ADD
  CONSTRAINT mytable_cc2 CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;

No comments:

Post a Comment