SQL 更改主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2310561/
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
Change Primary Key
提问by Mohit BAnsal
I have a table in Oracle which has following Schema:
我在 Oracle 中有一个表，它具有以下架构：
City_ID Name State Country BuildTime Time
When i declared the table my primary key was both City_IDand the BuildTimebut now I want to change the primary key to three columns:
当我声明表时，我的主键既是City_ID又是 ，BuildTime但现在我想将主键更改为三列：
City_ID BuildTime Time
How can I change the primary key?
如何更改主键？
回答by Peter Lang
Assuming that your table name is cityand your existing Primary Key is pk_city, you should be able to do the following:
假设您的表名是city并且您现有的主键是pk_city，您应该能够执行以下操作：
ALTER TABLE city
DROP CONSTRAINT pk_city;
ALTER TABLE city
ADD CONSTRAINT pk_city PRIMARY KEY (city_id, buildtime, time);
Make sure that there are no records where timeis NULL, otherwise you won't be able to re-create the constraint.
确保没有记录 where timeis NULL，否则您将无法重新创建约束。
回答by Tony Andrews
You will need to drop and re-create the primary key like this:
您需要像这样删除并重新创建主键：
alter table my_table drop constraint my_pk;
alter table my_table add constraint my_pk primary key (city_id, buildtime, time);
However, if there are other tables with foreign keys that reference this primary key, then you will need to drop those first, do the above, and then re-create the foreign keys with the new column list.
但是，如果有其他具有引用此主键的外键的表，则您需要先删除这些表，执行上述操作，然后使用新列列表重新创建外键。
An alternative syntax to drop the existing primary key (e.g. if you don't know the constraint name):
删除现有主键的另一种语法（例如，如果您不知道约束名称）：
alter table my_table drop primary key;
回答by Narasimha
Sometimes when we do these steps:
有时，当我们执行这些步骤时：
alter table my_table drop constraint my_pk;
alter table my_table add constraint my_pk primary key (city_id, buildtime, time);
The last statement fails with
最后一条语句失败
ORA-00955 "name is already used by an existing object"
ORA-00955“名称已被现有对象使用”
Oracle usually creates an unique index with the same name my_pk. In such a case you can drop the unique index or rename it based on whether the constraint is still relevant.
Oracle 通常会创建一个具有相同名称 my_pk 的唯一索引。在这种情况下，您可以删除唯一索引或根据约束是否仍然相关重命名它。
You can combine the dropping of primary key constraint and unique index into a single sql statement:
您可以将主键约束和唯一索引的删除组合到单个 sql 语句中：
alter table my_table drop constraint my_pk drop index;
check this: ORA-00955 "name is already used by an existing object"
检查这个： ORA-00955“名称已被现有对象使用”
