Для чего нужен составной ключ в mysql?

Question

[Артём]

Для чего нужен составной ключ в mysql? Можете пожалуйста объяснить и привести понятный пример, в котором можно увидеть необходимость использования составного ключа.

Comments

[Сергей delphinpro]

Есть таблица статистики.
Поля

date   user   и другие.

18.01  cat
18.01  dog
19.01  cat
19.01  dog
...

дата - повторяется, имена пользователей - повторяются.
нужен уникальный ключ. Им будет составной ключ из этих двух полей, потому что пара data-user всегда уникальна.

Answer 1

[Талян]

У меня например есть таблица "права доступа".

access (id,user_id,access_mode)
У одного пользователя не может быть два раза одно и тоже значение access_mode ( зачем допускать пользователя 2 раза к одному и тому же разделу сайта?)

Потому я использую двойной ключ (user_id,access_mode)

Comments

[Артём]

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

[Талян]

Артём, права разные есть. Для каждого пользователя может быть скольугодно разных прав.

Список прав хранится в другой таблице, и может меняться.

users (id,name)
access_modes (id,access_name)
access (user_id,access_mode_id)

users:
1|Петя
2|Вася

access_modes:
1|может менять новости
2|может редактировать главную страницу
3|может удалять аккаунт
4|может забанить

access:
1|1
1|2
2|1
2|3
1|4

[Артём]

Анатолий Цивилёв, все, теперь все стало на свои места, спасибо

[Алексей Сундуков]

Анатолий Цивилёв А каким образом составной ключ связан с уникальностью? В данном контексте вопроса. Может ли составной ключ быть не уникальным?

[Талян]

Алексей Сундуков, Вы меня спрашиваете?)

Нет, не может.

Связка значение1-значение2 всегда уникально, хотя оба значения в любой колонке могут встречаться сколь угодно раз по отдельности.

[Алексей Сундуков]

>Нет, не может.
Т.е. я не могу создать не уникальный составной ключ (индекс), так?

[Талян]

Алексей Сундуков, так. Будет ошибка

[Алексей Сундуков]

Анатолий Цивилёв, точно?

CREATE TABLE IF NOT EXISTS `test` (
  `field1` int(11) NOT NULL,
  `field2` int(11) NOT NULL,
  KEY `index_test` (`field1`,`field2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `test` (`field1`, `field2`) VALUES
(1, 1),
(1, 1);

[Алексей Сундуков]

Анатолий Цивилёв, Я это к чему...Нет связи между тем, составной индекс или нет и уникальный он или нет. Это теплое и мягкое и ни как это категории не сцеплены. Точно так же как не сцеплено создание ключей и fk в некоторых реализациях РСУБД.

Поэтому не стоит вводить в заблуждение автора и других читателей.

Answer 2

[luna3956]

Составные ключи могут служить заменой специально вводимым идентификаторам.
Например есть таблица Пользователи вида Users(id, name) и таблица Фотки вида Photos(id, name).

Таблица содержащая фотки пользователей будет таблица UsersPhotos(user_id, photo_id). В данном случае эти два поля образуют составной ключ, эта связка будет уникальна и нет смысла вводить избыточный идентификатор, если этого не требует логика приложения.

Comments

[Артём]

вот я и не понимаю, проще же создать как и написано выше внешний ключ user_id в таблице Photos и через него связать с таблицей Users. Это разве не проще, чем создавать отдельную таблицу с таким составным ключом?

[Талян]

Артём, это всего лишь абстрактный пример, как может использоваться.

Ниже я написал, как это используется у меня. Тут вы уже не засунете в таблицу users все их права.

[stratosmi]

Артём,

Таблица содержащая фотки пользователей будет таблица UsersPhotos(user_id, photo_id). В данном случае эти два поля образуют составной ключ, эта связка будет уникальна и нет смысла вводить избыточный идентификатор, если этого не требует логика приложения.

Эта таблица нужна для поиска всех фотографий пользователя.
Следовательно, несмотря на то, что ключ здесь составной, но индекс нужен по user_id

[stratosmi]

Артём,

вот я и не понимаю, проще же создать как и написано выше внешний ключ user_id в таблице Photos и через него связать с таблицей Users. Это разве не проще, чем создавать отдельную таблицу с таким составным ключом?

В данном случае - да, конечно.
luna3956 привел вам другую ситуацию.

[luna3956]

Артём, смотрите, есть связи вида один ко многим и есть связи вида многие ко многим. Если сделать так: Photos(id, name, user_id) - это будет связь один ко многим, то есть одна картинка/фотка привязана только к одному пользователю(но один и тот же пользователь может быть привязан к множеству картинок). А теперь представьте если нужно обозначить ситуацию, когда одна картинка может иметь отношение не только к одному пользователю, а к большому количеству. Тогда вы уже не сможете указать идентификатор пользователя так Photos(id, name, user_id), вам придется создавать новую таблицу вида, который был указан мною в ответе, и там уже каждая фотография может быть привязана к любому количеству пользователей. А каждая строка в этой новой таблице будет содержать уникальную связку, которая и будет являться составным ключом.

[Rsa97]

Следовательно, несмотря на то, что ключ здесь составной, но индекс нужен по user_id

Первичный ключ (user_id, photo_id) одновременно обеспечит уникальность пары и будет работать как индекс по user_id.

[Артём]

всем спасибо, разобрался

Answer 3

[Сергей Соломеин]

Если ключ primary, то, как уже ответили, чтобы использовать уникальную комбинацию полей вместо auto_increment.
А если речь идёт об индексе, то mysql не умеет их комбинировать. Соответственно, если вам нужна фильтрация, например, по тэгу статьи и дате публикации, и это частый запрос, то вам придётся добавить составной индекс. Причём, порядок имеет значение - key(tag_id, tm) не будет использоваться в запросах с tm, но без tag_id в where.

Answer 4

[Алексей Сундуков]

Составной ключ (индекс) нужен как и все индексы для ускорения выборки. Его можно не использовать и для каждого поля создать свой, но работать они будут медленее, чем составной и будет больше оверхеда на их поддержку.
Т.е. составной ключ нужен если у вас много выборок вида Поле1->Поле2->Поле3... При этом как уже отметил kilgur порядок полей имеет значение и работать будет быстро только префиксный вариант.

Answer 5

[Роман]

Очень хороши составные ключи для таблиц с соотношениями типа многие-ко-многим. Например: есть таблица с авторами и таблица с книгами, но т.к. у одной книги может быть несколько авторов и у одного автора несколько книг, то сопоставление автор/книга мы можем хранить с таблице типа many-to-many. Вводить для неё суррогатный ключ (все эти id INT INSUGNED PRIMARY KEY AUTO_INCREMENT) совершенно не нужно - в качестве ключа можно взять комбинацию первичных ключей из таблиц авторов и книг.

А если мы имеет таблицу many-to-many, которая сопоставляет две других таблицы с естественными ключами, и выбрали для many-to-many составной ключ, являющийся, таким образом, комбинацией двух естественных ключей, то иногда получается вообще избавиться от лишнего JOIN при запросе, что очень неплохо, потому что самая важная информация уже есть в самом ключе.