MySQL ALTER TABLE 添加复合主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8859353/
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 to add a composite primary key
提问by David542
I have a table called provider. I have three columns called person, place, thing. There can be duplicate persons, duplicate places, and duplicate things, but there can never be a dupicate person-place-thing combination.
我有一张桌子叫provider. 我有三列名为person, place, thing。可以有重复的人、重复的地点和重复的事物，但永远不可能有重复的人-地-物组合。
How would I ALTER TABLE to add a composite primary key for this table in MySQL with the these three columns?
我将如何使用这三列 ALTER TABLE 在 MySQL 中为该表添加复合主键？
回答by Adrian Cornish
ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);
If a primary key already exists then you want to do this
如果主键已经存在，那么你想这样做
ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);
回答by cs_alumnus
@Adrian Cornish's answer is correct. However, there is another caveat to dropping an existing primary key. If that primary key is being used as a foreign key by another table you will get an error when trying to drop it. In some versions of mysql the error message there was malformed (as of 5.5.17, this error message is still
@Adrian Cornish 的回答是正确的。但是，删除现有主键还有另一个警告。如果该主键被另一个表用作外键，您将在尝试删除它时收到错误消息。在某些版本的 mysql 中，错误信息格式错误（截至 5.5.17，此错误信息仍然
alter table parent drop column id;
ERROR 1025 (HY000): Error on rename of
'./test/#sql-a04_b' to './test/parent' (errno: 150).
If you want to drop a primary key that's being referenced by another table, you will have to drop the foreign key in that other table first. You can recreate that foreign key if you still want it after you recreate the primary key.
如果要删除另一个表正在引用的主键，则必须先删除另一个表中的外键。如果您在重新创建主键后仍然需要该外键，则可以重新创建该外键。
Also, when using composite keys, order is important. These
此外，在使用复合键时，顺序很重要。这些
1) ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);
and
2) ALTER TABLE provider ADD PRIMARY KEY(person,thing,place);
are not the the same thing. They both enforce uniqueness on that set of three fields, however from an indexing standpoint there is a difference. The fields are indexed from left to right. For example, consider the following queries:
不是一回事。它们都在这三个字段的集合上强制执行唯一性，但是从索引的角度来看是有区别的。字段从左到右编入索引。例如，考虑以下查询：
A) SELECT person, place, thing FROM provider WHERE person = 'foo' AND thing = 'bar';
B) SELECT person, place, thing FROM provider WHERE person = 'foo' AND place = 'baz';
C) SELECT person, place, thing FROM provider WHERE person = 'foo' AND place = 'baz' AND thing = 'bar';
D) SELECT person, place, thing FROM provider WHERE place = 'baz' AND thing = 'bar';
B can use the primary key index in ALTER statement 1
A can use the primary key index in ALTER statement 2
C can use either index
D can't use either index
B 可以使用 ALTER 语句 1 中
的主键索引 A 可以使用 ALTER 语句 2 中的主键索引
C 可以使用任何一个索引
D 不能使用任何一个索引
A uses the first two fields in index 2 as a partial index. A can't use index 1 because it doesn't know the intermediate place portion of the index. It might still be able to use a partial index on just person though.
A 使用索引 2 中的前两个字段作为部分索引。A 不能使用索引 1，因为它不知道索引的中间位置部分。不过，它可能仍然可以仅对人使用部分索引。
D can't use either index because it doesn't know person.
D 不能使用任何一个索引，因为它不认识 person。
See the mysql docs herefor more information.
有关更多信息，请参阅此处的 mysql 文档。
回答by granadaCoder
You may simply want a UNIQUE CONSTRAINT. Especially if you already have a surrogate key. (example of an already existing surrogate key would be a single column that is an AUTO_INCREMENT )
您可能只是想要一个 UNIQUE CONSTRAINT。特别是如果您已经有了代理键。（已经存在的代理键的示例是一个 AUTO_INCREMENT 列）
Below is the sql code for a Unique Constraint
以下是唯一约束的 sql 代码
ALTER TABLE `MyDatabase`.`Provider`
ADD CONSTRAINT CK_Per_Place_Thing_Unique UNIQUE (person,place,thing)
;
回答by Naveen Kumar Alonekar
alter table table_name add primary key (col_name1, col_name2);
回答by Arthur Kushman
It`s definitely better to use COMPOSITE UNIQUE KEY, as @GranadaCoder offered, a little bit tricky example though:
正如@GranadaCoder 提供的那样，使用 COMPOSITE UNIQUE KEY 绝对更好，尽管这是一个有点棘手的例子：
ALTER IGNORE TABLE table_name ADD UNIQUES INDEX idx_name(some_id, another_id, one_more_id);
ALTER IGNORE TABLE table_name ADD UNIQUES INDEX idx_name(some_id, another_id, one_more_id);
回答by Lucky
ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (col_name1, col_name2);
ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (col_name1, col_name2);
