Таблицы, первичный и внешний ключ

Фундамент реляционной базы данных за один урок

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

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

Таблица, строка, столбец

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

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

Первичный ключ (PRIMARY KEY)

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

В нашей базе у сотрудников ключ — emp_id, у отделов — dept_id. База данных сама следит за правилами: вставить двух сотрудников с одинаковым emp_id она не даст, и оставить emp_id пустым — тоже.

Хороший первичный ключ стабилен и бессмысленен сам по себе. Номер emp_id = 3 не меняется, даже если сотрудник сменит имя или отдел. Поэтому в ключи берут технические идентификаторы, а не имена или телефоны.

Внешний ключ (FOREIGN KEY)

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

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

Соединяем таблицы по ключу

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

SELECT e.emp_name,
       e.salary,
       d.dept_name
FROM employees AS e
JOIN departments AS d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_name;

В результате каждый сотрудник встал рядом со своим отделом. Заметьте: сотрудник Martin Blank в ответ не попал — у него dept_id равен NULL (он пока ни к какому отделу не привязан), и совпадения не нашлось. Так внешний ключ превращает разрозненные таблицы в единую картину.

Целостность данных

Главная польза ключей — целостность: база сама не даёт записать бессмыслицу. Если внешний ключ включён, нельзя поставить сотруднику dept_id = 99, когда отдела с таким номером не существует — база отвергнет такую строку. Это называется ссылочной целостностью: ссылка всегда ведёт на реально существующую запись.

Создадим свою маленькую базу «авторы и книги» и убедимся, что связь работает. Здесь author_id в таблице books — внешний ключ на authors.

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name      TEXT NOT NULL
);

CREATE TABLE books (
    book_id   INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(author_id)
);

INSERT INTO authors (author_id, name) VALUES
    (1, 'Лев Толстой'),
    (2, 'Александр Пушкин');

INSERT INTO books (book_id, title, author_id) VALUES
    (10, 'Война и мир',     1),
    (11, 'Анна Каренина',   1),
    (12, 'Евгений Онегин',  2);

SELECT b.title AS книга,
       a.name  AS автор
FROM books AS b
JOIN authors AS a
  ON b.author_id = a.author_id
ORDER BY b.book_id;

Запрос показал три книги с именами авторов. Имя «Лев Толстой» хранится в базе ровно один раз, а две его книги ссылаются на него через число 1. Захотим исправить имя автора — поправим одну строку, и оно тут же изменится для всех его книг.

Частые ошибки

• Брать в первичный ключ изменчивые данные (имя, e-mail, телефон). Они меняются — и ключ ломается. Используйте стабильный идентификатор.
• Дублировать название отдела в таблице сотрудников вместо ссылки dept_id. Это ведёт к рассинхрону: где-то «Sales», где-то «Продажи».
• Путать направление ссылки: внешний ключ живёт в «дочерней» таблице (employees) и указывает на первичный ключ «родительской» (departments), а не наоборот.
• Забывать про NULL: если внешний ключ может быть пустым, такие строки выпадут из обычного JOIN.

Коротко

• Таблица описывает одну сущность; строка — это запись, столбец — атрибут.
• PRIMARY KEY уникально и без пропусков идентифицирует каждую строку — это «паспорт» записи.
• FOREIGN KEY хранит ссылку на первичный ключ другой таблицы, связывая данные без дублирования.
• Ключи обеспечивают целостность: база не пускает дубликаты ключей и ссылки на несуществующие записи.
• JOIN по ключу собирает данные из разных таблиц в одну осмысленную выборку.