Użycie ON DELETE CASCADE i ON UPDATE RESTRICT

Wyobraź sobie, że stworzyłeś bazę danych, gdzie masz tabelę klientów (customers) i powiązane z nimi zamówienia (orders). Ale w pewnym momencie pojawia się pytanie: co zrobić, jeśli klient zostanie usunięty z tabeli customers? Czy zamówienia tego klienta też powinny zostać usunięte, czy mają zostać "sierotami", wskazując na nieistniejącego klienta? A co, jeśli zdecydujesz się zmienić ID klienta? Właśnie tutaj wchodzą do gry operacje kaskadowe (CASCADE) i ograniczenia (RESTRICT) do zarządzania zachowaniem bazy danych.

ON DELETE CASCADE — to mechanizm, który automatycznie usuwa powiązane rekordy przy usuwaniu rekordu z tabeli nadrzędnej. Innymi słowy, jeśli usuniesz klienta, to wszystkie jego zamówienia też znikną.

To działa tak: kiedy dodajesz ON DELETE CASCADE do definicji klucza obcego, baza danych "wie", że powiązany rekord ma być automatycznie usunięty.

Przykład

Załóżmy, że mamy dwie tabele: customers i orders. Klienci customers mogą mieć kilka zamówień orders, co odpowiada relacji ONE-TO-MANY. Chcemy, żeby przy usunięciu klienta wszystkie jego zamówienia też zostały usunięte.

-- Tworzymy tabelę klientów
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Tworzymy tabelę zamówień z kluczem obcym wskazującym na tabelę klientów
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

Wstawiamy dane do tabel

-- Wstawiamy dane do tabeli klientów
INSERT INTO customers (name) VALUES ('Ivan'), ('Anna');

-- Wstawiamy dane do tabeli zamówień
INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-10-01'),
(1, '2023-10-02'),
(2, '2023-10-03');

Tabela orders:

Usuwamy klienta i sprawdzamy, co się stanie

-- Usuwamy klienta z ID 1
DELETE FROM customers WHERE customer_id = 1;

-- Sprawdzamy, co zostało w tabeli zamówień
SELECT * FROM orders;

Jak widzisz, zamówienia powiązane z usuniętym klientem też zostały usunięte.

Ograniczenie zmian: ON UPDATE RESTRICT

ON UPDATE RESTRICT pozwala zablokować zmianę wartości w tabeli nadrzędnej, jeśli rekord w tabeli podrzędnej wskazuje na tę wartość. To taki "ochronny mur", który zapobiega zmianom mogącym rozwalić spójność danych.

Jak to działa? Gdy dodasz ON UPDATE RESTRICT, baza danych nie pozwoli zaktualizować klucza w tabeli nadrzędnej, jeśli są do niego odwołania w tabeli podrzędnej.

Przykład

Bierzemy te same tabele customers i orders, ale do klucza obcego dorzucamy ograniczenie na zmianę.

-- Tworzymy ponownie tabelę zamówień z ograniczeniem na update
DROP TABLE orders;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON UPDATE RESTRICT,
    order_date DATE NOT NULL
);

Spróbujmy zaktualizować ID klienta

-- Próba zmiany ID klienta z 2 na 5
UPDATE customers 
SET customer_id = 5 
WHERE customer_id = 2;

Wynik:

ERROR:  update or delete on table "customers" violates foreign key constraint
DETAIL:  Key (customer_id)=(2) is still referenced from table "orders".

Jak widzisz, baza danych wywaliła błąd, bo zmiana klucza rozwaliłaby powiązanie między tabelami.

Więcej o UPDATE i jego niuansach opowiem Ci na kolejnym poziomie :P

Łączenie ON DELETE CASCADE i ON UPDATE RESTRICT

Oczywiście operacje kaskadowe (CASCADE) i ograniczenia (RESTRICT) można łączyć. Na przykład możesz ustawić automatyczne usuwanie powiązanych danych przy usunięciu rekordu nadrzędnego (ON DELETE CASCADE), ale jednocześnie zablokować zmianę jego ID (ON UPDATE RESTRICT), żeby uniknąć niechcianych efektów.

Przykład

Tworzymy jeszcze raz tabelę zamówień, używając obu mechanizmów:

DROP TABLE orders;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT,
    order_date DATE NOT NULL
);

Teraz:

• Jeśli usuniesz klienta, wszystkie jego zamówienia zostaną usunięte.
• Jeśli spróbujesz zmienić ID klienta, dostaniesz błąd.

Dlaczego to ważne w prawdziwych projektach?

Używanie CASCADE i RESTRICT jest mega ważne w dużych systemach z wieloma powiązanymi tabelami. Na przykład:

W sklepie internetowym klient może mieć zamówienia. Jeśli klient zdecyduje się usunąć swój profil, nie chcesz zostawiać w bazie zamówień, które już do nikogo nie należą. Tu przydaje się ON DELETE CASCADE.

Jednocześnie możesz chcieć zablokować przypadkowe zmiany unikalnych kluczy, żeby nie rozwalić powiązań między tabelami. Do tego służy ON UPDATE RESTRICT.