Как лучше всего использовать первичные ключи в таблицах?
При разработке таблиц я выработал привычку иметь один столбец, который является уникальным и который я делаю первичным ключом. Это достигается тремя способами в зависимости от требований:
- Целочисленный столбец идентификаторов, который автоматически увеличивается.
- Уникальный идентификатор (GUID)
- Столбец с коротким символом (x) или целым числом (или другим относительно небольшим числовым типом), который может служить столбцом идентификатора строки
Номер 3 будет использоваться для довольно небольшого поиска, в основном для чтения таблиц, которые могут иметь уникальный строковый код статической длины или числовое значение, например год или другое число.
По большей части все остальные таблицы будут иметь автоинкрементное целое число или первичный ключ уникального идентификатора.
Вопрос:-)
Недавно я начал работать с базами данных, которые не имеют согласованного идентификатора строки, а первичные ключи в настоящее время сгруппированы по различным столбцам. Некоторые примеры:
- Дата и время / характер
- Дата и время / число
- Дата и время / VARCHAR
- символ / NVARCHAR / NVARCHAR
Есть ли веские аргументы для этого? Я бы всегда определял столбец идентификаторов или уникальных идентификаторов для этих случаев.
Кроме того, существует множество таблиц без первичных ключей. Каковы веские причины для этого?
Я пытаюсь понять, почему таблицы были спроектированы такими, какими они были, и для меня это кажется большим беспорядком, но, возможно, для этого были веские причины.
Третий вопрос, который поможет мне расшифровать ответы: в тех случаях, когда для составного первичного ключа используются несколько столбцов, есть ли конкретное преимущество этого метода по сравнению с суррогатным / искусственным ключом? Я думаю в основном о производительности, обслуживании, администрировании и т. Д.?
21 ответ
Я следую нескольким правилам:
- Первичные ключи должны быть настолько маленькими, насколько это необходимо. Предпочитайте числовой тип, потому что числовые типы хранятся в гораздо более компактном формате, чем символьные форматы. Это связано с тем, что большинство первичных ключей будут внешними ключами в другой таблице, а также используются в нескольких индексах. Чем меньше ваш ключ, тем меньше индекс, тем меньше страниц в кеше вы будете использовать.
- Первичные ключи никогда не должны меняться. Обновление первичного ключа всегда должно быть исключено. Это потому, что он, скорее всего, будет использоваться в нескольких индексах и использоваться в качестве внешнего ключа. Обновление одного первичного ключа может вызвать эффект изменений.
- НЕ используйте "первичный ключ вашей проблемы" в качестве первичного ключа вашей логической модели. Например, номер паспорта, номер социального страхования или номер контракта сотрудника, поскольку эти "первичные ключи" могут меняться в реальных ситуациях.
На суррогатном против естественного ключа, я ссылаюсь на правила выше. Если естественный ключ мал и никогда не изменится, его можно использовать в качестве первичного ключа. Если натуральный ключ большой или может измениться, я использую суррогатные ключи. Если первичного ключа нет, я все равно создаю суррогатный ключ, потому что опыт показывает, что вы всегда будете добавлять таблицы в свою схему и хотите, чтобы на месте был первичный ключ.
Природные стихи искусственных ключей - это своего рода религиозные дебаты среди сообщества баз данных - см. Эту статью и другие, на которые она ссылается. Я не за то, чтобы всегда иметь искусственные ключи или никогда не иметь их. Я бы решил в каждом конкретном случае, например:
- Штаты США: я бы выбрал код штата ("Техас" и т. Д.), А не код штата =1 для Техаса
- Сотрудники: я обычно создавал бы искусственный employee_id, потому что трудно найти что-то еще, что работает. SSN или его эквивалент может работать, но могут быть проблемы, например, у нового участника, который еще не предоставил свой SSN.
- История зарплаты сотрудника: (employee_id, start_date). Я бы не стал создавать искусственный employee_salary_history_id. Какой пункт это послужило бы (кроме "глупой последовательности")
Везде, где используются искусственные ключи, вы всегда должны объявлять уникальные ограничения на естественные ключи. Например, используйте state_id, если необходимо, но тогда вам лучше объявить уникальное ограничение на код_состояния, иначе вы обязательно в конечном итоге получите:
state_id state_code state_name
137 TX Texas
... ... ...
249 TX Texas
Я избегаю использовать естественные ключи по одной простой причине - человеческая ошибка. Хотя естественные уникальные идентификаторы часто доступны (SSN, VIN, номер счета и т. Д.), Они требуют, чтобы человек вводил их правильно. Если вы используете SSN в качестве первичного ключа, кто-то транспонирует пару цифр во время ввода данных, и ошибка не обнаруживается сразу, тогда вы сталкиваетесь с изменением вашего первичного ключа.
Все мои первичные ключи обрабатываются программой базы данных в фоновом режиме, и пользователь никогда не узнает о них.
Просто дополнительный комментарий о том, что часто упускается из виду. Иногда не использование суррогатного ключа имеет преимущества в дочерних таблицах. Допустим, у нас есть дизайн, который позволяет вам управлять несколькими компаниями в одной базе данных (может быть, это размещенное решение или что-то в этом роде).
Допустим, у нас есть эти таблицы и столбцы:
Company:
CompanyId (primary key)
CostCenter:
CompanyId (primary key, foreign key to Company)
CostCentre (primary key)
CostElement
CompanyId (primary key, foreign key to Company)
CostElement (primary key)
Invoice:
InvoiceId (primary key)
CompanyId (primary key, in foreign key to CostCentre, in foreign key to CostElement)
CostCentre (in foreign key to CostCentre)
CostElement (in foreign key to CostElement)
Если последний бит не имеет смысла, Invoice.CompanyId является частью двух внешних ключей, одного для таблицы CostCentre и одного для таблицы CostElement. Первичный ключ (InvoiceId, CompanyId).
В этой модели невозможно испортить и ссылаться на CostElement от одной компании и CostCentre от другой компании. Если суррогатный ключ использовался в таблицах CostElement и CostCentre, он был бы.
Чем меньше шансов облажаться, тем лучше.
Нет проблем в создании вашего первичного ключа из различных областей, это естественный ключ.
Вы можете использовать столбец Identity (связанный с уникальным индексом на полях-кандидатах), чтобы создать суррогатный ключ.
Это старая дискуссия. Я предпочитаю суррогатные ключи в большинстве ситуаций.
Но нет оправдания отсутствию ключа.
RE: РЕДАКТИРОВАТЬ
Да, есть много споров по этому поводу:D
Я не вижу очевидного преимущества в натуральных ключах, кроме того факта, что они являются естественным выбором. Вы всегда будете думать в Имя, SocialNumber - или что-то в этом роде - вместо idPerson.
Суррогатные ключи являются ответом на некоторые проблемы, с которыми сталкиваются естественные ключи (например, распространение изменений).
Как вы привыкли к суррогатам, это кажется более чистым и управляемым.
Но, в конце концов, вы обнаружите, что это просто вопрос вкуса или мышления. Люди "лучше думают" с помощью естественных ключей, а другие нет.
Помимо всех этих хороших ответов, я просто хочу поделиться хорошей статьей, которую я только что прочитал, "Великие дебаты по первичным ключам".
Просто процитирую несколько пунктов:
Разработчик должен применить несколько правил при выборе первичного ключа для каждой таблицы:
- Первичный ключ должен однозначно идентифицировать каждую запись.
- Значение первичного ключа записи не может быть нулевым.
- Первичное значение ключа должно существовать при создании записи.
- Первичный ключ должен оставаться стабильным - вы не можете изменять поля первичного ключа.
- Первичный ключ должен быть компактным и содержать как можно меньше атрибутов.
- Значение первичного ключа не может быть изменено.
Естественные ключи (как правило) нарушают правила. Суррогатные ключи соответствуют правилам. (Вам лучше прочитать эту статью, она стоит вашего времени!)
Таблицы должны иметь первичный ключ все время. Когда это не так, это должны были быть поля AutoIncrement.
Иногда люди пропускают первичный ключ, потому что они передают много данных, и это может замедлить (зависит от базы данных) процесс. НО, это должно быть добавлено после этого.
Несколько комментариев по поводу таблицы ссылок, это правильно, это исключение, НО поля должны быть FK для сохранения целостности, и в некоторых случаях эти поля также могут быть первичными ключами, если дублирование в ссылках не разрешено... но хранить в простая форма, потому что в программировании часто встречаются исключения, должен присутствовать первичный ключ для сохранения целостности ваших данных.
Вот мое собственное правило, на котором я остановился после 25 лет опыта разработки.
- Все таблицы должны иметь один столбец первичного ключа, который автоматически увеличивается.
- Включите его в любое представление, которое должно быть обновляемым
- Первичный ключ не должен иметь никакого значения в контексте вашего приложения. Это означает, что это не должен быть SKU, или номер счета, или идентификатор сотрудника, или любая другая информация, которая имеет значение для вашего приложения. Это просто уникальный ключ, связанный с сущностью.
Первичный ключ используется базой данных в целях оптимизации и не должен использоваться вашим приложением ни для чего иного, кроме идентификации конкретной сущности или ее связи с конкретной сущностью.
Постоянное использование первичного ключа с одним значением делает выполнение операций UPSERT очень простым.
Используйте дополнительные индексы для поддержки ключей из нескольких столбцов, которые имеют значение в вашем приложении.
Что особенного в первичном ключе?
Какова цель таблицы в схеме? Какова цель ключа таблицы? Что особенного в первичном ключе? Дискуссии вокруг первичных ключей, похоже, упускают из виду тот факт, что первичный ключ является частью таблицы, а эта таблица - частью схемы. То, что лучше для таблицы и отношений таблицы, должно определять ключ, который используется.
Таблицы (и связи таблиц) содержат факты об информации, которую вы хотите записать. Эти факты должны быть самодостаточными, значимыми, понятными и не противоречивыми. С точки зрения дизайна, другие таблицы, добавленные или удаленные из схемы, не должны влиять на данную таблицу. Должна быть цель для хранения данных, связанных только с самой информацией. Понимание того, что хранится в таблице, не требует проведения научно-исследовательского проекта. Ни один факт, хранящийся для одной и той же цели, не должен храниться более одного раза. Ключи представляют собой целую или часть записываемой информации, которая является уникальной, а первичный ключ - это специально назначенный ключ, который должен быть основной точкой доступа к таблице (т. Е. Его следует выбирать для согласованности и использования данных, а не просто для вставки). спектакль).
- ВНЕ: К сожалению, побочный эффект большинства баз данных, разрабатываемых и разрабатываемых прикладными программистами (которым я иногда являюсь), заключается в том, что то, что лучше всего подходит для приложения или среды приложения, часто определяет первичный ключ выбора таблиц. Это приводит к целочисленным и GUID-ключам (поскольку они просты в использовании для каркасов приложений) и к монолитным конструкциям таблиц (поскольку они уменьшают количество объектов каркаса приложений, необходимых для представления данных в памяти). Эти решения по проектированию баз данных на основе приложений приводят к значительным проблемам согласованности данных при использовании в масштабе. Прикладные структуры, разработанные таким образом, естественным образом приводят к созданию таблиц за раз. "Частичные записи" создаются в таблицах, а данные заполняются с течением времени. Взаимодействие с несколькими таблицами исключается или когда используется, приводит к несогласованности данных, когда приложение функционирует неправильно. Эти схемы приводят к получению бессмысленных (или трудных для понимания) данных, распределению данных по таблицам (чтобы понять текущую таблицу, вам нужно взглянуть на другие таблицы) и дублированию данных.
Было сказано, что первичные ключи должны быть настолько маленькими, насколько это необходимо. Я бы сказал, что ключи должны быть настолько большими, насколько это необходимо. Следует избегать случайного добавления бессмысленных полей в таблицу. Еще хуже сделать ключ из случайно добавленного бессмысленного поля, особенно когда оно разрушает зависимость соединения от другой таблицы к неосновному ключу. Это разумно только в том случае, если в таблице нет хороших ключей-кандидатов, но это, безусловно, признак плохой схемы, если она используется для всех таблиц.
Также было сказано, что первичные ключи никогда не должны изменяться, поскольку обновление первичного ключа всегда должно быть исключено. Но обновление аналогично удалению с последующей вставкой. По этой логике вы никогда не должны удалять запись из таблицы с одним ключом, а затем добавлять другую запись со вторым ключом. Добавление суррогатного первичного ключа не устраняет тот факт, что другой ключ в таблице существует. Обновление неосновного ключа таблицы может разрушить значение данных, если другие таблицы имеют зависимость от этого значения через суррогатный ключ (например, таблица состояния с суррогатным ключом, описание состояния которого изменено с "Обработано" на "Отменено"). 'определенно испортил бы данные). О чем всегда должно быть и речи, так это о разрушении смысла данных.
Сказав это, я благодарен за многие плохо спроектированные базы данных, которые существуют сегодня на предприятиях (бессмысленные-суррогатные ключи-данные-повреждены-1NF), потому что это означает, что существует бесконечный объем работы для людей, которые понимают правильное проектирование баз данных., Но с грустной стороны, это иногда заставляет меня чувствовать себя как Сизиф, но я держу пари, что у него был один чертовски 401 КБ (до крушения). Держитесь подальше от блогов и веб-сайтов для важных вопросов дизайна базы данных. Если вы разрабатываете базы данных, посмотрите CJ Date. Вы также можете ссылаться на Celko для SQL Server, но только если сначала будете держать себя за нос. На стороне Oracle, ссылка Том Кайт.
Естественный ключ, если таковой имеется, обычно лучше. Итак, если datetime/char однозначно идентифицирует строку, и обе части имеют значение для строки, это здорово.
Если значение имеет только дата и время, а символ просто привязан, чтобы сделать его уникальным, то вы могли бы просто пойти с полем идентификации.
Естественные и искусственные ключи для меня - это вопрос того, сколько бизнес-логики вы хотите в своей базе данных. Номер социального страхования (SSN) является отличным примером.
"Каждый клиент в моей базе данных будет и должен иметь SSN". Бам, готово, сделай его первичным ключом и покончим с этим. Просто помните, когда ваше бизнес-правило меняется, вы сожжены.
Я сам не люблю натуральные ключи из-за своего опыта в изменении бизнес-правил. Но если вы уверены, что это не изменится, это может предотвратить несколько критических соединений.
Я подозреваю, что свернутая газетная терапия Стивена А. Лоу требуется для дизайнера исходной структуры данных.
Кроме того, GUID в качестве первичного ключа может привести к снижению производительности. Я бы не рекомендовал это.
Я тоже всегда использую числовой идентификатор столбца. В оракуле я использую число (18,0) без реальной причины выше числа (12,0) (или что-то большее, чем целое, а не длинное), возможно, я просто не хочу беспокоиться о получении нескольких миллиардов строк в дБ!
Я также включил созданный и измененный столбец (отметка времени) для базового отслеживания, где это кажется полезным.
Я не против установки уникальных ограничений на другие комбинации столбцов, но мне действительно нравится мой идентификатор, созданные, измененные базовые требования.
Вы должны использовать "составной" или "составной" первичный ключ, который состоит из нескольких полей.
Это вполне приемлемое решение, иди сюда для получения дополнительной информации:)
Я ищу естественные первичные ключи и использую их, где могу.
Если естественные ключи не могут быть найдены, я предпочитаю GUID для INT++, потому что SQL Server использует деревья, и плохо всегда добавлять ключи к концу в деревьях.
В таблицах со многими связями я использую составной первичный ключ внешних ключей.
Поскольку мне посчастливилось использовать SQL Server, я могу изучить планы выполнения и статистику с помощью профилировщика и анализатора запросов и выяснить, как мои ключи работают очень легко.
Я всегда использую поле номера или идентификатора.
Я работал на клиента, который использовал SSN в качестве первичного ключа, а затем из-за правил HIPAA был вынужден изменить на "MemberID", и это вызвало массу проблем при обновлении внешних ключей в связанных таблицах. Придерживаясь последовательного стандарта столбца идентификации, я помог избежать подобной проблемы во всех моих проектах.
GUID можно использовать в качестве первичного ключа, но вам нужно создать правильный тип GUID, чтобы он работал хорошо.
Вам нужно создать COMID GUID. Хорошей статьей об этом и статистикой производительности является Стоимость GUID как первичных ключей.
Также некоторый код по созданию COMID GUID в SQL находится в Uniqueidentifier vs identity( архив).
Если вы действительно хотите прочитать все эти и другие споры об этих давних дебатах, выполните поиск "естественного ключа" в переполнении стека. Вы должны получить обратно страницы результатов.
Все таблицы должны иметь первичный ключ. В противном случае у вас есть HEAP - в некоторых ситуациях это может быть тем, что вам нужно (например, интенсивная загрузка вставки, когда данные затем реплицируются через сервисный брокер в другую базу данных или таблицу).
Для таблиц поиска с небольшим объемом строк вы можете использовать код 3 CHAR в качестве первичного ключа, так как он занимает меньше места, чем INT, но разница в производительности незначительна. Кроме этого, я всегда использовал бы INT, если у вас нет справочной таблицы, которая, возможно, имеет составной первичный ключ, составленный из внешних ключей из связанных таблиц.
Я буду честен о своем предпочтении натуральных ключей - используйте их, где это возможно, так как они значительно облегчат вам жизнь в администрировании баз данных. Я установил стандарт в нашей компании, что все таблицы имеют следующие столбцы:
- Идентификатор строки (GUID)
- Создатель (строка; по умолчанию имеет имя текущего пользователя (
SUSER_SNAME()в T-SQL)) - Создано (DateTime)
- Отметка
Идентификатор строки имеет уникальный ключ для каждой таблицы, и в любом случае он генерируется автоматически для каждой строки (а разрешения запрещают его редактирование), и гарантированно будет уникальным для всех таблиц и баз данных. Если какой-либо системе ORM требуется один ключ ID, это тот, который нужно использовать.
Между тем, фактический PK является, если возможно, естественным ключом. Мои внутренние правила примерно такие:
- Люди - используйте суррогатный ключ, например INT. Если это внутренний, GUID пользователя Active Directory является приемлемым выбором
- Таблицы поиска (например, StatusCodes) - используйте короткий код CHAR; его легче запомнить, чем INT, и во многих случаях бумажные формы и пользователи также будут использовать его для краткости (например, Status = "E" для "Expired", "A" для "Approved", "NADIS" для "No Asbstos Detected" В образце ")
- Связывание таблиц - комбинация ФК (например,
EventId, AttendeeId)
Таким образом, в идеале вы получите естественный, понятный для человека и запоминающийся PK, а также ORM-ориентированный GUID с одним идентификатором на таблицу.
Предостережение: базы данных, которые я поддерживаю, имеют тенденцию к сотням тысяч записей, а не миллионам или миллиардам, поэтому, если у вас есть опыт работы с большими системами, который противоречит моему совету, смело игнорируйте меня!
Мы выполняем много объединений, и составные первичные ключи просто стали причиной снижения производительности. Простое int или long решает многие проблемы, даже если вы вводите второй ключ-кандидат, но гораздо проще и понятнее объединиться в одной области, чем в трех.