Ключи в БД

Введение: Как отличить одного человека от другого

Представьте, что вы пришли в компанию, где работает 1000 человек. Как отличить Ивана Петрова от другого Ивана Петрова? По паспорту. По табельному номеру. По отпечатку пальца.

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

Для этого придумали ключи.

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

Суперключ (Superkey)

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

Это самое широкое понятие. Любой ключ – это суперключ. Но не любой суперключ - это “хороший” ключ.

Простыми словами: Суперключ – это “у меня есть способ отличить одного человека от другого. Я могу использовать имя, фамилию, дату рождения и город. Или только паспорт. Или только номер телефона. Или вообще все данные сразу”.

Пример:

Рассмотрим таблицу “Студенты”:

Какие комбинации столбцов являются суперключами?

• {Phone} - да, телефон уникален для каждого студента.
• {FirstName, LastName, Year, Phone} - да, это вообще все столбцы.
• {FirstName, Phone} - да, телефон уже все решает.
• {LastName, Phone} - да.
• {Year, Phone} - да.
• {FirstName, LastName, Year} - НЕТ. Видите двух Иванов Петровых 2000 года рождения? Они неразличимы без телефона.

Важный вывод: Суперключ может быть “жирным” (содержать лишние столбцы). Например, {FirstName, LastName, Year, Phone} – это суперключ, но столбцы FirstName, LastName, Year там лишние, потому что Phone уже все делает.

Потенциальный ключ (Candidate Key)

Потенциальный ключ (Candidate Key) – это минимальный суперключ. Он удовлетворяет трем условиям:

1. Уникальность: значения уникальны для каждой строки.
2. Минимальность (неприводимость): нельзя убрать ни один столбец из этого набора, не потеряв уникальность.
3. Не null: столбцы не могут быть пустыми (NOT NULL).

Потенциальный ключ – это “кандидат” на то, чтобы стать первичным ключом.

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

Пример:

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

Потенциальный ключ: {Phone}.

Но есть нюанс. А если студент может не иметь телефона? Тогда Phone может быть NULL. А потенциальный ключ не может содержать NULL. В этом случае у таблицы может не быть естественного потенциального ключа.

Что тогда? Тогда добавляют искусственный столбец специально для идентификации. Например, student_id.

Первичный ключ (Primary Key)

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

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

Что может быть первичным ключом

Естественный ключ (Natural Key): уже существующее уникальное поле.

Примеры:

• Номер паспорта (уникальный, не меняется)
• ИНН
• Номер заказа (внутри компании)

Искусственный ключ (Surrogate Key): специально созданное поле, которого не было в реальном мире.

Примеры:

• id - автоинкрементирующееся целое число
• UUID - глобально уникальный идентификатор

Почему искусственный ключ часто лучше

Рекомендация для аналитика: В 90% случаев используйте искусственный первичный ключ id. Это стандарт.

Пример

Таблица “Пользователи”:

user_id – первичный ключ. Искусственный, никогда не меняется, всегда уникален.

Альтернативный ключ (Alternate Key)

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

Пример:

Таблица “Пользователи”:

• user_id - первичный ключ (искусственный).
• email - альтернативный ключ (уникальный, не NULL).
• phone - альтернативный ключ (уникальный, не NULL).

Зачем нужны альтернативные ключи

• Чтобы запретить дублирование. База данных не даст создать двух пользователей с одинаковым email.
• Чтобы быстро искать. Индекс на альтернативном ключе ускорит поиск по email.
• Чтобы входить в систему. Пользователь может входить и по email, и по телефону – оба поля уникальны.

Внешний ключ (Foreign Key)

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

Простыми словами: Внешний ключ – это “я знаю, что этот заказ принадлежит клиенту с id=5”. В таблице заказов хранится client_id, а этот client_id ссылается на запись в таблице клиентов.

Пример:

Таблица “Клиенты” (parent - родительская):

Таблица “Заказы” (child - дочерняя):

client_id в таблице “Заказы” - это внешний ключ. Он ссылается на client_id в таблице “Клиенты”.

Что дает внешний ключ

1. Связь: Мы знаем, что заказ 101 сделал клиент 1 (Иван Петров).
2. Ссылочная целостность: СУБД не даст создать заказ с client_id = 999, если клиента с таким id не существует.
3. Каскадные операции: Можно настроить, чтобы при удалении клиента удалялись и его заказы (CASCADE DELETE).

Обозначения

Составной ключ (Composite Key)

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

Когда нужен составной ключ

Чаще всего в таблицах для связи “многие-ко-многим”.

Пример: Таблица “Заказы_Товары” (связывает заказы и товары).

Здесь составной первичный ключ: (order_id, product_id). Пара “заказ-товар” уникальна. Один и тот же товар не может быть дважды в одном заказе.

Важное правило для составных ключей

Вторая нормальная форма (2NF) требует, чтобы все неключевые поля зависели от ВСЕГО составного ключа, а не от его части. В примере выше поле quantity зависит от всей пары (order_id, product_id) – это правильно.

Все типы ключей в одной таблице

Таблица “Сотрудники”:

Разберем каждый ключ:

Какие еще есть ключи (не в таблице):

• Суперключи: {employee_id}, {passport_number}, {phone}, {employee_id, name}, {passport_number, name} и так далее – любые комбинации, включающие уникальный столбец.
• Потенциальные ключи: {employee_id}, {passport_number}, {phone} – минимальные суперключи.

Таблица сравнения ключей

Типичные ошибки в работе с ключами

Ошибка 1: Нет первичного ключа в таблице

Создают таблицу без уникального идентификатора. Как тогда обновлять конкретную строку? Как удалять? Как связывать?

Как исправить: Всегда добавляйте id как первичный ключ.

Ошибка 2: Использовать естественный ключ там, где он может измениться

Сделать email первичным ключом. А потом пользователь захочет сменить email. Придется обновлять внешние ключи во всех связанных таблицах. Боль и страдания.

Как исправить: Используйте искусственный id как первичный ключ. email пусть будет альтернативным (уникальным, но не первичным).

Ошибка 3: Не объявлять внешние ключи

Логически связь есть (в коде приложения проверяют), но в базе данных внешний ключ не объявлен. СУБД не контролирует целостность. Могут появиться “висячие” записи (заказ без клиента).

Как исправить: Всегда объявляйте внешние ключи в базе данных.

Ошибка 4: Путать первичный и внешний ключ

Думать, что client_id в таблице заказов - это первичный ключ. Нет, это внешний ключ. Первичный ключ в таблице заказов – order_id.

Как исправить: Запомнить: PK – в своей таблице. FK – ссылается на PK другой таблицы.

Ошибка 5: Игнорировать составные ключи

Пытаться вставить в таблицу “Заказы_Товары” один и тот же товар в один и тот же заказ дважды. Без составного первичного ключа это возможно.

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

Резюме для системного аналитика

1. Ключи – это фундамент реляционных баз данных. Они нужны для идентификации строк и связывания таблиц.

2. Иерархия ключей: Суперключ -> Потенциальный ключ -> Первичный ключ. Суперключ – самый широкий, первичный – самый конкретный.

3. Первичный ключ (PK) – главный. У каждой таблицы должен быть первичный ключ. Лучше всего – искусственный id.

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

5. Альтернативные ключи (AK) – это тоже уникальные поля. Email, телефон, номер паспорта - они уникальны, но не являются первичным ключом.

6. Составные ключи – из нескольких столбцов. Часто нужны в таблицах для связи “многие-ко-многим”.