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

Ответ 1

На самом деле это вопрос о суррогатных ключах, которые всегда являются либо автоматически увеличивающимся числом, либо GUID и, следовательно, одним столбцом, а не натуральными ключами, которые часто требуют нескольких частей информации, чтобы быть поистине уникальными. Если вы можете иметь естественный ключ, который представляет собой только один столбец, тогда точка, очевидно, все равно.

Некоторые люди будут настаивать только на том, чтобы использовать тот или иной. Проведите достаточное время, работая с базами данных, и вы узнаете, что нет никакой контекстно-независимой передовой практики.

Некоторые из этих ответов используют терминологию SQL Server, но эти концепции обычно применимы ко всем продуктам СУБД:

Причины использования суррогатных ключей с одним столбцом:

• Кластеризованные индексы. Кластеризованный индекс всегда лучше всего работает, когда база данных может просто присоединяться к нему - в противном случае БД должна выполнять разделяет. Обратите внимание, что это применимо только в том случае, если ключ является последовательным, то есть последовательностью автоматического инкремента или последовательным GUID. Аргументарные GUID, вероятно, будут намного хуже для производительности.

• Отношения. Если ваш ключ состоит из 3, 4, 5 столбцов, включая типы символов и другие некомпактные данные, вы теряете огромное количество места и впоследствии уменьшаете производительность, если вам необходимо создать отношения внешнего ключа с этим ключом в 20 других таблицах.

• Уникальность. Иногда у вас нет истинного натурального ключа. Возможно, ваш стол - это своего рода журнал, и вы можете одновременно получить два одинаковых события. Или, может быть, ваш реальный ключ - это нечто вроде материализованного пути, который может быть определен только после того, как строка уже вставлена. В любом случае, вы всегда хотите, чтобы ваш кластерный индекс и/или первичный ключ были уникальными, поэтому, если у вас нет другой действительно уникальной информации, у вас нет выбора, кроме как использовать суррогатный ключ.

• Совместимость.. Большинство людей никогда не будут иметь дело с этим, но если натуральный ключ содержит что-то вроде hierarchyid, возможно, некоторые системы даже не могут его прочитать. В этом случае снова вы должны создать простой автогенерированный суррогатный ключ для использования этими приложениями. Даже если у вас нет каких-либо "странных" данных в естественном ключе, в некоторых библиотеках БД есть много проблем с первичными ключами с несколькими столбцами, хотя эта проблема быстро исчезает.

Причины использования многоколоночных натуральных клавиш

• Хранение.. Многие люди, которые работают с базами данных, никогда не работают с достаточно большими, чтобы заботиться об этом. Но когда таблица имеет миллиарды или триллионы строк, вы захотите сохранить абсолютный минимальный объем данных в этой таблице, который вы, возможно, сможете.

• Репликация. Да, вы можете использовать GUID или последовательный GUID. Но у GUID есть свои компромиссы, и если вы не можете или не хотите использовать GUID по какой-то причине, многоколоночный естественный ключ является гораздо лучшим выбором для сценариев репликации, потому что он по своей сути глобально уникален - это есть, вам не нужен специальный алгоритм, чтобы сделать его уникальным, он уникален по определению. Это позволяет легко рассуждать о распределенных архитектурах.

• Вставить/Обновить производительность. Суррогатные ключи не являются бесплатными. Если у вас есть набор столбцов, которые уникальны и часто запрашиваются, и поэтому вам нужно создать индекс покрытия для этих столбцов; индекс заканчивается почти таким же большим, как и таблица, что тратит пространство и требует, чтобы второй индекс обновлялся каждый раз, когда вы делаете какие-либо изменения. Если для вас всегда возможно иметь только один индекс (кластерный индекс) в таблице, вы должны это сделать!

Это то, что приходит в голову сразу с места. Я обновлю, если вдруг вспомню что-нибудь еще.

Ответ 2

Я думаю, что это почти всегда лучше (с точки зрения разработчика приложений, по крайней мере), чтобы сделать первичный ключ автогенерированным ключом и создать ограничение UNIQUE и индекс для нескольких столбцов.

• С помощью одного автоматически генерируемого первичного ключа вы сможете легко добавить ссылки на эту таблицу из других таблиц.
• Автоматически созданные первичные ключи работают проще с библиотеками ORM.
• Кроме того, если ваши ограничения уникальности изменяются в будущем, вам не нужно менять существующие первичные ключи.

Я столкнулся с несколькими ситуациями, вызывающими головную боль, потому что администратор баз данных считал, что всегда будет достаточно многоколоночного первичного ключа, а изменения будущих потребностей оказались неверными.

Ответ 3

Вы почти всегда хотите первичный ключ, поэтому я предполагаю, что выбор состоит в том, чтобы выбрать существующие два столбца в качестве первичного ключа или создать новый автоматически увеличивающий PK и вместо этого установить обычные уникальные ограничения для двух столбцов.

Если вам нужен первичный ключ с двумя столбцами:

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

Если вам нужен первичный ключ с автоматическим приращением:

• Если вы ссылаетесь на таблицу из другой таблицы, вы хотите, чтобы первичный ключ целевой таблицы был небольшим, потому что эти данные будут повторяться как внешний ключ в ссылочной таблице. Вы также хотите, чтобы это быстро сравнивалось.
• Каждый индекс, который вы добавляете в таблицу, содержит копию ключа кластеризации (который обычно совпадает с первичным ключом). Если ваш ключ кластеризации больше, чем нужно, каждый индекс в этой таблице будет больше, чем должен быть.

Ответ 4

Несколько примеров...

Соответствующие

• OLTP-системы, в большинстве ситуаций при реализации большинства отношений "многие-ко-многим".

недопустимый:

• Для таблиц измерений в OLAP-системах вы хотите сделать свой размерный ключ как можно меньше, чтобы ваша таблица фактов была как можно меньше (и быстро).

• Время, когда вы не уверены в уникальности комбинации. Конечно, это довольно сложный пример, но таблица "Person" была бы плохим выбором для многоколоночного ПК.

Ответ 5

Один пример того, когда это уместно, - это когда у вас есть таблица ссылок с полями внешнего ключа, соединяющими разные таблицы.

В целом, вероятно, неплохо использовать существующие, идентифицируя поля в качестве основного ключа, когда это возможно. Если у вас нет естественного поля id, и вам нужно будет объединить много полей, чтобы получить уникальный PK, вероятно, лучше использовать номер авто. Первичные ключи с более чем двумя полями могут стать беспорядочными.

Ответ 6

Мы нашли большой прирост производительности в нашем приложении, когда использовали несколько столбцов индексов и ключей. Это позволило нам создать индекс по нашим наиболее распространенным запросам, а основная таблица не была доступна даже после того, как весь индекс select мог быть в индексе. Однако это зависит от вашего приложения и набора данных.

Ответ 7

Иногда сложные естественные клавиши создают интуитивный смысл. Например. Предположим, у вас есть таблица для компании (ПК - ComapnyId) с некоторыми деталями компании в колонках. У вас также есть требование хранить имя генерального директора компании за всю свою историю. Естественным инвариантом является то, что одна компания может иметь только одного генерального директора за раз. Затем интуитивно понятно создать таблицу CompanyCeo с составным PK of CompanyId (таблица FK в CompanyId в компании) + FromDate. Другими столбцами в этой таблице могут быть ToDate и CeoName. Таким образом, вы можете гарантировать, что один и только один генеральный директор может начать свою работу в определенную дату.