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

BestProg

Реляционные базы данных. Понятие ключа. Виды ключей. Отношения. Главная и подчиненная таблицы

В данной теме, на примере двух таблиц, определяются основные понятия реляционных баз данных, а именно:

Содержание

Поиск на других ресурсах:

Входные данные

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

Таблицы имеют следующую структуру.

Таблица «Работник». Содержит данные о работнике

Таблица «Зарплата». Содержит сведения о заработной плате работников.

Вопрос/ответ

1. Что такое первичный ключ в таблице базы данных? Для чего используются первичные ключи?

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

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

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

Пример. Для таблицы «Работник» можно ввести дополнительное поле, которое будет первичным ключом. Однако, поле (атрибут) «Табельный номер» также обеспечивает уникальность. Так как, теоретически, не может быть двух одинаковых табельных номеров. На практике могут быть случаи, что один и тот же табельный номер будет введен по ошибке и совпадут значения всех полей таблицы. В результате возникнут два одинаковых записи в таблице. Во избежание такой ошибки, лучше создать в таблице дополнительное поле-счетчик, которое обеспечит уникальность.

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

2. Что такое отношение (связь) между таблицами (relationship)? Пример

Таблицы в реляционной модели данных могут иметь связи между собой. Такие связи называются отношениями. Для таблиц «Работник» и «Зарплата» можно установить связь по полю «Табельный номер».

Пример. Проанализируем таблицы «Работник» и «Зарплата». В этих таблицах можно установить отношение между таблицами на основе поля «Табельный номер». То есть, связь между таблицами происходит на основе поля (атрибуту) «Табельный номер».

Это означает следующее. Если нужно найти начисленную заработную плату в таблице «Зарплата» для работника Иванов И.И., то нужно выполнить следующие действия:

Рис. 1. Иллюстрация связи между таблицами. Табельный номер 2145 таблицы «Работник» отображается в таблице «Зарплата»

Рис. 2. Связь (отношение) между полями таблиц

3. Что такое внешний ключ (foreign key)? Пример

Понятие «внешний ключ» есть важным при рассмотрении связанных таблиц.

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

Пример. Пусть между таблицами «Работник» и «Зарплата» существует взаимосвязь по полю «Табельный номер». В этом случае, поле «Табельный номер» таблицы «Работник» может быть первичным ключом, а поле «Табельный номер» таблицы «Зарплата» внешним ключом. Это означает, что значения поля «Табельный номер» таблицы «Зарплата» заменяются значениями поля «Табельный номер» таблицы «Работник».

4. Что такое рекурсивный внешний ключ?

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

5. Могут ли первичный и внешний ключи быть простыми или составными (сложными)?

Первичный, вторичный и внешний ключи могут быть как простыми так и составными (сложными). Простые ключи – это ключи, которые содержат только одно поле (один атрибут). Составные (сложные) ключи – это ключи, которые содержат несколько полей (атрибутов).

6. Какое отличие между искусственным и естественным ключом? Пример

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

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

Пример. В таблице «Работник» естественном ключом есть поле (атрибут) «Табельный номер». Поле «Табельный номер» есть само по себе уникальным, так как не может быть двух работников с одинаковым табельным номером.

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

где поле «Номер» есть искусственным ключом, который обеспечивает уникальность.

7. Какие существуют способы выбора первичного ключа?

Существует 3 способа выбора первичного ключа:

8. Что означают термины «главная таблица» (master) и «подчиненная таблица» (detail)?

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

Пример. Если рассмотреть таблицы «Работник» и «Зарплата», то таблица «Работник» есть главной, а таблица «Зарплата» есть подчиненной.

9. Какие существуют типы отношений (связей) между таблицами?

Существует 4 основных типа отношений между таблицами:

Пример. Если рассмотреть отношение между таблицами «Работник» и «Зарплата», то это отношения есть типа «один ко многим». Таблица «Работник» есть главной. Таблица «Зарплата» есть подчиненной.

Источник

Первичный ключ и внешний ключ таблиц реляционных баз данных

Вступление

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

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

Типы данных в базах

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

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

Что такое первичный ключ и внешний ключ таблиц реляционных баз данных

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

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

Primary key (PK) очень важен для каждой таблицы. Поясню почему.

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

Ключ внешний

Foreign key, кратко FK. Обеспечивает однозначную логическую связь, между таблицами одной БД.

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

Более сложный пример.

Две таблицы данных: Люди и Номера телефонов.

Таблица: Люди

Таблица: Номера телефонов

