Как создать связи в SQL: внешний ключ и связь многие ко мног

Связи в SQL создают через внешние ключи — FOREIGN KEY. Внешний ключ показывает, что значение в одной таблице должно ссылаться на существующую строку в другой таблице. Например, заказ связан с клиентом через orders.customer_id -> customers.id

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

Содержание

Связь один ко многим
Проверить связь через JOIN
Связь многие ко многим
Что делать при удалении строк
Мини-практика
Частые ошибки
Что почитать дальше по SQL

Связь один ко многим

Создадим клиентов:

CREATE TABLE customers (
    id int IDENTITY(1,1) PRIMARY KEY,
    name nvarchar(100) NOT NULL
);

Создадим заказы:

CREATE TABLE orders (
    id int IDENTITY(1,1) PRIMARY KEY,
    customer_id int NOT NULL,
    total_sum decimal(10,2) NOT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);

Теперь SQL Server не даст создать заказ с customer_id, которого нет в customers. Это защита от «висящих» заказов без клиента

Проверить связь через JOIN

Добавим данные:

INSERT INTO customers (name)
VALUES (N'Анна'), (N'Илья');

INSERT INTO orders (customer_id, total_sum)
VALUES (1, 1500.00), (1, 700.00), (2, 900.00);

Проверим:

SELECT
    c.name,
    o.id AS order_id,
    o.total_sum
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;

Если результат показывает заказы рядом с именами клиентов, связь работает и читается через JOIN

Связь многие ко многим

Для связи многие ко многим не нужно добавлять в одну таблицу список id через запятую. Делают третью таблицу

CREATE TABLE students (
    id int IDENTITY(1,1) PRIMARY KEY,
    name nvarchar(100) NOT NULL
);

CREATE TABLE courses (
    id int IDENTITY(1,1) PRIMARY KEY,
    title nvarchar(100) NOT NULL
);

CREATE TABLE student_courses (
    student_id int NOT NULL,
    course_id int NOT NULL,
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_student_courses_students
        FOREIGN KEY (student_id)
        REFERENCES students(id),
    CONSTRAINT fk_student_courses_courses
        FOREIGN KEY (course_id)
        REFERENCES courses(id)
);

Таблица student_courses хранит пары: какой студент записан на какой курс. Составной первичный ключ не дает добавить одну и ту же пару дважды

Что делать при удалении строк

Если удалить клиента, у которого есть заказы, база может запретить удаление. Это нормально: внешний ключ защищает связанные данные. Можно настроить каскадное удаление, но его нельзя включать вслепую

Пример каскада:

FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE

Такой вариант означает: при удалении клиента удалить и его заказы. В некоторых системах это удобно, в других опасно. Для заказов, платежей и истории лучше особенно внимательно думать, можно ли удалять связанные данные автоматически

Мини-практика

Создайте таблицы authors и books, где один автор может иметь много книг. Добавьте внешний ключ books.author_id -> authors.id, вставьте автора и книгу, затем попробуйте вставить книгу с несуществующим author_id

Ожидаемо база должна остановить неправильную вставку. Это и есть смысл связи: она не только помогает делать JOIN, но и защищает целостность данных