Примеры SQL-кода создания всех типов связей в MySQL

При работе с базами данных часто приходится использовать несколько таблиц для хранения данных. Более или менее крупный проект требует логической связи между таблицами, для того чтоб упростить и ускорить поиск нужной информации. Для этого есть такая вещ как связи таблиц mysql, создаётся связь с помощью внешних ключей (FOREIGN KEY). Ниже представлены примеры создания таблиц и организации связей one-to-one, one-to-many и many-to-many с использованием FOREIGN KEY в MySQL.

Связь один к одному (One-to-One)

Связь один к одному используется когда для строки записи в одной таблице используется одна строка записи в другой таблице. Например, у пользователя есть только один определенный ранг/роль из 5 возможных.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    full_name VARCHAR(255),
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Данный sql код дает нам такую логику связи:

• user_profiles.user_id имеет UNIQUE, поэтому одному пользователю соответствует только один профиль.
• ON DELETE CASCADE автоматически удаляет профиль при удалении пользователя.

Связь один ко многим (One-to-Many)

Такая связь один ко многим применяется когда одна запись в таблице связана с несколькими записями из другой таблицы. Например, категория имеет множество статей или товаров.

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    title VARCHAR(255),
    content TEXT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Такая связь mysql таблиц дает нам следующее:

• Одна категория может иметь множество статей.
• При удалении категории поле category_id станет NULL.

Связь многие ко многим (Many-to-Many)

Для такой связи многие ко многим нужна третья таблица как промежуточная. Именно она будет держать в себе связь двух других таблиц. Например, есть множество товаров и есть множество тегов, как тег может содержать в себе много товаров так и товар может содержать много тегов.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(100) NOT NULL
);

CREATE TABLE product_tags (
    product_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (product_id, tag_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Происходит после создания таких таблиц следующее:

• Любой товар может иметь множество тегов.
• Любой тег может относиться к множеству товаров.
• PRIMARY KEY (product_id, tag_id) запрещает дублирование связей.

Связь — самосвязь (Self-Reference/Рекурсивная связь)

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

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    parent_id INT DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Достаточно в одной записи указать просто идентификатор родительской записи, если она есть.

• parent_id указывает на id в той же таблице.
• Категория может иметь подкатегории.

Связь с таблицей-справочником (Lookup table)

Используется для ограниченного набора значений, например, для установки статуса заказа, наличия товара на складе и т.п.

CREATE TABLE order_statuses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status_name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status_id INT NOT NULL,
    amount DECIMAL(10,2),
    FOREIGN KEY (status_id) REFERENCES order_statuses(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Статус не может быть удалён, если он используется (RESTRICT).

Наглядная таблица связей MySQL

Базовое понимание логики связей между таблицами mysql даст вам большой опыт профессиональной разработки проектов, где все будет работать быстрее и понятнее. А также, советуем обратить внимание на типы данных связываемых ячеек, про типы данных мы рассказывали в прошлом материале: типы данных в mysql. Приводим в пример таблицу: