Каковы критерии дизайна для первичных ключей?

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

Каковы критерии?

Источник

12 ответов

Решение

Критерии для рассмотрения первичного ключа:

  • уникальность
  • Неприводимость (без подмножества ключа однозначно идентифицируется строка в таблице)
  • Простота (так что реляционное представление и манипулирование могут быть проще)
  • Стабильность (не следует часто менять)
  • Знакомство (значимое для пользователя)
Источник

Что такое первичный ключ?

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

Возможность изменить

Поскольку первичный ключ часто используется для внешних ссылок, он должен быть максимально стабильным. Все данные в базе данных являются изменчивыми, при условии, что кто-то соединяется с учетной записью, имеющей соответствующие привилегии. Вот почему базы данных предоставляют возможность определять CASCADE ON DELETE и CASCADE ON UPDATE- для синхронизации ссылочных зависимостей без необходимости отключения ограничений.

Натуральный или искусственный / суррогатный?

В идеале вам нужен натуральный ключ. Естественный ключ - это существующие данные, которые однозначно идентифицируют сущность, которую вы моделируете. Например, аббревиатуры штатов США - хороший естественный ключ, потому что аббревиатура непротиворечива, и все их знают:

US_STATE_PRIMARY_KEY  US_STATE
-------------------------- 
AL                    Alabama
AK                    Alaska
AZ                    Arizona
AR                    Arkansas
CA                    California

Не старайся найти естественный ключ. Они редко существуют. Маловероятно, что название штата США изменится, но это правдоподобно.

Реально, первичные ключи обычно являются искусственными (часто сгенерированными функциональностью базы данных). Обычно это числа или GUID, и они считаются искусственными, потому что сами по себе - ничто не может связать их ценность с информацией, которую они уникально идентифицируют. Торговая квитанция всегда нумеруется, потому что в этом нет ничего естественного, а также для аудита - пробелы в номерах квитанций вызывают подозрения. Чтобы продемонстрировать, какова произвольная нумерация, вот таблица состояний США, но с целым числом для столбца первичного ключа, US_STATE_CODE:

US_STATE_PRIMARY_KEY  US_STATE
-------------------------- 
100                   Alabama
101                   Alaska
102                   Arizona
103                   Arkansas
104                   California

Нет необходимости начинать значение с одного; некоторые магазины используют это в качестве меры безопасности, чтобы помешать внедрению SQL. Значение является последовательным, основанным на алфавитном порядке названия государства, но это не может быть гарантировано. Но в отличие от естественного ключа, если имя состояния изменилось - нужно обновить только один столбец.

Один столбец против композитного

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

Спектакль

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

Источник

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

В ERD сущности имеют первичные ключи. Это естественные ключи, то есть они являются атрибутами сущности. Для объекта PERSON это может быть SocialSecurityNumber. Для объекта ORDER if может быть OrderRef Для объекта INVOICE это может быть InvoiceNo. В первом случае это реальный идентификатор; во втором случае это умный ключ в ужасном формате (2010/DEF/000023); в третьем случае это монотонно увеличивающееся число, потому что это то, что использует текущая бумажная система.

Натуральные ключи могут быть причудливыми. Однажды я работал над дизайном базы данных, в котором аналитик указал сущность CUSTOMER с ключом (FullName, Address, Sex, DateOfBirth, DistinguishingCharacteristics), исходя из того, что два человека с одинаковым именем, датой рождения и полом могут жить в одном и том же месте. адрес.

Характеристики первичного ключа объекта:

  • уникальный
  • знакомый
  • стабильный (предположительно)
  • минимальный (один или несколько атрибутов, но столько, сколько необходимо)

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

Есть много причин не использовать SSN в качестве физического первичного ключа. Защита личных данных гражданина на самом деле является наиболее важной, но также имеет место, что номер человека может измениться. Первичные ключи должны быть неизменными.

Умные клавиши тупые. На самом деле это составные ключи, сжатые в один столбец. Они лучше представлены в виде отдельных столбцов, не в последнюю очередь потому, что часто требуется выполнять поиск по отдельным элементам ключа. Также формат таких клавиш может меняться.

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

