在 oracle 中删除然后添加约束失败
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/843242/
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
Dropping then adding a constraint fails in oracle
提问by mR_fr0g
I'm trying to move a primary key constraint to a different column in oracle. I tried this:
我正在尝试将主键约束移动到 oracle 中的不同列。我试过这个:
ALTER TABLE MY_TABLE
DROP CONSTRAINT c_name;
ALTER TABLE MY_TABLE
ADD CONSTRAINT c_name PRIMARY KEY
(
"COLUMN_NAME"
) ENABLE;
This fails on the add constraint with an error saying the the constraint already exists even though i just dropped it. Any ideas why this is happening
这在添加约束上失败,并出现错误,指出该约束已经存在,即使我刚刚删除了它。为什么会发生这种情况的任何想法
回答by Thomas Jones-Low
If the original constraint was a primary key constraint, Oracle creates an index to enforce the constraint. This index has the same name as the constraint (C_NAME in your example). You need to drop the index separately from the constraint. So you will need to do a :
如果原始约束是主键约束,Oracle 创建一个索引来强制执行该约束。此索引与约束同名(在您的示例中为 C_NAME)。您需要将索引与约束分开删除。所以你需要做一个:
ALTER TABLE <table1> DROP CONSTRAINT C_NAME;
DROP INDEX C_NAME;
ALTER TABLE <table1> ADD CONSTRAINT C_NAME PRIMARY KEY
( COLUMN_2 ) ENABLE;
回答by Pop
The safest way is to first add a unique index. Try this:
最安全的方法是先添加唯一索引。尝试这个:
create unique index new_pk on <tabname> (column_2);
Then drop the old PK:
然后放下旧的PK:
alter table <tabname> drop primary key drop index;
(Optional) Rename the index:
(可选)重命名索引:
alter index new_pk rename to c_name;
And then add the PK again indicating the index to be used:
然后再次添加指示要使用的索引的PK:
alter table <tabname> add constraint c_name
primary key (column_2) using index c_name;
回答by paxdiablo
I don't know if this is a similar problem but, in DB2, you have to actually commit following the alter table
statement. Otherwise it's still hanging around.
我不知道这是否是一个类似的问题,但是在 DB2 中,您必须按照alter table
语句实际提交。否则它仍然徘徊。