Как работает связанность в базах данных?

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

Типы связанных отношений:

• Один к одному (One-to-One): Каждая запись в первой таблице связана только с одной записью во второй, и наоборот. Редко используются, обычно такие данные можно объединить в одну таблицу.
• Один ко многим (One-to-Many): Каждая запись в первой таблице может быть связана с несколькими записями во второй таблице, но каждая запись во второй таблице связана только с одной записью в первой. Наиболее распространенный тип.
• Многие ко многим (Many-to-Many): Каждая запись в первой таблице может быть связана с несколькими записями во второй, и каждая запись во второй таблице может быть связана с несколькими записями в первой. Реализуется через промежуточную (связующую) таблицу, содержащую внешние ключи из обеих связанных таблиц.

Механизм работы:

При установке связи с помощью FOREIGN KEY база данных обеспечивает ссылочную целостность. Это означает, что система управления базами данных (СУБД) проверяет, что значения во внешнем ключе существуют в столбце, на который он ссылается (обычно первичный ключ).

Опции ON DELETE и ON UPDATE определяют поведение при удалении или обновлении записи в связанной таблице (таблице с первичным ключом):

• RESTRICT (по умолчанию): Запрещает удаление или обновление записи, если на неё существует ссылка во внешней таблице.
• CASCADE: Удаляет или обновляет связанные записи во внешней таблице.
• SET NULL: Устанавливает значение внешнего ключа NULL в связанных записях во внешней таблице. Возможно только при разрешении NULL для столбца внешнего ключа.
• NO ACTION: Аналогично RESTRICT, но проверка выполняется в конце транзакции.
• SET DEFAULT: Устанавливает значение внешнего ключа в значение по умолчанию для столбца во внешней таблице.

Пример создания связи:

-- Создание таблицы "пользователи"
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

-- Создание таблицы "заказы"
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

Этот пример демонстрирует связь "один ко многим" между пользователями и заказами. FOREIGN KEY (user_id) в таблице orders ссылается на user_id в таблице users. ON DELETE CASCADE указывает, что при удалении пользователя будут удалены и все его связанные заказы.

Получение данных из связанных таблиц осуществляется с помощью операторов JOIN.

SELECT users.username, orders.order_date
FROM users
JOIN orders ON users.user_id = orders.user_id;

Таблицы:

Таблица users
user_id	username
1	Иванов
2	Петров

Таблица orders
order_id	user_id	order_date
101	1	2023-10-27
102	1	2023-10-28
103	2	2023-10-27

Результат JOIN:

username	order_date
Иванов	2023-10-27
Иванов	2023-10-28
Петров	2023-10-27

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