Sprawdzanie integralności danych

Dzisiaj zaczniemy ogarniać, jak foreign keys pomagają nam pilnować integralności danych i zapobiegać typowym problemom związanym z niespójnymi albo błędnymi danymi.

Najpierw ogarniemy, co dokładnie mamy na myśli mówiąc o "integralności danych". Wyobraź sobie, że masz tabelę z zamówieniami (orders) i tabelę z klientami (customers). Jeśli zamówienie ma klienta, którego nie ma w tabeli klientów, to jest naruszenie integralności. Ważne, żeby wszystkie dane w powiązanych tabelach były logicznie spójne.

Integralność danych oznacza:

• Zero "pustych" referencji: jeśli odnosimy się do czegoś w innej tabeli, to to "coś" zawsze istnieje.
• Odporność na błędy modyfikacji: jeśli usuwamy z tabeli wartość, do której odnoszą się inne rekordy, baza danych powinna nas ostrzec albo poprawnie obsłużyć tę sytuację.

Właśnie do tego w PostgreSQL używamy foreign keys.

Jak foreign keys zapewniają integralność danych?

Kiedy w tabeli tworzysz foreign key, PostgreSQL automatycznie sprawdza:

1. Obecność danych w tabeli nadrzędnej. Zanim wstawisz albo zaktualizujesz rekord, PostgreSQL sprawdza, czy wskazany foreign key istnieje w powiązanej tabeli.
2. Usuwanie lub zmiana danych. Przed usunięciem albo aktualizacją rekordu w tabeli nadrzędnej, PostgreSQL sprawdza, czy nie odnoszą się do niego rekordy w tabeli podrzędnej.

Foreign keys to taki "strażnik". Nie przepuszczą niepoprawnych danych i gwarantują, że tabele będą współpracować według ustalonych zasad.

Przykład: integralność danych w tabelach studentów i kursów

Załóżmy, że mamy dwie tabele — students i courses. Każdy student może zapisać się na kilka kursów. Żeby to ogarnąć, używamy tabeli enrollments. Zobaczmy sytuację, gdy ktoś próbuje zapisać studenta na nieistniejący kurs.

Krok 1. Tworzymy trzy powiązane tabele:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id)
);

Tu:

• W tabeli enrollments jawnie podaliśmy foreign keys student_id i course_id, które odnoszą się do primary keys w tabelach students i courses.

Typowe sprawdzania integralności danych

1. Sprawdzanie przy wstawianiu danych

Jeśli spróbujemy wstawić do tabeli enrollments rekord z nieistniejącym student_id albo course_id, pojawi się błąd.

Przykład:

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- Błąd! Student o ID 999 nie istnieje.

Komunikat błędu:

ERROR:  insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"
DETAIL:  Key (student_id)=(999) is not present in table "students".

2. Sprawdzanie przy usuwaniu danych

Spróbujmy usunąć rekord z tabeli nadrzędnej, do którego jest referencja.

Przykład:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO courses (title) VALUES ('Mathematics');

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1); -- Udane wstawienie

DELETE FROM students WHERE student_id = 1; -- Błąd, bo student nadal jest zapisany na kurs!

Komunikat błędu:

ERROR:  update or delete on table "students" violates foreign key constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

Żeby poprawnie usuwać rekordy w takich sytuacjach, korzystamy ze strategii CASCADE, SET NULL albo RESTRICT, o których już wcześniej gadaliśmy.

Przykłady użycia foreign keys do sprawdzania integralności

Przykład 1: Automatyczna ochrona przed niepoprawnymi danymi

Dzięki foreign keys PostgreSQL automatycznie blokuje wstawianie "nieistniejących" danych:

-- Spróbujmy dodać nieistniejących studentów do kursu:
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- Błąd! Student o ID 42 nie istnieje.

To gwarantuje, że student nie zapisze się na kurs, jeśli nie istnieje w tabeli students.

Przykład 2: Usuwanie danych z ON DELETE CASCADE

Jeśli foreign key jest ustawiony na kaskadowe usuwanie ON DELETE CASCADE, to przy usunięciu rekordu z tabeli nadrzędnej powiązane dane w tabeli podrzędnej też zostaną usunięte.

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- Usuwamy stary foreign key

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON DELETE CASCADE;

DELETE FROM students WHERE student_id = 1; -- Teraz usuną się też rekordy z tabeli enrollments

Przykład 3: Obsługa zmian z ON UPDATE

Jeśli foreign key jest ustawiony z ON UPDATE CASCADE, to przy zmianie wartości w tabeli nadrzędnej PostgreSQL automatycznie zaktualizuje dane w tabeli podrzędnej.

-- Ustawiamy foreign key tak, żeby zmiany klucza nadrzędnego automatycznie pojawiły się w tabeli podrzędnej:
ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey;

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON UPDATE CASCADE;

-- Zmieniamy identyfikator studenta:
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- Teraz w tabeli enrollments student_id też zmieni się na 10.

Testowanie integralności danych

Zawsze warto przetestować, jak ustawienia foreign key zachowują się w różnych scenariuszach:

1. Spróbuj wstawić dane z niepoprawnym student_id albo course_id.
2. Usuń dane z students i sprawdź, jak zachowuje się tabela enrollments.
3. Zmień dane w tabeli students i upewnij się, że powiązane rekordy są zaktualizowane.

Specyfika pracy z foreign keys

Czasem pojawiają się sytuacje, które mogą trochę zamieszać:

• Brak indeksu. Jeśli tabela nadrzędna (students, na przykład) nie ma zindeksowanej kolumny, do której jest referencja, PostgreSQL może działać wolniej. Dlatego ważne, żeby primary key w tabeli nadrzędnej zawsze był indeksem.
• Cykliczne referencje. Jeśli dwie tabele odnoszą się do siebie nawzajem, może to powodować trudności przy wstawianiu danych. W takich przypadkach trzeba lepiej przemyśleć projekt.
• Usuwanie wszystkich rekordów. Jeśli trzeba usunąć wszystkie rekordy z kaskadowym usuwaniem, trzeba uważać na dane w tabeli podrzędnej, żeby nie było niespodzianek.

Żeby uniknąć tych problemów, ważne jest dobre projektowanie tabel i testowanie zasad powiązań zanim wrzucisz je do prawdziwej bazy danych.