Usuwanie i modyfikacja danych z uwzględnieniem kluczy obcych

No dobra, już wiemy, czym są klucze obce (FOREIGN KEY), jak działają i nawet ćwiczyliśmy tworzenie tabel z ich użyciem. Ale co, jeśli przyjdzie czas na usunięcie danych albo zmianę powiązanych rekordów? Zaraz ogarniemy, jak usuwanie i zmiana danych działa razem z kluczami obcymi i jakie są tu triki.

Wyobraź sobie, że baza danych to taki skomplikowany domek z kart. Jeśli wyciągniesz jedną kartę, możesz rozwalić całą konstrukcję. I tu właśnie wchodzą klucze obce, które zapobiegają "zniszczeniu" naszej bazy przy usuwaniu powiązanych danych. Zobaczmy, jak to działa.

Co się stanie, jeśli spróbujesz usunąć dane?

Kiedy w tabeli jest klucz obcy, DBMS sprawdza, czy rekord jest powiązany z innymi tabelami. Jeśli jest, próba usunięcia rekordu może wywołać błąd integralności danych. Żeby nie było niespodzianek, możesz z góry ustawić akcje dla klucza obcego. Te akcje ustawia się przez opcje ON DELETE.

Konfiguracja zachowania z ON DELETE

Możesz ustawić jedną z poniższych zasad dla klucza obcego podczas jego tworzenia:

1. ON DELETE CASCADE: Usunięcie rekordu w tabeli nadrzędnej automatycznie powoduje usunięcie wszystkich powiązanych rekordów w tabeli podrzędnej.
2. ON DELETE SET NULL: Zamiast usuwać powiązane rekordy, ich klucze obce są ustawiane na NULL.
3. ON DELETE SET DEFAULT: Wartość klucza obcego jest ustawiana na domyślną.
4. ON DELETE RESTRICT (domyślne zachowanie): Usunięcie rekordu jest niemożliwe, jeśli są powiązane rekordy, i pojawi się błąd.
5. ON DELETE NO ACTION: Prawie jak RESTRICT, ale sprawdzenie integralności jest odłożone do końca transakcji.

Przykład: Usuwanie kaskadowe ON DELETE CASCADE

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

-- Tabela zamówień
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

-- Wstawianie danych
INSERT INTO customers (name) VALUES ('Ivan Ivanov');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- Usuwanie klienta
DELETE FROM customers WHERE customer_id = 1;

-- Sprawdzamy, co się stało z tabelą orders
SELECT * FROM orders; -- Brak rekordów, zostały usunięte kaskadowo!

Kiedy usuwamy rekord z tabeli customers, PostgreSQL automatycznie usuwa wszystkie zamówienia tego klienta z tabeli orders.

Przykład: Ustawianie odnośników na NULL ON DELETE SET NULL

-- Tabela zamówień z nową zasadą zachowania
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- Wstawianie danych
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

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

-- Sprawdzamy tabelę orders_with_null
SELECT * FROM orders_with_null;

Wynik:

Dzięki ON DELETE SET NULL możemy zachować zamówienia, ale "odpiąć" je od już nieistniejącego klienta.

Modyfikacja danych z uwzględnieniem kluczy obcych

Oprócz usuwania, zmiany danych w tabelach nadrzędnych też mogą wpływać na powiązane rekordy. Na przykład, co się stanie, jeśli klient zmieni swój customer_id? I tu wchodzi opcja ON UPDATE.

Konfiguracja zachowania z ON UPDATE

Zmiany w tabeli nadrzędnej możesz obsłużyć za pomocą takich strategii:

1. ON UPDATE CASCADE: zmiana wartości klucza obcego w tabeli nadrzędnej automatycznie aktualizuje go we wszystkich powiązanych rekordach.
2. ON UPDATE SET NULL: wartość klucza obcego w tabelach podrzędnych jest ustawiana na NULL.
3. ON UPDATE SET DEFAULT: ustawiana jest wartość domyślna.
4. ON UPDATE RESTRICT: zmiana wartości klucza obcego jest zabroniona, jeśli są powiązane rekordy.
5. ON UPDATE NO ACTION: sprawdzenie jest odłożone do końca transakcji.

Przykład: Kaskadowa aktualizacja ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- Wstawianie danych
INSERT INTO customers_with_cascade (name) VALUES ('Ivan Ivanov');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- Zmiana customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- Sprawdzamy tabelę orders_with_cascade
SELECT * FROM orders_with_cascade;

Wynik:

Przy zmianie customer_id PostgreSQL automatycznie aktualizuje go w tabeli orders_with_cascade.

Ćwiczenie praktyczne: Tabela enrollments

Przypomnijmy sobie nasz przykład ze studentami students i kursami courses. Będziemy pracować z tabelą zapisów na kursy enrollments i ustawiać zarządzanie usuwaniem danych.

-- Tabela studentów
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Tabela kursów
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- Tabela pośrednia zapisów
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- Wstawianie danych
INSERT INTO students (name) VALUES ('Aleksey Petrov');
INSERT INTO courses (title) VALUES ('Matematyka');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- Usuwamy studenta
DELETE FROM students WHERE student_id = 1;

-- Sprawdzamy tabelę enrollments
SELECT * FROM enrollments; -- Pusto! Rekord został automatycznie usunięty.

Typowe błędy i jak ich unikać

Jednym z częstych błędów jest próba usunięcia rekordu z tabeli nadrzędnej bez poprawnej konfiguracji zachowania kluczy obcych. Na przykład, jeśli nie ustawisz nic dla ON DELETE, domyślnie będzie RESTRICT, co skończy się błędem.

Ważne też, żeby pamiętać, że zbyt częste używanie operacji kaskadowych (CASCADE) może prowadzić do nieoczekiwanych skutków. Możesz przypadkiem usunąć więcej danych, niż planowałeś.

Żeby uniknąć takich problemów, stosuj się do tych zasad:

• Zawsze dobrze przemyśl zachowanie ON DELETE i ON UPDATE, zgodnie z logiką aplikacji.
• Przy ważnych operacjach dodaj zapytania sprawdzające przed wykonaniem zmian lub usunięć.
• Używaj transakcji, żeby móc cofnąć zmiany w razie błędu.