Какие знаешь уровни связей в SQL?

Уровни Связей (Integrity Levels) в SQL

Уровни связей в SQL определяют, насколько строго база данных следит за целостностью отношений между таблицами. Это важный механизм для обеспечения data integrity.

1. Без поддержки связей (No Constraints)

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

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT  -- Внешний ключ без constraint
);

-- Можно добавить order с несуществующим user_id
INSERT INTO orders (id, user_id) VALUES (1, 999);

Проблемы:

• Orphaned records (записи без родителей)
• Данные становятся грязными
• Сложнее исправлять ошибки

2. Уровень RESTRICT

RESTRICT — это уровень, который запрещает удаление или изменение родительской записи, если на неё ссылаются дочерние:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    dept_id INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE RESTRICT
);

-- Попытка удалить department, если есть employees
DELETE FROM departments WHERE id = 1;
-- ERROR: Cannot delete or update a parent row: a foreign key constraint fails

Характеристики:

• Операция отклоняется (transaction откатывается)
• Данные остаются в целостном состоянии
• Нужно вручную разрешить конфликт

3. Уровень CASCADE

CASCADE — автоматически каскадно удаляет или обновляет все зависимые записи:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    dept_id INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

-- Удаление department удалит все связанные employees
DELETE FROM departments WHERE id = 1;
-- Все employees с dept_id = 1 будут удалены

Когда использовать:

• Иерархические отношения (комментарии к посту)
• Корзина покупок при удалении заказа
• История и логи

Опасность:

• Может привести к потере большого объёма данных
• Нужен хороший backup

4. Уровень SET NULL

SET NULL — при удалении родителя, внешний ключ обнуляется (null):

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    amount DECIMAL(10, 2),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id) 
        ON DELETE SET NULL
);

-- При удалении customer, его orders останутся, но customer_id = NULL
DELETE FROM customers WHERE id = 1;
-- SELECT * FROM orders; => customer_id = NULL для этого заказа

Когда использовать:

• Опциональные связи
• Данные должны сохраниться, но без привязки
• История заказов при удалении покупателя

Требование:

• Столбец должен быть NOT NULL исключен (т.е. может быть NULL)

5. Уровень SET DEFAULT

SET DEFAULT — устанавливает значение по умолчанию:

CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT DEFAULT 0
);

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE items (
    id INT PRIMARY KEY,
    product_id INT,
    category_id INT DEFAULT 0,
    FOREIGN KEY (category_id) REFERENCES categories(id) 
        ON DELETE SET DEFAULT
);

6. Уровень NO ACTION

NO ACTION похож на RESTRICT, но проверка может быть отложена (DEFERRABLE):

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

CREATE TABLE transactions (
    id INT PRIMARY KEY,
    account_id INT,
    FOREIGN KEY (account_id) REFERENCES accounts(id) 
        ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
);

-- В PostgreSQL это полезно для сложных операций
BEGIN;
    DELETE FROM accounts WHERE id = 1;
    DELETE FROM transactions WHERE account_id = 1;
COMMIT;  -- Проверка происходит в конце

Сравнительная таблица

Пример: сложная структура с разными уровнями

// JPA аннотации для Java
@Entity
public class User {
    @Id
    private Long id;
    private String name;
    
    @OneToMany(mappedBy = "user", cascade = CascadeType.REMOVE)
    private List<Order> orders;  // Удаление юзера удаляет заказы
}

@Entity
public class Order {
    @Id
    private Long id;
    
    @ManyToOne
    @JoinColumn(name = "user_id", nullable = false)
    private User user;  // RESTRICT на уровне БД
    
    @OneToMany(mappedBy = "order", cascade = CascadeType.REMOVE)
    private List<OrderItem> items;  // CASCADE для items
}

Рекомендации

1. RESTRICT — по умолчанию (самое безопасное)
2. CASCADE — только для иерархических данных
3. SET NULL — для опциональных связей
4. Всегда используй внешние ключи — это гарантия целостности на уровне БД
5. Тестируй сценарии удаления — убедись, что поведение соответствует требованиям