[oracle] how to modify an existing check constraint?

Is there any way to modify an existing check constraint on a table other than dropping and re-creating it?

create table t ( n number);
ora10g> Tabelle wurde erstellt.

ora10g> alter table t add constraint ck check(n>0);

Tabelle wurde geõndert.

ora10g> alter table t modify constraint ck check(n<0);
alter table t modify constraint ck check(n<0)
                                   *
FEHLER in Zeile 1:
ORA-00933: SQL-Befehl wurde nicht korrekt beendet

This question is related to oracle ora-00933

The answer is


Create a new constraint first and then drop the old one.
That way you ensure that:

  • constraints are always in place
  • existing rows do not violate new constraints
  • no illegal INSERT/UPDATEs are attempted after you drop a constraint and before a new one is applied.

NO, you can't do it other way than so.


No. If such a feature existed it would be listed in this syntax illustration. (Although it's possible there is an undocumented SQL feature, or maybe there is some package that I'm not aware of.)