Характеристики первичного ключа объекта:

  • уникальный
  • уместно (бессмысленно)
  • гарантированная стабильность
  • минимальный, обычно один столбец (кроме таблиц пересечений)

Поэтому, если ключ-кандидат не является бессмысленным идентификатором (например, InvoiceNo), таблица должна иметь синтетический ключ (суррогатный ключ AKA). Это может быть монотонно увеличивающееся число или GUID в зависимости от ваших потребностей. Что касается таблиц пересечений, если у них нет других атрибутов или зависимых таблиц, нет смысла заменять составной первичный ключ (составной ключ AKA) синтетическим.

Самое важное: мы все еще применяем ключи-кандидаты. Это означает применение ограничений UNIQUE к этим столбцам - SSN, OrderRef - в родительской таблице. Это связано с тем, что синтетический ключ однозначно идентифицирует строку в таблице, но не уникально идентифицирует данные.

По поводу знакомства

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

В комнете @bbadour предоставляет два контрастных примера:

{3296013,840082470,Bob Badour,745} versus {840082470,Bob Badour,PE,CA}

и ставит вопрос:

"Чего добивается 3296013, чего еще не достиг 840082470, что является основным ключом для моих академических достижений в любой или каждой средней школе в Канаде".

Ну, 840082470 это как номер счета. Само по себе это бессмысленная цепочка цифр. Если система, которую мы разрабатываем, относится к сфере высшего образования Канады, то она, безусловно, приемлема в качестве ключа кандидата. Однако, поскольку он является ключом, очевидно принадлежащим внешней центральной системе (простите, что я не понимаю канадскую академическую систему), он открыт для некоторых возражений против SSN в качестве первичного ключа. Мы полагаемся на эту внешнюю систему, чтобы гарантировать уникальность, гарантировать стабильность и проверять идентификацию.

Что касается 745 против PE, CA, это явно неправильно. Канадская почтовая аббревиатура "Остров Принца Эдуарда" и документ ISO для "Канады" идентифицируют две отдельные части информации и происходят из разных источников, поэтому они должны быть представлены в виде двух отдельных столбцов. Но давайте сосредоточимся на том, что 745 или PE делает лучший первичный ключ.

Во-первых, базе данных не важно, какой тип данных мы используем для кода для представления "Остров Принца Эдуарда". Просто хочет гарантированной уникальности.

Во-вторых, часть системы, обращенная к пользователю, скорее всего, будет отображать полное расширение "Остров Принца Эдуарда", и в этом случае приложению в любом случае потребуется выполнить поиск. Это связано с тем, что пользователи системы, в которой также хранятся адреса из страны Перу или штата Калифорния, оценят ясность расширенных имен [1]. Конечно, если мы пойдем дальше нескольких сложных случаев (таких как сокращения состояний), приложение всегда должно расширять коды при отображении их пользователям.

Таким образом, единственное преимущество использования PE, а не 745, состоит в том, что он делает специальные запросы проще.

В-третьих, если расширение кода изменяется, мы можем захотеть различать записи, которые используют более новую версию. Это намного проще, если 745='Prince Edward Island' а также 746='Prince Edward Is.' чем если бы мы использовали PE в качестве первичного ключа.

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

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

[1] Канадцы будут знать, что CA выступает за Канаду. Но стоит ли МО за Марокко, Монако, Молдову, Черногорию, Монголию или Монтсеррат? На самом деле ни один из них: это Макао.

Источник

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

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

Большинство из нас используют 32-битное целое число с автоинкрементом в качестве первичного ключа. Другой отличный выбор (при определенных обстоятельствах) - это UUID.

Источник

Ключ-кандидат - это набор атрибутов, которые являются неустранимо уникальными (это означает, что ни один атрибут не может быть удален из ключа без потери свойства уникальности).

Другими критериями при выборе того, какие ключи-кандидаты следует реализовать, являются: простота, стабильность, знакомство.

