how to modify an existing check constraint?
OracleOra 00933Oracle Problem Overview
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
Oracle Solutions
Solution 1 - Oracle
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.
Solution 2 - Oracle
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.
Solution 3 - Oracle
NO, you can't do it other way than so.
Solution 4 - Oracle
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.)