我有一个名为 provider
的表。我有三列,分别称为 person
、place
、thing
。可以有重复的人、重复的地点和重复的事物,但永远不可能有重复的人-地点-事物组合。
我将如何使用这三列在 MySQL 中为该表添加复合主键?
ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);
如果主键已经存在,那么你想这样做
ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);
@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).
如果要删除另一个表正在引用的主键,则必须先删除另一个表中的外键。如果在重新创建主键后仍需要它,您可以重新创建该外键。
此外,在使用复合键时,顺序很重要。这些
1) ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);
and
2) ALTER TABLE provider ADD PRIMARY KEY(person,thing,place);
不是一回事。它们都在这三个字段的集合上强制唯一性,但是从索引的角度来看是有区别的。字段的索引从左到右。例如,考虑以下查询:
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';
可以在 ALTER 语句 1 中使用主键索引 A 可以在 ALTER 语句 2 中使用主键索引 C 可以使用任一索引 D 不能使用任一索引
使用索引 2 中的前两个字段作为部分索引。 A 不能使用索引 1,因为它不知道索引的中间位置部分。不过,它可能仍然可以对一个人使用部分索引。
D 不能使用任何一个索引,因为它不认识人。
有关详细信息,请参阅 mysql 文档 here。
您可能只需要一个唯一约束。特别是如果您已经有代理键。 (已经存在的代理键的示例是一个 AUTO_INCREMENT 的单列)
下面是唯一约束的sql代码
ALTER TABLE `MyDatabase`.`Provider`
ADD CONSTRAINT CK_Per_Place_Thing_Unique UNIQUE (person,place,thing)
;
alter table table_name add primary key (col_name1, col_name2);
正如@GranadaCoder 提供的那样,使用COMPOSITE UNIQUE KEY 肯定会更好,不过这是一个有点棘手的例子:
ALTER IGNORE TABLE table_name ADD UNIQUES INDEX idx_name(some_id, another_id, one_more_id);
ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (col_name1, col_name2);
不定期副业成功案例分享