Перейти к содержанию

Введение

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

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

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

Понятие первичного ключа

Первичный ключ (Primary Key) — это поле (или набор полей), которое: - однозначно идентифицирует каждую запись в таблице; - не может содержать NULL; - должно быть уникальным.

Пример:

CREATE TABLE Students (
    id INT PRIMARY KEY,
    full_name VARCHAR(100)
);

Поле id — уникальный идентификатор студента.

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

1. Понятие внешнего ключа

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

2. Пример связи «один ко многим»

Таблица групп:

CREATE TABLE Groups (
    id INT PRIMARY KEY,
    group_name VARCHAR(50)
);

Таблица студентов:

CREATE TABLE Students (
    id INT PRIMARY KEY,
    full_name VARCHAR(100),
    group_id INT,
    FOREIGN KEY (group_id) REFERENCES Groups(id)
);

Здесь: - Groups.id — первичный ключ; - Students.group_id — внешний ключ; - один студент принадлежит одной группе; - одна группа может содержать много студентов.

Типы связей

1. Один к одному (1:1)

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

2. Один ко многим (1:N)

Самый распространённый тип связи. Пример: - Клиент — Заказы - Группа — Студенты - Категория — Товары

Внешний ключ размещается в таблице «многие».

3. Многие ко многим (M:N)

Одна запись первой таблицы может быть связана со многими записями второй таблицы и наоборот. Пример: - Студенты — Дисциплины - Товары — Заказы

Реализуется через промежуточную таблицу.

Пример:

CREATE TABLE Student_Course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (course_id) REFERENCES Courses(id)
);

Ограничения внешнего ключа

SQL позволяет управлять поведением при изменении или удалении данных.

1. ON DELETE

  • RESTRICT — запретить удаление (по умолчанию);
  • CASCADE — удалить связанные записи;
  • SET NULL — установить NULL;
  • SET DEFAULT — установить значение по умолчанию.

Пример:

FOREIGN KEY (group_id)
REFERENCES Groups(id)
ON DELETE CASCADE

2. ON UPDATE

Определяет поведение при изменении значения первичного ключа.

ON UPDATE CASCADE

Создание связи в SQL

Связь между таблицами создаётся с помощью ограничения FOREIGN KEY. Связь можно задать: 1. Во время создания таблицы (CREATE TABLE); 2. После создания таблицы (ALTER TABLE).

Создание связи при CREATE TABLE

Общий синтаксис:

CREATE TABLE имя_таблицы (
    поле1 тип,
    поле2 тип,
    ...,
    FOREIGN KEY (поле_внешнего_ключа)
        REFERENCES имя_родительской_таблицы(поле_первичного_ключа)
        ON DELETE действие
        ON UPDATE действие
);

Пример

CREATE TABLE Students (
    id INT PRIMARY KEY,
    full_name VARCHAR(100),
    group_id INT,
    CONSTRAINT fk_students_group
        FOREIGN KEY (group_id)
        REFERENCES Groups(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Разберём команду: - CONSTRAINT fk_students_group — имя ограничения (можно задать своё); - FOREIGN KEY (group_id) — поле в текущей таблице; - REFERENCES Groups(id) — таблица и поле, на которое идёт ссылка; - ON DELETE — поведение при удалении записи в родительской таблице; - ON UPDATE — поведение при изменении ключа.

Создание связи через ALTER TABLE

Если таблица уже существует, связь можно добавить позже.

Синтаксис:

ALTER TABLE имя_таблицы
ADD CONSTRAINT имя_ограничения
FOREIGN KEY (поле)
REFERENCES родительская_таблица(поле)
ON DELETE действие
ON UPDATE действие;

Пример

ALTER TABLE Students
ADD CONSTRAINT fk_students_group
FOREIGN KEY (group_id)
REFERENCES Groups(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Возможные действия ON DELETE и ON UPDATE

Действие Описание
RESTRICT Запретить удаление/изменение, если есть связанные записи
CASCADE Автоматически удалить/изменить связанные записи
SET NULL Установить NULL в дочерней таблице
NO ACTION Аналогично RESTRICT (в MariaDB)

Пример CASCADE: Если удалить группу, автоматически удалятся все студенты этой группы.

Требования для создания связи

  1. Тип данных внешнего ключа и первичного ключа должны совпадать.
  2. Родительское поле должно быть PRIMARY KEY или UNIQUE.
  3. Таблицы должны использовать движок InnoDB.

Проверить движок таблицы можно так:

SHOW TABLE STATUS;

Проверка работы связи

Если попытаться вставить запись с несуществующим внешним ключом:

INSERT INTO Students (id, full_name, group_id)
VALUES (1, 'Иванов Иван', 999);

MariaDB выдаст ошибку, если группы с id = 999 не существует. Это и есть механизм обеспечения ссылочной целостности.

Проверка связей

Если попытаться: - добавить запись с несуществующим внешним ключом; - удалить запись, на которую есть ссылка;

SQL выдаст ошибку (если не указано иное поведение). Это называется обеспечение ссылочной целостности.

Практическое значение связей

Связи обеспечивают: - корректность данных; - отсутствие «висячих» записей; - целостность системы; - возможность выполнять JOIN-запросы.

Без связей база данных превращается в набор несвязанных таблиц.

Итоги

  • Связи создаются через PRIMARY KEY и FOREIGN KEY.
  • Основные типы связей: 1:1, 1:N, M:N.
  • Для связи M:N используется промежуточная таблица.
  • ON DELETE и ON UPDATE управляют поведением при изменениях.
  • Связи обеспечивают ссылочную целостность базы данных.