在 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 18:12:25  来源:igfitidea点击:

Dropping then adding a constraint fails in oracle

sqloracle

提问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 tablestatement. Otherwise it's still hanging around.

我不知道这是否是一个类似的问题,但是在 DB2 中,您必须按照alter table语句实际提交。否则它仍然徘徊。