первичные, уникальные, родительские и внешние ключи

Наличие взаимосвязей, перекрестных ссылок между таблицами — это одно из фундаментальных свойств, отличающих реляционную базу данных от простого набора таблиц. Для реализации таких взаимосвязей почти все СУБД позволяют определять в таблицах первичные и внешние ключи и имеют в своем составе механизмы поддержания ссылочной целостности.

Первичный ключ

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

Уникальный ключ

Уникальный ключ — это столбец или группа столбцов, значения (комбинация значений для группы столбцов) которых не могут повторяться. Отличия уникального ключа от первичного — в том, что:

  • уникальных ключей для одной таблицы может быть несколько (вопросик на засыпку для тех, кто прочитал статью про нормализацию: правила какой нормальной формы при этом будут нарушены? 😉
  • уникальные ключи могут иметь значения NULL, при этом если имеется несколько строк со значениями уникального ключа NULL, такие строки согласно стандарту SQL 92 считаются различными (уникальными).

Внешний ключ

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

Внешний ключ — это столбец или группа столбцов, ссылающиеся на столбец или группу столбцов другой (или этой же) таблицы. Таблица, на которую ссылается внешний ключ, называется родительской таблицей, а столбцы, на которые ссылается внешний ключ — родительским ключом. Родительский ключ должен быть первичным или уникальным ключом, значения же внешнего ключа могут повторяться хоть сколько раз. То есть с помощью внешних ключей поддерживаются связи «один ко многим». Типы данных (а в некоторых СУБД и размерности) соответствующих столбцов внешнего и родительского ключа должны совпадать.

И самое главное. Все значения внешнего ключа должны совпадать с каким-либо из значений родительского ключа. (Заметим в скобках насчет совпадения / несовпадения: нюансы возникают, когда в значениях столбцов вторичного ключа встречается NULL. Давайте пока в эти нюансы вдаваться не будем). Появление значений внешнего ключа, для которых нет соответствующих значений родительского ключа, недопустимо. Вот тут-то мы плавно переходим к понятию ссылочной целостности.

Ссылочная целостность

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

Более интересные моменты возникают, когда мы удаляем или изменяем строки родительской таблицы. Как при этом не допустить появления \»болтающихся в воздухе\» строк дочерней таблицы? Для этого существуют правила ссылочной целостности ON UPDATE и ON DELETE, которые, по стандарту SQL 92, могут содержать следующие инструкции:

  • CASCADE — обеспечивает автоматическое выполнение в дочерней таблице тех же изменений, которые были сделаны в родительском ключе. Если родительский ключ был изменен — ON UPDATE CASCADE обеспечит точно такие же изменения внешнего ключа в дочерней таблице. Если строка родительской таблицы была удалена, ON DELETE CASCADE обеспечит удаление всех соответствующих строк дочерней таблицы.
  • SET NULL — при удалении строки родительской таблицы ON DELETE SET NULL установит значение NULL во всех столбцах вторичного ключа в соответствующих строках дочерней таблицы. При изменении родительского ключа ON UPDATE SET NULL установит значение NULL в соответствующих столбцах соответствующих строк (о как:) дочерней таблицы.
  • SET DEFAULT — работает аналогично SET NULL, только записывает в соответствующие ячейки не NULL, а значения, установленные по умолчанию.
  • NO ACTION (установлено по умолчанию) — при изменении родительского ключа никаких действий с внешним ключом в дочерней таблице не производится. Но если изменение значений родительского ключа приводит к нарушению ссылочной целосности (т.е. к появлению «висящих в воздухе» строк дочерней таблицы), то СУБД не даст произвести такие изменения родительской таблицы.

Ну а сейчас — от общего к частному.

Ключи и ссылочная целостность в MySQL и Oracle

Oracle поддерживает первичные, уникальные, внешние ключи в полном объеме. Oracle поддерживает следующие правила ссылочной целостности:

  • NO ACTION (устанавливается по умолчанию) в более жестком, чем по стандарту SQL 92, варианте: запрещается изменение и удаление строк родительской таблицы, для которых имеются связанные строки в дочерних таблицах.
  • ON DELETE CASCADE.

Более сложные правила ссылочной целостности в Oracle можно реализовать через механизм триггеров.

MySQL версии 4.1 (последняя на момент написания статьи стабильная версия) позволяет в командах CREATE / ALTER TABLE задавать фразы REFERENCES / FOREIGN KEY, но в работе никак их не учитывает и реально внешние ключи не создает. Соответственно правила ссылочной целостности, реализуемые через внешние ключи, в MySQL не поддерживаются. И все заботы по обеспечению целостности и непротиворечивости информации в базе MySQL ложатся на плечи разработчиков клиентских приложений.

Разработчики MySQL обещают реализовать работу с внешними ключами и поддержание ссылочной целостности в версии 5.0. Что ж, когда версия MySQL 5.0 станет стабильной — посмотрим, что там в итоге получится. Очень, очень хотелось бы, чтобы MySQL поддерживала ссылочную целостность (без ущерба для производительности:).

www.wwwmaster.ru

Уникальный ключ в условиях распределенной БД / Хабрахабр

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

Какие требования (кроме уникальности) могут предъявляться к ключам?

Ключ должен монотонно увеличиваться

Зачем это нужно:

  • Автоматическая естественная сортировка (в том числе хронологическая)
  • Быстрее вставка, чем при случайных ключах

Вышесказанное актуально только для индексов с использованием деревьев.

Ключ должен формироваться на стороне клиента

Зачем это нужно:

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

Ограничение по размеру ключа (например, только 64 бита)

Зачем это нужно:

  • В некоторых случаях 128- или 160-битные идентификаторы могут быть проблемой. Например, Sphinx search поддерживает только целочисленные идентификаторы документов (64 бита в 64-битной версии).
  • Индекс с 32- или 64-битными ключами теоретически должен работать быстрее, чем с ключами произвольной длины.
  • Индекс с 32- или 64-битными ключами компактнее и, в результате, целиком помещается в память (актуально для больших объемов данных).

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

Генерация ID на стороне приложения (UUID и т.п.)

С UUID все относительно просто — берем библиотеку и генерируем ключи на стороне приложения. UUID широко используется во многих системах. Значение формируется таким образом, что коллизии невозможны.

Минусы:

    • Длина стандартного UUID 128 бит (которые, на самом деле, часто хочется хранить прямо в виде строки шестнадцатеричных цифр, а это уже 32 байта)

    • Как правило, UUID не дает естественной сортировки ключей

Отдельный сервис, для генерации ключей

Такой вариант используют, например, Flick и Twitter.

Минусы:

    • усложнение системы из-за введения дополнительных компонентов

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

Автоинкремент на стороне базы данных по диапазонам значений

Весь диапазон значений делится на поддиапазоны и каждый шард (узел кластера) отвечает за свой диапазон, например:

    1 - 0        .. 3FFFFFFF,
    2 - 40000000 .. 7FFFFFFF,
    3 - 80000000 .. BFFFFFFF,
    4 - C0000000 .. FFFFFFFF

Минусы:
    • БД должна поддерживать автоинкремент (не применимо для многих NoSQL-хранилищ)
естественная сортировка невозможна
    • Клиент «не знает» ключ до вставки объекта. Чтобы узнать ключ нужно делать отдельный запрос.
    • Практически невозможно увеличить количество узлов кластера

Aвтоинкремент от Instagram

Об этом решении рассказано в техническом блоге команды Instgram (photo sharing application).

Они используют 64-битный ключ, который формируется на стороне БД (PostgreSQL) и состоит из битовых полей

63                          31                          0
|======|======|======|======|======|======|======|======|
|        CUSTOM TIMESTAMP          | SHARD ID  |  AUTO  |

где
CUSTOM TIMESTAMP (41 бита) — время в миллисекундах от 2011-01-01 00:00:00
SHARD ID (13 бит) — идентификатор логического раздела (число шардов больше, чем число физических узлов)
AUTO (10 бит) — последовательность (sequence), уникальная в пределах логического раздела (шарда)

Плюсы (по сравнению с автоинкрементом по диапазонам):

    • Автоматическая хронологическая сортировка

А вы какие варианты знаете и используете?

habr.com

primary-key — В чем разница между основным ключом и уникальным ключом

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

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

В соответствии с перспективой РСУБД:
Key (a.k.a Ключ кандидата), значение или набор значений, которые однозначно идентифицируют объект. Для Db-таблицы существует так много ключей и может иметь право на Первичный ключ.
Так что все ключи, первичный ключ, уникальный ключ и т.д. Совместно называются клавишами-кандидатами.

Для таблицы выбранный ключ DBA-кандидата называется Первичный ключ, другие ключи-кандидаты называются вторичными ключами.

Разница между основным ключом и уникальным ключом

1. Поведение: Основной ключ используется для идентификации строки (записи) в таблице, тогда как Уникальный ключ предназначен для предотвращения дублирования значений в столбце.

2. Индексирование: По умолчанию Sql-engine создает кластерный указатель первичного ключа, если он не существует, и некластеризованный индекс на уникальном ключе.

3. Nullability: Первичный ключ не включает значения Null, в то время как уникальный ключ может.

4. Существование: Таблица может иметь не более одного первичного ключа, но может иметь несколько уникальных клавиш.

5. Модифицируемость: Вы не можете изменить или удалить первичные значения, но значения уникальной клавиши могут.

Для получения дополнительной информации с примерами:
http://dotnetauthorities.blogspot.in/2013/11/Microsoft-SQL-Server-Training-Online-Learning-Classes-Integrity-Constraints-PrimaryKey-Unique-Key_27.html

qaru.site

sql — В чем разница между первичным ключом и уникальным ограничением?

TL; DR Многое может быть подразумевается PRIMARY KEY (уникальность, ссылочная, ненулевая, кластеризация и т.д.), но ничего, что нельзя явно указать с помощью UNIQUE.

Я предлагаю, чтобы, если вы такой кодер, который любит удобство SELECT * FROM..., не имея необходимости перечислять все эти надоедливые столбцы, тогда PRIMARY KEY — это только то, что вам нужно.


у relvar может быть несколько ключей, но мы выбираем только один для подчеркивания и назовите это первичным ключом. Выбор произволен, поэтому концепция первичного не очень важна с логической точки зрения. Однако общая концепция ключа очень важна! термин ключ-кандидат означает точно такое же, как ключ (то есть добавление кандидата не имеет реального значения — он был предложен Тедом Коддом потому что он рассматривал каждый ключ в качестве кандидата на назначение в качестве первичный ключ)… SQL позволяет подмножество столбцов таблицы объявленный как ключ для этой таблицы. Это также позволяет одному из них быть назначенный в качестве первичного ключа. Указание ключа, который будет для определенного удобства в связи с другими ограничения, которые могут потребоваться

Что такое ключ? Хью Дарвен


обычно… выделить один ключ как первичный ключ (и любой другой ключи для рассматриваемого relvar, как говорят, являются альтернативными ключами). Но следует ли выбрать какой-то ключ как первичный, и если да, то какой, являются по существу психологическими проблемами, выходящими за рамки реляционной модели как таковой. Что касается хорошей практики, то большинство базовых Вероятно, relvars должен иметь первичный ключ, но, повторяя это правило, если это правило, на самом деле не является реляционной проблемой как таковой… Сильный рекомендация [для пользователей SQL]: для базовых таблиц, во всяком случае, используйте PRIMARY KEY и/или UNIQUE, чтобы гарантировать, что каждый такой таблица имеет хотя бы один ключ.

SQL и реляционная теория: как написать точный код SQL
По С. J. Дата

В стандартном SQL PRIMARY KEY

  • подразумевает уникальность, но вы можете указать это явно (используя UNIQUE).
  • подразумевает NOT NULL, но вы можете указать это явно при создании столбцов (но вы должны избегать нулей!)
  • позволяет опустить столбцы в FOREIGN KEY, но вы можете явно указать их.
  • может быть объявлен только для одного ключа в таблице, но непонятно, почему (Codd, который первоначально предложил концепцию, не налагал такого ограничения).

В некоторых продуктах PRIMARY KEY подразумевается табличный кластеризованный индекс, но вы можете указать это явно (вы можете не захотеть, чтобы первичный ключ был кластеризованным индексом!)

Для некоторых людей PRIMARY KEY имеет чисто психологическое значение:

  • они думают, что это означает, что ключ будет ссылаться на внешний ключ (это было предложено Codd, но фактически не принято стандартными SQL и поставщиками SQL).
  • они думают, что это означает единственный ключ таблицы (но отказ от принудительного применения других ключей-кандидатов приводит к потере целостности данных).
  • они думают, что это подразумевает «суррогатный» или «искусственный» ключ, не имеющий никакого значения для бизнеса (но фактически налагает на предприятие нежелательное значение, будучи подверженным пользователям).

qaru.site

sql — Как сделать составной ключ уникальным?

Я не совсем понимаю, почему вам нужно то, что вы описали, но я бы посмотрел на модель следующим образом…

У вас есть студенты
— Это разные объекты, а не составные части других объектов
— У них есть свои свойства; имя, дата рождения и т.д.

У вас есть классы
— Это группы студентов
— Каждый учебный год того же «класса» в нем участвуют разные ученики
— У них также есть свои свойства; класс, подкласс и т.д.

У вас есть дополнительное свойство в вашей модели, которое я обычно не использовал бы
— Если у класса 20 учеников, каждый из них идентифицируется с вторичным идентификатором от 1 до 20

Это даст мне следующие таблицы измерений

Student                  Class                     Grade              SubGrade
-----------------------  ------------------------  -----------------  -----------------
id          INT PK       id           INT PK       id    INT PK       id    INT PK
first_name  VARCHAR(45)  name         VARCHAR(45)  name  VARCHAR(45)  name  VARCHAR(45)
last_name   VARCHAR(45)  grade_id     INT FK       desc  VARCHAR(45)  desc  VARCHAR(45)
etc, etc                 subgrade_id  INT FK       etc, etc           etc, etc

Таблица Class имела бы уникальное ограничение на (grade_id, subgrade_id), так что только один класс мог бы быть 7b.

Затем вам нужно связать учащихся с их классами с помощью таблицы фактов…

Class_Membership
-----------------------
id               INT PK
student_id       INT FK
class_id         INT FK
academic_year    INT

Если студент должен быть только в одном классе в любом учебном году, вы должны установить уникальное ограничение на (student_id, academic_year).

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

В равной степени у вас могут быть студенты, которые переходят от 7b до 7c в середине года. В этом случае таблица Class_Membership может иметь поле start_date и, возможно, поле end_date.

Однако ничто из этого не создает непосредственно поле id_class (1-20 для класса с 20 учениками). Лично у меня не было бы такого поля, поле id из таблицы Class_Membership может обслуживать большинство тех же функций и, возможно, дополнительные функции. Однако, если это необходимо, вы можете просто добавить его в таблицу Class_Membership

Class_Membership
-----------------------
id               INT PK
student_id       INT FK
class_id         INT FK
academic_year    INT
class_member_id  INT

Тогда у вас также может быть уникальное ограничение на (academic_year, class_id, class_member_id).

Здесь существует довольно много гибкости, в зависимости от вашей конкретной модели реального мира и ваших конкретных потребностей. Но, надеюсь, этот пример — хорошее начало для вас; Таблицы размеров, перечисляющие сущности, и таблицу фактов (или таблицы), связывающую эти сущности вместе и/или дальнейшее описание объектов.

qaru.site

sql-server — В чем разница между Первичным ключом и уникальным ключевым ограничением?

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

Уникальный ключ — это более общий случай, когда ключ не может иметь повторяющиеся значения. В большинстве случаев люди не могут иметь одинаковые номера социального страхования в отношении одной и той же юрисдикции (международное дело может отличаться). Следовательно, если мы будем хранить номера социального страхования, мы хотели бы моделировать их как уникальные, так как любой случай их соответствия существующему числу явно ошибочен. Имена пользователей обычно должны быть уникальными, так что здесь другой случай. Внешние идентификаторы (идентификаторы, используемые другой системой, стандартом или протоколом) также являются уникальными, например. существует только один язык с данным кодом ISO 639, поэтому, если мы будем хранить коды ISO 639, мы будем моделировать это как уникальное.

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

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

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

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

  • Скорость поиска. Ключевая эффективность может быть важной, поскольку они используются во многих предложениях WHERE и (чаще) во многих JOIN s. В частности, JOINS, скорость поиска может быть жизненно важной. Влияние будет зависеть от деталей реализации, а разные базы данных будут различаться в зависимости от того, как они будут обрабатывать разные типы данных (у меня было бы несколько сомнений с точки зрения производительности при использовании большого фрагмента текста в качестве первичного ключа в Postgres, где я мог бы указать использование хэш, но я бы очень не решался это сделать в SQLServer [Edit: for «large». Я думаю о размере имени пользователя, а не о размере всего скандинавского Eddas!).

  • Частота ключа — это только интересные данные. Например, с таблицей языков и таблицей комментариев на этом языке очень часто единственной причиной, по которой я хотел бы присоединиться к языковой таблице при работе с таблицей комментариев, является либо получение кода языка, либо ограничение запрос к тем, у кого есть конкретный код языка. Другая информация о языке, скорее всего, будет использоваться гораздо реже. В этом случае при присоединении к коду, вероятно, будет менее эффективно, чем присоединение к числовому идентификатору, установленному из столбца IDENTITY, имеющему код как первичный ключ, и, следовательно, как то, что хранится в столбце внешнего ключа в комментариях table — полностью устранит необходимость любого JOIN, с большим коэффициентом полезного действия. Чаще всего, хотя я хочу получить больше информации из соответствующих таблиц, поэтому сделать JOIN более эффективным является более важным.

qaru.site

[sqlperformance] Разница между ключом, основным ключом, уникальным ключом и индексом в MySQL [unique-key] [primary-key]

KEY и INDEX являются синонимами в MySQL. Они означают одно и то же. В базах данных вы должны использовать indexes для повышения скорости поиска данных. Обычно индекс создается в столбцах, используемых в предложениях JOIN , WHERE и ORDER BY .

Представьте, что у вас есть таблица, называемая users и вы хотите искать всех пользователей, имеющих фамилию «Смит». Без индекса база данных должна будет пройти через все записи таблицы: это медленно, потому что чем больше записей у вас есть в вашей базе данных, тем больше работы она должна найти, чтобы найти результат. С другой стороны, индекс поможет базе данных быстро перейти к соответствующим страницам, где хранятся записи «Смит». Это очень похоже на то, как мы, люди, просматриваем каталог телефонной книги, чтобы найти кого-то по фамилии: мы не начинаем поиск по каталогу с обложки на обложку, поскольку мы вставляем информацию в некотором порядке, чтобы мы могли используйте для быстрого перехода на страницы «S».

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

Также обратите внимание, что столбцы, определенные как первичные ключи или уникальные ключи, автоматически индексируются в MySQL.


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

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


Уникальные ключи: столбцы, в которых нет двух строк

Первичный ключ: сбор минимального количества столбцов, который может однозначно идентифицировать каждую строку в таблице (т. Е. Две строки не похожи во всех столбцах, составляющих первичный ключ). В таблице может быть несколько первичных ключей. Если существует уникальный ключ, то это первичный ключ (а не «первичный ключ») в таблице. Если не существует уникального ключа, то для идентификации строки, такой как (first_name, last_name, father_name, mother_name), может потребоваться более одного значения столбца, в некоторых таблицах это первичный ключ.

Индекс: используется для оптимизации запросов. Если вы собираетесь многократно искать или сортировать результаты на основе некоторого столбца (например, в основном люди собираются искать студентов по имени, а не по их номеру), тогда его можно оптимизировать, если значения столбца все » индексируется «, например, с помощью алгоритма двоичного дерева.


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

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


Уникальный ключ:

  1. Более одного значения может быть нулевым.
  2. Никакие два кортежа не могут иметь одинаковые значения в уникальном ключе.
  3. Один или несколько уникальных ключей могут быть объединены для формирования первичного ключа, но не наоборот.

Основной ключ

  1. Может содержать более одного уникального ключа.
  2. Уникально представляет кортеж.

code-examples.net

Отправить ответ

avatar
  Подписаться  
Уведомление о