如何修改现有的检查约束？

是否有任何方法可以修改表上的现有检查约束 除了掉落并重新创建它 ？

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

216248 次浏览

小开

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

小开

最佳答案

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.

小开

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

小开

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.