oracle 更改表启用 novalidate 约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42902315/
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
Alter table enable novalidate constraint
提问by Neeraj
I am trying to add UNIQUE KEYon a previously existing table with duplicate records by setting ENABLE NOVALIDATE.
我正在尝试UNIQUE KEY通过设置添加具有重复记录的先前存在的表ENABLE NOVALIDATE。
But I am getting ORA-02299: cannot validate (my_owner.my_key_UK ) - duplicate keys found
但我越来越 ORA-02299: cannot validate (my_owner.my_key_UK ) - duplicate keys found
ALTER TABLE my_owner.my_table
ADD CONSTRAINT my_key_UK UNIQUE (ID1,ID2)
ENABLE NOVALIDATE;
回答by APC
A unique constraint uses an index to enforce the noduplicates rule. By default it will create a unique index (makes sense right?). It is this index creation which is hurling ORA-02299.
唯一约束使用索引来强制执行 noduplicates 规则。默认情况下，它会创建一个唯一索引（有意义吗？）。正是这个索引创建正在抛出ORA-02299。
However, if this is an existing index on the constrained columns the constraint will use that. The good news is, the index doesn't need to be unique for the constraint to use it.
但是，如果这是受约束列上的现有索引，则约束将使用该索引。好消息是，索引不需要是唯一的约束来使用它。
So what you need to do is build a non-unique index first:
所以你需要做的是先建立一个非唯一索引：
create index whatever_idx on my_table (ID1,ID2);
Then you will be able to create your constraint:
然后你将能够创建你的约束：
ALTER TABLE my_owner.my_table
ADD CONSTRAINT my_key_UK UNIQUE (ID1,ID2)
ENABLE NOVALIDATE;
You can check this by querying the data dictionary:
您可以通过查询数据字典来检查这一点：
select uc.constraint_name
, uc.constraint_type
, uc.index_name
, ui.uniqueness as idx_uniqueness
from user_constraints uc
join user_indexes ui
on ui.index_name = uc.index_name
where uc.table_name = 'MY_TABLE'
回答by Arkadiusz ?ukasiewicz
Oracle ensure unique values using indexes. And if you create unique constrains db automatic creates unique index. Workaround is add DEFERRABLEoptions. In this case oracle creates normal index. Check example.
Oracle 使用索引确保唯一值。如果您创建唯一约束，db 会自动创建唯一索引。解决方法是添加DEFERRABLE选项。在这种情况下，oracle 创建普通索引。检查示例。
create table table_abc (a number,b number);
insert into table_abc values(1,1);
insert into table_abc values(1,2);
ALTER TABLE table_abc ADD CONSTRAINT my_key_a UNIQUE (a) DEFERRABLE enable novalidate; -- no error but in table nonunique values
ALTER TABLE table_abc ADD CONSTRAINT my_key_b UNIQUE (b) ENABLE NOVALIDATE; --no error
select * from user_indexes where table_name ='TABLE_ABC';