Эти три критерия являются важными соображениями, но не обязательно важными атрибутами ключа. Например, может быть желательно и вполне разумно применять ключ, который может часто меняться. Например: имя пользователя должно быть уникальным, но пользователь может изменить его по своему желанию, если оно остается уникальным.

Первичный ключ является ключом-кандидатом.

Источник

Ваш ответ Эрвину: "Я согласен, что при выборе первичного ключа просто выбирается один ключ-кандидат в качестве предпочтительного для ссылок на внешние ключи. Однако даже если мы полностью исключили имя" первичный ключ ", разработчики все равно должны выбрать, какой ключ-кандидат распространять в другой. отношение для справочных целей. Если пользователи идентифицируют отношение, на которое часто ссылаются, с нестабильным составным ключом, подразумеваете ли вы, что разработчик не имеет никакого дела, выбирая дополнительный простой, стабильный ключ? Или используя простой, стабильный ключ для ссылки на отношение? Ваш ключевой раздел кандидата, кажется, подразумевает это. - bbadour 8 часов назад "

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

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

ты согласен с этим? Если да, измените ли вы свой первоначальный вопрос на "Каковы критерии разработки ключей и каковы критерии выбора внешнего ключа из доступных ключей-кандидатов?"? Если нет, то почему?

С уважением, Карлос

Источник

Привет. это снова открыто. Вот оно.

(1) Выберите подходящие ключи-кандидаты.

Это не относится к дизайнеру базы данных, чтобы выбрать ключи-кандидаты. Разработчик базы данных должен следить за тем, чтобы все требования уникальности, о которых он сообщил пользователю, были соблюдены. Таким образом, именно пользователь "выбирает" ключи-кандидаты.

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

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

Второе - это то, как картина искажается из-за возможности различных логических схем, решающих одну и ту же проблему. Если D1 и D2 являются допустимыми проектами, решающими одну и ту же проблему, то может быть так, что определенное заданное правило уникальности, наложенное пользователем, принудительно применяется с использованием ключей в D1, но не в D2. С этой точки зрения, "выбор ключей-кандидатов" можно интерпретировать как "выбор конкретного дизайна, так что данное правило уникальности может быть осуществлено с использованием ключей". Но это был не тот вопрос, который вы задали.

(2) Выберите хорошие первичные ключи.

Некоторое время назад Дарвен поставил вопрос "Каковы веские причины выделять одного конкретного кандидата среди других как" первичных "?". Ничего особенного не получилось, разве что, возможно: "предположить, что именно этот ключ является предпочтительным для использования при ссылках на этот relvar". Я подозреваю, что они не нашли это достаточно убедительным, чтобы изменить свое прежнее решение о том, что "ни один ключ не является более уникальным, чем любой другой".

Но, предполагая, что, тем не менее, существует какая-то веская причина выделить один конкретный ключ в качестве "первичного", я полагаю, что применимы следующие соображения:

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

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

Уникальность: ключ-кандидат должен однозначно идентифицировать каждую строку в таблице. Ни одна таблица не может содержать две строки с одинаковым значением ключа-кандидата.

Неснижаемость: удаление любого столбца из ключа-кандидата должно нарушать свойство уникальности. Другими словами, никакое подмножество столбцов в ключе-кандидате само по себе не является ключом-кандидатом.

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

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

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

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

Источник

Каковы критерии?

PRIMARY KEY это то, что будет определять сущность, только сущность и ничего, кроме сущности.

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

    В этом случае вы полагаетесь на внешний мир.

    • У всех людей есть SSN? (Они этого не делают).
    • Являются SSNуникален? (Это не так).
    • Может SSN быть назначенным на другого человека? (Оно может).

  • Вы можете создать его внутри своей модели, используя AUTOINCREMENT или же GUIDs или что угодно.

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

    • У всех людей в вашей модели есть ID? (Да, они делают, иначе они не были бы в таблице с ID NOT NULL).
    • Эти ID's уникальным? (Да, они PRIMARY KEY ограничение заботится об этом).
    • Могут ли они быть назначены другим лицам? (Нет, они не могут, они либо не повторяются по замыслу, либо автоматически увеличиваются).

    Или другой набор ответов:

    • У всех людей в вашей модели есть ID? (Нет, таблица "Люди" была случайно удалена, хотя некоторая другая информация сохранилась).
    • Эти ID's уникальным? (Нет, мы не смогли правильно объединить две версии базы данных).
    • Могут ли они быть назначены другим лицам? (Да, мы сбрасываем AUTOINCREMENT по ошибке).

