ALTER TABLE添加复合主键。

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。可以有重复的人，重复的地方，重复的东西，但是不可能有dupicate person-place-东西的组合。

How would I ALTER TABLE to add a composite primary key for this table in MySQL with the these three columns?

如何修改表，以便在MySQL中添加具有这三列的复合主键?

6 个解决方案

#1

349

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);

#2

@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.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不能用任何一个指标，因为它不知道人。

See the mysql docs here for more information.

更多信息请参见这里的mysql文档。

#3

You may simply want a UNIQUE CONSTRAINT. Especially if you already have a surrogate key. (example: an AUTO_INCREMENT )

您可能只想要一个唯一的约束。特别是如果您已经有了代理键。(例如:一个AUTO_INCREMENT)

ALTER TABLE `MyDatabase`.`Provider`
    ADD CONSTRAINT CK_Per_Place_Thing_Unique UNIQUE (person,place,thing)
;

#4

alter table table_name add primary key (col_name1, col_name2);

#5

It`s definitely better to use COMPOSITE UNIQUE KEY, as @GranadaCoder offered, a little bit tricky example though:

使用组合唯一键肯定更好，@GranadaCoder提供了一个有点棘手的例子:

ALTER IGNORE TABLE table_name ADD UNIQUES INDEX idx_name(some_id, another_id, one_more_id);

添加索引idx_name(some_id、another_id、one_more_id);

#6

ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (col_name1, col_name2);

修改表table_name删除主键，添加主键(col_name1, col_name2);

#1

349

ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);