В таблице Номера телефонов PK уникален. FK этой таблицы является PK таблицы Люди. Связь между номерами телефонов и людьми обеспечивает FK таблицы телефонов. То есть:

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

Источник

Sysadminium

База знаний системного администратора

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

Из статьи вы узнаете, что такое первичный и внешний ключ в SQL. Зачем они нужны и как их использовать. Я покажу на практике как их использовать в PostgreSQL.

Теория

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

К первичному ключу предъявляют следующее требование:

Первичный ключ может быть:

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

Связь между таблицами

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

Например, у вас есть таблица “Ученики” (pupils) и выглядит она следующим образом:

Таблица pupils

И есть таблица “Успеваемость” (evaluations):

Таблица evaluations

В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице “Успеваемость” не может содержаться ФИО, которого нет в таблице “ Ученики“. Ведь нельзя поставить ученику оценку, которого не существует.

Первичным ключом в нашем случае может выступать поле “ФИО” в таблице “ Ученики“. А внешним ключом будет “ФИО” в таблице “Успеваемость“. При этом, если мы удаляем запись о каком-то ученике из таблицы “Ученики“, то все его оценки тоже должны удалиться из таблицы “Успеваемость“.

Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:

Теперь давайте попробуем создать эти две таблички и попробуем с ними поработать.

Практика

Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:

Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.

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

Индекс в свою очередь наложил ещё одно ограничение – записи в поле full_name должны быть уникальны.

Следующим шагом создадим таблицу evaluations:

В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.

Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.

Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.

Заполнение таблиц и работа с ними

Заполним таблицу “pupils“:

Заполним таблицу “evaluations“:

А теперь попробуем поставить оценку не существующему ученику:

Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.

Теперь удалим какого-нибудь ученика из таблицы pupils:

И посмотрим на строки в таблице evaluations:

Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.

Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:

Ничего не вышло, так как такая запись уже существует в поле full_name, а это поле у нас имеет индекс. Значит значения в нём должны быть уникальные.

Составной первичный ключ

Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.

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

Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.

Теперь посмотрим на структуры этих таблиц:

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

Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:

И также по второй таблице:

Удаление таблиц

Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:

Поэтому удалим наши таблицы в следующем порядке:

Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:

Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.

Создание связи в уже существующих таблицах

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

Вначале удалим оставшуюся таблицу:

И сделаем таблицы без ключей:

Теперь создадим первичный ключ в таблице pupils:

И создадим внешний ключ в таблице evaluations:

Посмотрим что у нас получилось:

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

Дополнительно про первичный и внешний ключ sql можете почитать тут.

Источник

SQL ключи во всех подробностях

В Интернете полно догматических заповедей о том, как нужно выбирать и использовать ключи в реляционных базах данных. Иногда споры даже переходят в холивары: использовать естественные или искусственные ключи? Автоинкрементные целые или UUID?

Прочитав шестьдесят четыре статьи, пролистав разделы пяти книг и задав кучу вопросов в IRC и StackOverflow, я (автор оригинальной статьи Joe «begriffs» Nelson), как мне кажется, собрал куски паззла воедино и теперь смогу примирить противников. Многие споры относительно ключей возникают, на самом деле, из-за неправильного понимания чужой точки зрения.

Содержание

Что же такое «ключи»?

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

Для примера рассмотрим таблицу для подсчёта карт в карточной игре:

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

Мы можем и должны задать базе данных это ограничение, добавив следующее:

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

В более общей ситуации, когда нужно отслеживать несколько колод карт, можно добавить новое поле и записывать сколько раз мы видели карту:

Ограничения уникальности

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

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

Вот два примера таблиц с несколькими ключами.

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

Любопытный случай первичных ключей

То, что в предыдущем разделе мы назвали просто «ключами», обычно называется «потенциальными ключами» (candidate keys). Термин «candidate» подразумевает, что все такие ключи конкурируют за почётную роль «первичного ключа» (primary key), а оставшиеся назначаются «альтернативными ключами» (alternate keys).

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

Термин «ключ» означал ключ сортировки файла, который был нужен для выполнения любых операций обработки в последовательной файловой системе. Набор перфокарт считывался в одном и только в одном порядке; невозможно было «вернуться назад». Первые накопители на магнитных лентах имитировали такое же поведение и не позволяли выполнять двунаправленный доступ. Т.е., первоначальный Sybase SQL Server для чтения предыдущей строки требовал «перемотки» таблицы на начало.

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

