Ключи и связи между таблицами в SQL | Java программирование

1.

Что такое ключ в базе данных и какие бывают ключи в SQL?

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

В SQL объявляют три вида ключей:

PRIMARY KEY — первичный: однозначно определяет строку, уникален, не NULL, один на таблицу;
UNIQUE — уникальный: запрещает повтор значений;
FOREIGN KEY — внешний: связывает строку с другой таблицей.

Остальные названия описывают, как ключ выбран и из чего состоит: потенциальный (candidate) — любой столбец, который мог бы стать первичным ключом; альтернативный (alternate) — потенциальный, не выбранный первичным, объявляется как UNIQUE; естественный — из данных предметной области (email); суррогатный — искусственный id; составной — из нескольких столбцов, уникальна только их комбинация, например PRIMARY KEY (topic_id, tag_id).

2.

Чем естественный ключ отличается от суррогатного и какой выбрать для первичного ключа?

Естественный ключ берётся из данных предметной области, например email. Суррогатный — искусственный столбец без смысла в предметной области, например id INT AUTO_INCREMENT.

Естественный ключ может измениться: пользователь сменил email — придётся обновлять все ссылки. Суррогатный номер присваивается один раз.
В связанных таблицах естественный ключ занимает строку до 100 символов в каждой ссылке, суррогатный — 4 байта (INT) или 8 байт (BIGINT).
Естественный ключ сам гарантирует уникальность данных; при суррогатном на email нужно отдельное ограничение UNIQUE.
В Hibernate/JPA суррогатный ключ — стандартный вариант: поле с @Id и @GeneratedValue.

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

3.

Чем UNIQUE отличается от PRIMARY KEY и может ли уникальный столбец содержать NULL?

Оба ограничения запрещают повторы и автоматически создают индекс, но:

PRIMARY KEY в таблице один, UNIQUE — сколько угодно;
PRIMARY KEY не допускает NULL, а столбец с UNIQUE может содержать NULL, если не объявлен NOT NULL;
внешний ключ обычно ссылается на PRIMARY KEY, на UNIQUE — редко.

UNIQUE защищает альтернативные ключи — логин и email пользователя. В MySQL, PostgreSQL и Oracle уникальный столбец может хранить сколько угодно NULL: по стандарту SQL два неизвестных значения не равны. SQL Server допускает только один NULL — частая неожиданность при переносе схемы.

Ограничению можно дать имя: CONSTRAINT uq_users_phone UNIQUE (phone). Оно видно в сообщении об ошибке 1062, и по нему ограничение удаляют.

4.

Что такое внешний ключ (FOREIGN KEY) и что MySQL требует для его создания?

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

CONSTRAINT fk_topics_author FOREIGN KEY (author_id)
    REFERENCES users (id)

Требования MySQL:

обе таблицы на движке InnoDB — MyISAM принимает синтаксис, но молча игнорирует ключ;
типы столбцов совпадают: INT ссылается на INT, а не на BIGINT или INT UNSIGNED (иначе ошибка 3780); у строк совпадают кодировка и collation;
ссылка идёт на PRIMARY KEY или UNIQUE родительской таблицы (с MySQL 8.4 ссылка на неуникальный индекс по умолчанию запрещена);
родительская таблица создаётся раньше дочерней (иначе ошибка 1824).

Индекс по столбцу внешнего ключа MySQL создаёт сам, если подходящего нет. SET FOREIGN_KEY_CHECKS = 0 отключает проверку, и после включения уже вставленные ссылки не перепроверяются.

5.

Какие действия можно задать в ON DELETE и ON UPDATE и когда их использовать?

ON DELETE и ON UPDATE задают, что сделать с дочерними строками, когда родительскую строку удаляют или меняют её ключ:

RESTRICT — операция отклоняется с ошибкой 1451; для данных, которые нельзя терять (заказы, платежи);
NO ACTION — в InnoDB работает как RESTRICT и действует по умолчанию;
CASCADE — дочерние строки удаляются или обновляются вместе с родительской; когда они без родителя не имеют смысла (сообщения темы, теги темы);
SET NULL — внешний ключ становится NULL, столбец должен допускать NULL; когда связь необязательна (сообщение остаётся, автор неизвестен);
SET DEFAULT — InnoDB такую таблицу не создаст, в MySQL не используется.

CONSTRAINT fk_messages_topic FOREIGN KEY (topic_id)
    REFERENCES topics (id) ON DELETE CASCADE,
CONSTRAINT fk_messages_author FOREIGN KEY (author_id)
    REFERENCES users (id) ON DELETE SET NULL

CASCADE срабатывает по цепочке на каждом уровне, поэтому его ставят только там, где дочерние строки действительно не нужны. ON UPDATE CASCADE нужен редко: суррогатный id не меняется; он полезен при ссылке на естественный ключ, например на код страны.

6.

Какие бывают связи между таблицами и как каждую реализовать в SQL?

Три вида связей:

Один к одному (1:1), пользователь — профиль: внешний ключ, который одновременно первичный или UNIQUE (user_profiles.user_id). Без UNIQUE получится один ко многим.
Один ко многим (1:M), пользователь — его темы: внешний ключ на стороне «многие» (topics.author_id), а не в users.
Многие ко многим (M:N), темы — теги: одним внешним ключом не выразить, нужна промежуточная таблица с двумя внешними ключами и составным первичным ключом.

CREATE TABLE topic_tags (
    topic_id INT NOT NULL,
    tag_id   INT NOT NULL,
    PRIMARY KEY (topic_id, tag_id),
    CONSTRAINT fk_topic_tags_topic FOREIGN KEY (topic_id)
        REFERENCES topics (id) ON DELETE CASCADE,
    CONSTRAINT fk_topic_tags_tag FOREIGN KEY (tag_id)
        REFERENCES tags (id) ON DELETE CASCADE
);

Составной ключ не даёт пометить тему одним тегом дважды. В JPA такую связь описывает @ManyToMany с @JoinTable.

Ключи и связи между таблицами в SQL - Вопросы