how to modify an existing check constraint?

Question

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

Answer 1

You have to drop it and recreate it, but you don't have to incur the cost of revalidating the data if you don't want to.

alter table t drop constraint ck ;
alter table t add constraint ck check (n < 0) enable novalidate;

The enable novalidate clause will force inserts or updates to have the constraint enforced, but won't force a full table scan against the table to verify all rows comply.