В большинстве баз данных первичные ключи сохранились как пережиток прошлого, и едва ли обеспечивают что-то, кроме отражения или определения физического расположения. Например, в таблице PostgreSQL объявление первичного ключа автоматически накладывает ограничение NOT NULL и определяет внешний ключ по умолчанию. К тому же первичные ключи являются предпочтительными столбцами для оператора JOIN.

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

Нахождение естественных ключей

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

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

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

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

Например, база данных членов хобби-клуба может иметь уникальность в двух колонках — first_name, last_name. При небольшом объёме данных дубликаты маловероятны, и до возникновения реального конфликта использовать такой ключ вполне разумно.

С ростом базы данных и увеличением объёма информации, выбор естественного ключа может стать сложнее. Хранимые нами данные являются упрощением внешней реальности, и не содержат в себе некоторые аспекты, которыми различаются объекты в мире, такие как их изменяющиеся со временем координаты. Если у объекта отсутствует какой-либо код, то как различить две банки с напитком или две коробки с овсянкой, кроме как по их расположению в пространстве или по небольшим различиям в весе или упаковке?

Именно поэтому органы стандартизации создают и наносят на продукцию различительные метки. На автомобилях штампуется Vehicle Identification Number (VIN), в книгах печатается ISBN, на упаковке пищевых товаров есть UPC. Вы можете возразить, что эти числа не кажутся естественными. Так почему же я называю их естественными ключами?

Естественность или искусственность уникальных свойств в базе данных относительна к внешнему миру. Ключ, который при своём создании в органе стандартизации или государственном учреждении был искусственным, становится для нас естественным, потому что в целом мире он становится стандартом и/или печатается на объектах.
Существует множество отраслевых, общественных и международных стандартов для различных объектов, в том числе для валют, языков, финансовых инструментов, химических веществ и медицинских диагнозов. Вот некоторые из значений, которые часто используются в качестве естественных ключей:

Искусственные ключи

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

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

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

Суррогаты

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

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

Искусственные ключи нужно выбирать, учитывая возможные способы их передачи, чтобы минимизировать опечатки и ошибки. Надо учесть, что ключ могут произносить, читать напечатанным, отправлять по SMS, читать написанным от руки, вводить с клавиатуры и встраивать в URL. Дополнительно, некоторые искусственные ключи, например, номера кредитных карт, содержат контрольную сумму, чтобы при возникновении определённых ошибок их можно было хотя бы распознать.

Эта функция является обратной самой себе (т.е. pseudo_encrypt(pseudo_encrypt(x)) = x ). Точное воспроизведение функции является своего рода безопасностью через неясность, и если кто-нибудь догадается, что вы использовали сеть Фейстеля из документации PostgreSQL, то ему будет легко получить исходную последовательность. Однако вместо (((1366 * r1 + 150889) % 714025) / 714025.0) можно использовать другую функцию с областью значений от 0 до 1, например, просто поэкспериментировать с числами в предыдущем выражении.

Вот, как использовать pseudo_encrypt:

В предыдущем примере для short_id использовались целые значения обычного размера, для bigint есть другие функции Фейстеля, например XTEA.

Ещё один способ запутать последовательность целых чисел заключается в преобразовании её в короткие строки. Попробуйте воспользоваться расширением pg_hashids:

Здесь снова будет быстрее хранить в таблице сами целые числа и преобразовывать их по запросу, но замерьте производительность и посмотрите, имеет ли это смысл на самом деле.

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

Если у вас есть искусственный ключ и вы не объявляете естественные ключи, когда они существуют, то оставляете последние незащищёнными:

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

Также стоит объявлять несколько искусственных ключей, если они есть. Например, у организации есть кандидаты на работу (Applicants) и сотрудники (Employees). Каждый сотрудник когда-то был кандидатом, и относится к кандидатам по своему собственному идентификатору, который также должен быть и ключом сотрудника. Ещё один пример, можно задать идентификатор сотрудника и имя логина как два ключа в Employees.

Суррогатные ключи

Как уже упоминалось, важный тип искусственного ключа называется «суррогатный ключ». Он не должен быть кратким и передаваемым, как другие искусственные ключи, а используется как внутренняя метка, всегда идентифицирующая строку. Он используется в SQL, но приложение не обращается к нему явным образом.

Если вам знакомы системные колонки (system columns) из PostgreSQL, то вы можете воспринимать суррогаты почти как параметр реализации базы данных (вроде ctid), который однако никогда не меняется. Значение суррогата выбирается один раз для каждой строки и потом никогда не изменяется.

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

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

Автоинкрементные bigint

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

Недостатки последовательных ключей:

