oracle 如何修改现有的检查约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5077658/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to modify an existing check constraint?
提问by schurik
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
回答by Adam Musch
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 novalidateclause 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.
该enable novalidate子句将强制插入或更新以强制执行约束，但不会强制对表进行全表扫描以验证所有行是否符合要求。
回答by Witold Kaczurba
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.
- 约束总是存在的
- 现有行不违反新约束
- 在删除约束之后和应用新约束之前，不会尝试非法的 INSERT/UPDATE。
回答by Oleg Danu
NO, you can't do it other way than so.
不，你不能这样做。
回答by Jon Heller
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.)
不。如果存在这样的功能，它将在此语法说明中列出。（尽管可能存在未记录的 SQL 功能，或者可能有一些我不知道的包。）
