Что такое ON DELETE SET NULL?

Что такое ON DELETE SET NULL

ON DELETE SET NULL — это SQL constraint (ограничение) в отношениях между таблицами базы данных. Когда родительская запись удаляется, все foreign key в дочерней таблице, которые указывали на эту запись, автоматически устанавливаются в NULL. Это способ поддержания referential integrity (целостности ссылок) в БД.

Основные концепции

Foreign Key (внешний ключ) — это столбец в одной таблице, который ссылается на первичный ключ другой таблицы. ON DELETE SET NULL определяет поведение при удалении родительской записи.

SQL пример: ON DELETE SET NULL

-- Создаём таблицу компаний (родительская)
CREATE TABLE companies (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Создаём таблицу сотрудников (дочерняя)
CREATE TABLE employees (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    company_id UUID,
    -- Внешний ключ с ON DELETE SET NULL
    CONSTRAINT fk_employees_company 
    FOREIGN KEY (company_id) 
    REFERENCES companies(id) 
    ON DELETE SET NULL  -- ← Это ограничение
);

-- Вставляем данные
INSERT INTO companies VALUES ('comp-1', 'TechCorp');
INSERT INTO employees VALUES ('emp-1', 'Alice', 'comp-1');
INSERT INTO employees VALUES ('emp-2', 'Bob', 'comp-1');

-- Удаляем компанию
DELETE FROM companies WHERE id = 'comp-1';

-- Результат: сотрудники остаются в БД, но company_id становится NULL
SELECT * FROM employees;
-- emp-1, Alice, NULL  ← company_id = NULL
-- emp-2, Bob, NULL    ← company_id = NULL

Варианты ON DELETE действий

-- 1. ON DELETE SET NULL — установить NULL (только если столбец nullable)
ALTER TABLE employees
ADD CONSTRAINT fk_on_delete_null
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;

-- 2. ON DELETE CASCADE — удалить все дочерние записи
ALTER TABLE employees
ADD CONSTRAINT fk_on_delete_cascade
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE;

-- 3. ON DELETE RESTRICT — запретить удаление если есть дочерние записи
ALTER TABLE employees
ADD CONSTRAINT fk_on_delete_restrict
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT;

-- 4. ON DELETE SET DEFAULT — установить значение по умолчанию
ALTER TABLE employees
ADD CONSTRAINT fk_on_delete_default
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET DEFAULT;

-- 5. ON DELETE NO ACTION — не делать ничего (Postgres)
ALTER TABLE employees
ADD CONSTRAINT fk_on_delete_no_action
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE NO ACTION;

Сравнение действий

Java + JPA: ON DELETE SET NULL

// Родительская сущность
@Entity
@Table(name = "companies")
public class Company {
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private String id;
    
    private String name;
    
    // Двусторонняя связь
    @OneToMany(mappedBy = "company")
    private List<Employee> employees = new ArrayList<>();
}

// Дочерняя сущность
@Entity
@Table(name = "employees")
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private String id;
    
    private String name;
    
    // foreignKey с ON DELETE SET NULL
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(
        name = "company_id",
        foreignKey = @ForeignKey(name = "fk_employees_company"),
        nullable = true  // Важно: столбец должен быть nullable
    )
    private Company company;  // Может быть null
}

// Сервис
@Service
@Transactional
public class CompanyService {
    
    @Autowired
    private CompanyRepository companyRepository;
    
    public void deleteCompany(String companyId) {
        Company company = companyRepository.findById(companyId)
            .orElseThrow(() -> new NotFoundException("Company not found"));
        
        // При удалении компании:
        // - БД автоматически установит company_id = NULL для всех сотрудников
        // - Сотрудники останутся в БД, но без компании
        companyRepository.delete(company);
    }
}

Примеры сценариев

Сценарий 1: Сотрудники без компании (ON DELETE SET NULL)

-- Таблица: companies (id, name)
-- Таблица: employees (id, name, company_id FK ON DELETE SET NULL)

-- Ситуация 1: Компания закрывается
DELETE FROM companies WHERE id = 'apple';

-- Результат: сотрудники остаются, company_id = NULL
SELECT * FROM employees;
-- Результат:
-- id=emp-1, name=Alice, company_id=NULL
-- id=emp-2, name=Bob, company_id=NULL

Сценарий 2: Комментарии удаляются с постом (ON DELETE CASCADE)

-- Таблица: posts (id, title)
-- Таблица: comments (id, text, post_id FK ON DELETE CASCADE)

-- Удалить пост
DELETE FROM posts WHERE id = 'post-1';

-- Результат: все комментарии к этому посту тоже удаляются
SELECT COUNT(*) FROM comments WHERE post_id = 'post-1';
-- Результат: 0 (все удалены)

Когда использовать ON DELETE SET NULL

// ✅ ХОРОШО: опциональная связь
@Entity
public class Review {
    @Id
    private String id;
    
    private String text;
    
    // Рецензент может быть удалён, но рецензия останется
    @ManyToOne(optional = true)
    @JoinColumn(name = "reviewer_id", nullable = true)
    private User reviewer;  // Может быть NULL
}

// ✅ ХОРОШО: Заказы привязаны к клиентам, но клиент может быть удалён
@Entity
public class Order {
    @ManyToOne
    @JoinColumn(name = "customer_id", nullable = true)
    private Customer customer;  // Может быть NULL если клиент удалён
}

// ❌ ПЛОХО: обязательная связь
@Entity
public class OrderItem {
    @ManyToOne
    @JoinColumn(name = "order_id", nullable = false)  // НЕ должно быть SET NULL
    private Order order;  // Должен быть обязательно
}

Миграция (Goose SQL)

-- migration: 0001_create_tables.sql
CREATE TABLE companies (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE employees (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    company_id UUID,
    CONSTRAINT fk_employees_company 
    FOREIGN KEY (company_id) 
    REFERENCES companies(id) 
    ON DELETE SET NULL
);

-- migration: 0002_add_nullable_column.sql
ALTER TABLE employees
ADD COLUMN optional_company_id UUID;

ALTER TABLE employees
ADD CONSTRAINT fk_optional_company
FOREIGN KEY (optional_company_id) 
REFERENCES companies(id) 
ON DELETE SET NULL;

Важные замечания

1. nullable = true — столбец ДОЛЖЕН допускать NULL
2. Производительность — ON DELETE SET NULL медленнее, чем ON DELETE CASCADE
3. Аудит — сложнее отследить что произошло с данными
4. Целостность данных — сотрудник без компании может быть некорректным состоянием

Заключение

ON DELETE SET NULL — это способ безопасно удалять родительские записи, сохраняя дочерние записи в БД с пустыми ссылками. Это полезно для опциональных связей, где наличие родителя не критично. Выбор между SET NULL, CASCADE и RESTRICT зависит от семантики ваших данных и требований приложения.