Нужна ли каждой таблице автоинкрементный искусственный первичный ключ?
Почти каждая таблица в каждой базе данных, которую я видел за 7 лет опыта разработки, имеет автоинкрементный первичный ключ. Почему это? Если у меня есть таблица штатов США, где у каждого штата, где у каждого штата должно быть уникальное имя, какой смысл использовать автоинкрементный первичный ключ? Почему бы просто не использовать имя штата в качестве первичного ключа? Мне кажется, что это оправдание тому, что дубликаты замаскированы под уникальные строки.
Это кажется очевидным для меня, но опять же, похоже, никто больше не приходит и не делает то же логическое заключение, что и я, поэтому я должен предположить, что есть большая вероятность, что я ошибаюсь.
Есть ли реальная практическая причина, по которой нам нужно использовать автоинкрементные ключи?
9 ответов
Нет.
В большинстве случаев имея суррогат INT IDENTITY Ключ является простым вариантом: он может быть гарантированно НЕ НЕДЕЙСТВИТЕЛЕН и уникален на 100%, чего не предлагает множество "естественных" ключей - имена могут меняться, так же как и SSN и другие элементы информации.
В случае аббревиатур штатов и названий - если что, я бы использовал двухбуквенную аббревиатуру штатов в качестве ключа.
Первичный ключ должен быть:
- уникальный (100% гарантировано! Не просто "почти" уникальный)
- Не NULL
Первичный ключ должен быть:
- стабильный, если это возможно (не менять - или, по крайней мере, не слишком часто)
Государственные двухбуквенные коды определенно предложили бы это - это могло бы быть кандидатом на естественный ключ. Ключ также должен быть маленьким - INT с 4 байтами идеален, двухбуквенный столбец CHAR(2) точно такой же. Я бы никогда не использовал поле VARCHAR(100) или что-то подобное в качестве ключа - он слишком неуклюжий, скорее всего, будет постоянно меняться - не очень хороший ключевой кандидат.
Поэтому, несмотря на то, что вам не нужно иметь автоинкрементный "искусственный" (суррогатный) первичный ключ, он часто является довольно хорошим выбором, поскольку никакие естественные данные на самом деле не соответствуют задаче быть первичным ключом, и вы хотите избегайте использования огромных первичных ключей с несколькими столбцами - они слишком неуклюжи и неэффективны.
Этот вопрос неоднократно задавался по SO и на протяжении многих лет был предметом многочисленных дискуссий среди (и между) разработчиков и администраторов баз данных.
Позвольте мне начать с того, что посылка вашего вопроса подразумевает, что один подход универсально превосходит другой... это редко бывает в реальной жизни. Суррогатные ключи и естественные ключи имеют свои применения и проблемы - и важно понимать, что они собой представляют. Независимо от того, какой выбор вы делаете в своей системе, имейте в виду, что согласованность приносит пользу - она упрощает понимание модели данных и упрощает разработку запросов и приложений. Я также хочу сказать, что я склонен предпочесть суррогатные ключи естественным ключам для ПК... но это не значит, что естественные ключи иногда не могут быть полезны в этой роли.
Важно понимать, что суррогатные и естественные ключи НЕ являются взаимоисключающими и во многих случаях они могут дополнять друг друга. Имейте в виду, что "ключ" для таблицы базы данных - это просто то, что однозначно идентифицирует запись (строку). Для одной строки вполне возможно иметь несколько ключей, представляющих различные категории ограничений, которые делают запись уникальной.
С другой стороны, первичный ключ - это особый уникальный ключ, который база данных будет использовать для обеспечения ссылочной целостности и для представления внешнего ключа в других таблицах. Для любой таблицы может быть только один первичный ключ. Основным качеством первичного ключа является то, что он должен быть на 100% уникальным и отличным от NULL. Желаемым качеством первичного ключа является его стабильность (неизменность). Хотя изменяемые первичные ключи возможны - они вызывают много проблем для базы данных, которых лучше избегать (каскадные обновления, сбои RI и т. Д.). Если вы решите использовать суррогатный первичный ключ для своих таблиц - вам также следует рассмотреть возможность создания уникальных ограничений, отражающих существование любых естественных ключей.
Суррогатные ключи полезны в случаях, когда:
- Естественные ключи не стабильны (значения могут меняться со временем)
- Натуральные ключи большие или громоздкие (несколько столбцов или длинные значения)
- Естественные ключи могут меняться со временем (столбцы добавляются / удаляются со временем)
Предоставляя короткое, стабильное, уникальное значение для каждой строки, мы можем уменьшить размер базы данных, повысить ее производительность и уменьшить изменчивость зависимых таблиц, в которых хранятся внешние ключи. Есть также преимущество ключевого полиморфизма, о котором я расскажу позже.
В некоторых случаях использование естественных ключей для выражения отношений между таблицами может быть проблематичным. Например, представьте, что у вас есть таблица PERSON с естественным ключом {LAST_NAME, FIRST_NAME, SSN}, Что произойдет, если у вас есть другая таблица GRANT_PROPOSAL, в которой вам нужно сохранить ссылку на Proposer, Reviewer, Approver и Authorizer. Теперь вам нужно 12 столбцов, чтобы выразить эту информацию. Вам также необходимо придумать какое-то соглашение об именах, чтобы определить, какие столбцы принадлежат какому-либо человеку. Но что, если вашей таблице PERSON потребовалось 6, или 8, или 24 столбца для естественного ключа? Это быстро становится неуправляемым. Суррогатные ключи решают такие проблемы, отделяя семантику (значение) ключа от его использования в качестве идентификатора.
Давайте также посмотрим на пример, который вы описали в своем вопросе.
Следует ли использовать двухсимвольную аббревиатуру состояния в качестве первичного ключа этой таблицы.
На первый взгляд это выглядит как поле аббревиатур, соответствующее требованиям хорошего первичного ключа. Он относительно короткий, его легко распространять как внешний ключ, он выглядит стабильным. К сожалению, вы не контролируете набор сокращений... почтовый сервис делает. И вот интересный факт: в 1973 году USPS изменил аббревиатуру Небраски с NB на NE, чтобы свести к минимуму путаницу с Нью-Брансуик, Канада. Мораль этой истории заключается в том, что естественные ключи часто находятся вне контроля базы данных... и со временем они могут меняться. Даже когда вы думаете, что они не могут. Эта проблема становится еще более очевидной для более сложных данных, таких как люди, продукты и т. Д. По мере развития бизнеса определения того, что делает такие объекты уникальными, могут измениться. И это может создать значительные проблемы для разработчиков моделей и разработчиков приложений.
Ранее я упоминал, что первичные ключи могут поддерживать полиморфизм ключей. Что это значит? Ну, полиморфизм - это способность одного типа, A, выглядеть и использоваться как другой тип, B. В базах данных это понятие относится к возможности объединять ключи из разных классов сущностей в одну таблицу. Давайте посмотрим на пример. Представьте на мгновение, что вы хотите иметь контрольный журнал в вашей системе, который определяет, какие объекты были изменены каким пользователем в какой день. Было бы неплохо создать таблицу с полями: {ENTITY_ID, USER_ID, EDIT_DATE}, К сожалению, используя естественные ключи, разные сущности имеют разные ключи. Итак, теперь нам нужно создать отдельную таблицу связей для каждого вида сущностей... и построить наше приложение таким образом, чтобы оно понимало разные виды сущностей и как формируются их ключи.
Не пойми меня неправильно. Я не сторонник использования суррогатных ключей ВСЕГДА. В реальном мире никогда, никогда, и всегда опасная позиция для принятия. Одним из самых больших недостатков суррогатных ключей является то, что они могут привести к таблицам, которые имеют внешние ключи, состоящие из множества "бессмысленных" чисел. Это может затруднить интерпретацию значения записи, поскольку для получения полной картины вам нужно объединять или искать записи из других таблиц. Это также может усложнить развертывание распределенной базы данных, поскольку присвоение уникальных инкрементных номеров на серверах не всегда возможно (хотя большинство современных баз данных, таких как Oracle и SQLServer, смягчают это посредством репликации последовательностей).
Я думаю, что использование слова "Primary" в фразе "Primary" Key в реальном смысле вводит в заблуждение.
Во-первых, используйте определение, что "ключ" - это атрибут или набор атрибутов, которые должны быть уникальными в таблице,
Тогда наличие любого ключа служит нескольким часто несовместимым целям.
Цель 1. Использовать в качестве условий соединения одну или несколько записей в дочерних таблицах, которые имеют отношение к этой родительской таблице. (Явное или неявное определение внешнего ключа в этих дочерних таблицах)
Цель 2. (связанная) Обеспечение того, чтобы дочерние записи имели родительскую запись в родительской таблице (дочерняя таблица FK должна существовать как ключ в родительской таблице)
Цель 3. Для повышения производительности запросов необходимо быстро найти конкретную запись / строку в таблице.
Цель 4. (Наиболее важно с точки зрения согласованности данных!) Чтобы обеспечить согласованность данных, предотвращая вставку повторяющихся строк, представляющих один и тот же логический объект, в таблицу. (Это часто называют "естественным" ключом и должен состоять из атрибутов таблицы (сущности), которые являются относительно инвариантными.)
Ясно, что любой не имеющий смысла, неестественный ключ (например, GUID или автоматически сгенерированное целое число) совершенно неспособен удовлетворить цель 4.
Но часто, во многих (большинстве) таблицах, полностью естественный ключ, который может предоставить #4, часто состоит из нескольких атрибутов и будет чрезмерно широким или настолько широким, что использование его в целях #1, #2 или #3 приведет к неприемлемому последствия производительности.
Ответ прост. Используйте оба. Используйте простой авто-генерирующий интегральный ключ для всех объединений и FK в других дочерних таблицах, но убедитесь, что у каждой таблицы, которая требует согласованности данных (очень мало таблиц), есть альтернативный естественный уникальный ключ, который предотвратит вставку несогласованных строк данных. Плюс, если у вас всегда есть оба, тогда все возражения против использования естественного ключа (что, если он изменится? Я должен менять каждое место, на которое он ссылается как FK), становятся спорными, поскольку вы не используете его для этого... Вы используете его только в одной таблице, где это PK, чтобы избежать противоречивых дублирующих данных...
Единственный раз, когда вы можете обойтись без обоих, это полностью автономная таблица, которая не участвует ни в каких отношениях с другими таблицами и имеет очевидный и надежный естественный ключ.
Как правило, числовой первичный ключ будет работать лучше, чем строка. Кроме того, вы можете создавать уникальные ключи, чтобы предотвратить проникновение дубликатов. Таким образом, вы получаете гарантию отсутствия дубликатов, но вы также получаете производительность чисел (по сравнению со строками в вашем сценарии).
По всей вероятности, основные базы данных имеют некоторые оптимизации производительности для целочисленных первичных ключей, которых нет для строковых первичных ключей. Но это только разумное предположение.
Я думаю, что есть две вещи, которые могут объяснить причину, почему иногда используются автоинкрементные ключи:
Космическое рассмотрение; Хорошо, ваше название штата не имеет большого значения, но место, которое оно занимает, может сложиться. Если вы действительно хотите сохранить состояние с его именем в качестве первичного ключа, тогда продолжайте, но оно займет больше места. Это не может быть проблемой в некоторых случаях, и это звучит как проблема старины, но привычка, возможно, укоренилась. А мы, программисты и DBA, любим привычки:D
Защитное соображение: у меня недавно была следующая проблема; у нас есть пользователи в базе данных, где электронная почта является ключом ко всей идентификации. Почему бы не сделать электронное письмо первичным ключом? за исключением внезапно возникающих пограничных случаев, когда один парень должен быть там дважды, чтобы иметь два разных адреса, и никто не говорил об этом в спецификациях, чтобы адрес не нормализовался, и есть такая ситуация, когда два разных электронных письма должны указывать на одного и того же человека... Через некоторое время вы перестаете вытягивать волосы и добавляете столбец чертовых целых чисел
Я не говорю, что это плохая или плохая привычка; Я уверен, что хорошие системы могут быть спроектированы на основе разумных первичных ключей, но эти два момента наводят меня на мысль, что страх и привычка - два среди виновных
Нет, абсолютно нет.
Наличие первичного ключа, который не может быть изменен, является хорошей идеей (UPDATE допустим для столбцов первичного ключа, но в целом может привести к путанице и может создать проблемы для дочерних строк). Но если в вашем приложении есть какой-то другой кандидат, который больше подходит, чем автоинкрементное значение, то вам, вероятно, следует использовать его вместо этого.
С точки зрения производительности, как правило, меньше столбцов лучше и, в частности, меньше индексов. Если у вас есть другой столбец с уникальным индексом, который никогда не может быть изменен каким-либо бизнес-процессом, то это может быть подходящий первичный ключ.
Говоря с точки зрения MySQL (Innodb), также неплохо использовать "реальный" столбец в качестве первичного ключа, а не "искусственный", поскольку InnoDB всегда кластеризует первичный ключ и включает его во вторичные индексы (то есть как он находит в них строки). Это дает ему возможность провести полезную оптимизацию с первичным ключом, что невозможно с любым другим уникальным индексом. Пользователи MSSQL часто выбирают кластеризацию первичного ключа, но он также может кластеризовать другой уникальный индекс.
РЕДАКТИРОВАТЬ:
Но если это небольшая база данных, и вы не слишком заботитесь о производительности или размере, добавление ненужного столбца автоинкремента не так уж и плохо.
Не автоинкрементное значение (например, UUID или какая-либо другая строка, сгенерированная в соответствии с вашим собственным алгоритмом) может быть полезно для распределенных, сегментированных или разнородных систем, где поддержание согласованного идентификатора автоинкремента трудно (или невозможно - подумайте о распределенном распределении). система, которая продолжает вставлять строки с обеих сторон сетевого раздела).
Да, по моему мнению, каждая таблица нуждается в автоматическом увеличении целочисленного ключа, потому что это делает как JOIN, так и (особенно) интерфейсное программирование намного, намного, намного проще. Другие чувствуют по-другому, но это более 20 лет опыта.
Единственное исключение - небольшие таблицы "код" или "поиск", в которых я готов заменить короткое (4 или 5 символов) значение кода TEXT. Я делаю это потому, что я часто использую многие из них в своих базах данных, и это позволяет мне представлять значимое отображение пользователю без необходимости искать описание в таблице поиска или присоединять его к результирующему набору. Ваш пример таблицы состояний будет соответствовать этой категории.
Это ключевой компонент реляционных баз данных. Целочисленное отношение к состоянию, а не полное имя состояния экономит кучу места в вашей базе данных! Представьте, что у вас есть миллион записей, ссылающихся на вашу таблицу состояний. Вы хотите использовать 4 байта для числа в каждой из этих записей, или вы хотите использовать полную хрень для байтов для каждого имени состояния?
Вот несколько практических соображений.
Большинство современных ORM (rails, django, hibernate и т. Д.) Работают лучше всего, когда в качестве первичного ключа используется один целочисленный столбец.
Кроме того, наличие стандартного соглашения об именах (например, id в качестве первичного ключа и table_name_id для внешних ключей) облегчает идентификацию ключей.