Самое главное, что суррогатный ключ - это праздник, который всегда с тобой. Вы всегда можете создать суррогатный ключ: ничто на Земле не может помешать вам объявить AUTOINCREMENT поле. Но далеко не все вещи имеют какой-то идентификатор, с которым все согласны.

Однако хороший натуральный ключ невозможно переоценить.

Guide Star Catalog база данных, скорее всего, резервируется более надежно, чем ваша, и список US Коды состояний вы всегда можете восстановить прямо из памяти.

Источник

(Не совсем уверен, как интерпретировать этот вопрос. Похоже на викторину или что-то, где вы ищете один "правильный" ответ из учебника. Я собираюсь интерпретировать вопрос как более практичный, поэтому мой совет ниже.)

По крайней мере, в мире MS SQL обсуждение правильного первичного ключа неизбежно заканчивается обсуждением правильного кластеризованного индекса для таблицы. Два не обязательно должны быть одинаковыми, но они по умолчанию, и для многих таблиц сделать два одинаковых часто хорошая идея.

Для целей нашего обсуждения здесь важно различать два:

ПЕРВИЧНЫЙ КЛЮЧ - это поле или комбинация полей, которые однозначно определяют строку.
КЛАСТЕРНЫЙ ИНДЕКС - это поле или комбинация полей, которые представляют физический порядок таблицы. (Опять же, я говорю о MS SQL Server, не уверен, как другие RDBS могут справиться с этим)

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

Давайте сначала посмотрим на критерии хорошего кластерного индекса (из отличной статьи Кимберли Триппа). Кластерный индекс должен быть:

  1. Уникальный - иначе бесполезный как идентификатор строки для других индексов
  2. Узкий - этот ключ используется в других индексах, поэтому должен быть как можно более узким
  3. Статический - если значения ключей изменяются, ссылки становятся недействительными и требуют обновления
  4. Постоянно увеличивающийся - для уменьшения фрагментации физической таблицы при добавлении новых строк

Очевидно, что первые 3 также являются хорошими критериями для первичного ключа. #4 - это бонус, который уменьшит фрагментацию таблицы по мере ее роста.

GUID в качестве первичного ключа, как бы он ни был популярен, фактически не выполняет 2 из этих критериев (узкий и постоянно увеличивающийся). Как таковой, он не рекомендуется в качестве индекса PK/Clustered в большинстве случаев (см. Статью Кима по теме здесь)

Источник

На самом деле только один, выберите суррогат для каждой таблицы (identity/auto_number) или что-то подобное, что пользователи никогда не увидят, поэтому вы можете делать с ними все, что вам нужно, когда вам это нужно и в будущем.

Источник

Я собираюсь сказать кое-что здесь, что не ожидается.

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

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

Если ваш доминирующий запрос диапазона не находится на ключе-кандидате, вы получите первичный ключ, который не применяется для уникальности! Это иногда называют кластеризованным индексом, который является неправильным, потому что нет индекса.

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

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

РЕДАКТИРОВАТЬ: определение диапазона запроса:
Запрос диапазона - это запрос ORDER BY, который содержит оператор больше или меньше оператора. Нас интересуют столбцы, для которых выполняются эти запросы. Фундаментальная идея заключается в том, что запрос диапазона выбирает несколько (от десятков до сотен, возможно, тысяч, но не всех) строк из таблицы на основе ограничивающих условий на одном или обоих концах.
Существует другой тип запросов диапазона, и здесь у вас есть внешний ключ к другой таблице, и операция - выбрать все совпадения для этого внешнего ключа. На самом деле это также запрос диапазона, хотя это не так.

Источник
Другие вопросы по тегам