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 gt  Tabelle wurde erstellt   ora10g gt  alter table t add constraint ck check n gt 0    Tabelle wurde ge  ndert   ora10g gt  alter table t modify constraint ck check n lt 0   alter table t modify constraint ck check n lt 0                                       FEHLER in Zeile 1  ORA-00933  SQL-Befehl wurde nicht korrekt beendet

User · Accepted Answer

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  lt  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

User · Answer

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

User · Answer

NO  you can t do it other way than so

User · Answer

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

[oracle] how to modify an existing check constraint?

Examples related to oracle

Examples related to ora-00933