Изменить таблицу для добавления составного первичного ключа

у меня есть таблица под названием provider. У меня есть три колонки под названием person,place,thing. Могут быть дубликаты людей, дубликаты мест и дубликаты вещей, но никогда не может быть дубликата комбинации человек-место-вещь.

как бы я изменил таблицу, чтобы добавить составной первичный ключ для этой таблицы в MySQL с этими тремя столбцами?

6 161
sql mysql primary-key alter-table composite-primary-key

6 ответов:

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

если первичный ключ уже существует, то вы хотите сделать это

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

@ответ Адриана Корниша правильный. Однако есть еще один нюанс в удалении существующего первичного ключа. Если первичный ключ используется в качестве внешнего ключа другой таблицы, вы получите ошибку при попытке удалить его. В некоторых версиях 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';

B можно использовать первичный ключ индекс в инструкции ALTER 1
A может использовать индекс первичного ключа в инструкции ALTER 2
C может использовать любой индекс
D не может использовать ни один индекс

A использует первые два поля в индексе 2 в качестве частичного индекса. A не может использовать индекс 1, потому что он не знает часть промежуточного места индекса. Он все еще может использовать частичный индекс только для человека.

D может не использовать индекс, потому что он не знает человека.

смотрите mysql документы здесь для получения дополнительной информации.

17

вы можете просто захотеть уникальное ограничение. Особенно если у вас уже есть суррогатный ключ. (пример: типа AUTO_INCREMENT )

ALTER TABLE `MyDatabase`.`Provider`
    ADD CONSTRAINT CK_Per_Place_Thing_Unique UNIQUE (person,place,thing)
;
14
alter table table_name add primary key (col_name1, col_name2);
3

определенно лучше использовать составной уникальный ключ, как предложил @GranadaCoder, немного сложный пример:

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

1

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

0