oracle-databaseora-00933

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

Solution

  • 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.