Давайте рассмотрим другой вариант: использование больших целых чисел (128-битных), генерируемых в соответствии со случайным шаблоном. Алгоритмы генерации таких универсальных уникальных идентификаторов (universally unique identifier, UUID) имеют чрезвычайно малую вероятность выбора одного значения дважды, даже при одновременном выполнении на двух разных процессорах.

В таком случае, UUID кажутся естественным выбором для использования в качестве суррогатных ключей, не правда ли? Если вы хотите пометить строки уникальным образом, то ничто не сравнится с уникальной меткой!

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

Для начала, расскажу о надуманных причинах. Некоторые люди думают, что UUID — это строки, потому что они записываются в традиционном шестнадцатеричном виде с дефисом: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Действительно, некоторые базы данных не имеют компактного (128-битного) типа uuid, но в PostgreSQL он есть и имеет размер двух bigint, т.е., по сравнению с объёмом прочей информации в базе данных, издержки незначительны.

Ещё UUID незаслуженно обвиняется в громоздкости, но кто будет их произносить, печатать или читать? Мы говорили, что это имеет смысл для показываемых искусственных ключей, но никто (по определению) не должен увидеть суррогатный UUID. Возможно, с UUID будет иметь дело разработчик, запускающий команды SQL в psql для отладки системы, но на этом всё. А разработчик может ссылаться на строки и с помощью более удобных ключей, если они заданы.

Реальная проблема с UUID в том, что сильно рандомизированные значения приводят к увеличению объёма записи (write amplification) из-за записей полных страниц в журнал с упреждающей записью (write-ahead log, WAL). Однако, на самом деле снижение производительности зависит от алгоритма генерации UUID.

Давайте измерим write amplification. По правде говоря, проблема в старых файловых системах. Когда PostgreSQL выполняет запись на диск, она изменяет «страницу» на диске. При отключении питания компьютера большинство файловых систем всё равно сообщит об успешной записи ещё до того, как данные безопасно сохранились на диске. Если PostgreSQL наивно воспримет такое действие завершённым, то при последующей загрузке системы база данных будет повреждена.

Раз PostgreSQL не может доверять большинству ОС/файловых систем/конфигураций дисков в вопросе обеспечения неразрывности, база данных сохраняет полное состояние изменённой дисковой страницы в журнал с упреждающей записью (write-ahead log), который можно будет использовать для восстановления после возможного сбоя. Индексирование сильно рандомизированных значений наподобие UUID обычно затрагивает кучу различных страниц диска и приводит к записи полного размера страницы (обычно 4 или 8 КБ) в WAL для каждой новой записи. Это так называемая полностраничная запись (full-page write, FPW).

Некоторые алгоритмы генерации UUID (такие, как «snowflake» от Twitter или uuid_generate_v1() в расширении uuid-ossp для PostgreSQL) создают на каждой машине монотонно увеличивающиеся значения. Такой подход консолидирует записи в меньшее количество страниц диска и снижает FPW.

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

Перед тек, как добавить UUID в каждую таблицу, находим текущую позицию write-ahead log.

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

Я провёл тесты трёх сценариев:

И вот результаты замеров скорости:

График скорости вставки UUID

Вот статистика WAL для каждого из способов:

Результаты подтверждают, что gen_random_uuid создаёт существенную активность в WAL из-за полностраничных образов (full-page images, FPI), а другие способы этим не страдают. Конечно, в третьем методе я просто запретил базе данных делать это. Однако запрет FPW совсем не то, что стоило бы использовать в реальности, если только вы не полностью уверены в файловой системе и конфигурации дисков. В этой статье утверждается, что ZFS может быть безопасным для отключения FPW, но пользуйтесь им с осторожностью.

Явным победителем в моём бенчмарке оказался uuid_generate_v1() – он быстр и не замедляется при накоплении строк. Расширение uuid-ossp по умолчанию установлено в таких облачных базах данных, как RDS и Citus Cloud, и будет доступно без дополнительных усилий.

В документация есть предупреждение о uuid_generate_v1:

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

Итоги и рекомендации

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

Для каждой таблицы:

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

Обсуждать подобные профессиональные вопросы мы предлагаем на наших конференциях. Если у вас за плечами большой опыт в ИТ-сфере, наболело, накипело и хочется высказаться, поделиться опытом или где-то попросить совета, то на майском фестивале конференций РИТ++ будут для этого все условия, 8 тематических направлений начиная от фронтенда и мобильной разработки, и заканчивая DevOps и управлением. Подать заявку на выступление можно здесь.

Источник