Więzy integralności

Dane przechowywane w bazie danych powinny spełniać wymogi integralności wynikające z założeń przyjętych podczas projektowania bazy.

Definiowanie klucza obcego

Jeżeli w bazie danych ksiegarnia_internetowa do tabeli Ksiazki zostanie wpisana nowa książka z numerem autora 27, a autora o numerze 27 nie ma w bazie, to znaczy, że powstał błąd, który łatwo popełnić przy wprowadzaniu danych. Aby tego uniknąć, należy odpowiednio zdefiniować więzy integralności — wtedy serwer bazodanowy będzie automatycznie sprawdzał poprawność dokonywanych wpisów.

Sprawdzanie spójności w bazie danych odbywa się po jawnym zdefiniowaniu klucza obcego. Dla tabeli Ksiazki możemy zdefiniować klauzulę, która poinformuje bazę, że id_autora w tej tabeli to klucz obcy pochodzący z kolumny id_autora w tabeli Autor. Klauzulę dotyczącą ograniczeń klucza obcego trzeba umieścić za definicją kolumn w poleceniu CREATE TABLE lub ALTER TABLE.

Ogólna postać polecenia wygląda następująco:

          [CONSTRAINT nazwa] FOREIGN KEY (kolumna1, kolumna 2, ...)
          REFERENCE nazwa tabeli (kolumna1, kolumna 2, ...)

gdzie:

• CONSTRAINT nazwa - jest nazwą ograniczenia, może zostać pominięta — wtedy ograniczeniu zostanie nadana nazwa systemowa;
• FOREIGN KEY (kolumna1, kolumna 2, . . . ) - określa kolumny zawierające klucz obcy;
• REFERENCE nazwa_tabeli (kolumna1, kolumna 2, . . . ) - określa, z której tabeli pochodzi klucz obcy i które kolumny są w niej kluczem podstawowym.

Przykład

Podczas tworzenia tabeli Ksiazki należy zdefiniować klauzulę ograniczeń klucza obcego dla kolumny id_autora:

W istniejącej tabeli (Ksiazki) można definiować klauzulę ograniczeń klucza obcego dla nowej kolumny (id_autora) podczas dodawania tej kolumny do tabeli:

W istniejącej tabeli (Ksiazki) dla istniejącego pola klucza obcego (id_autora) należy zdefiniować klauzulę ograniczeń klucza obcego przez zmodyfikowanie kolumny:

Klauzula REFERENCES we wszystkich przypadkach zdefiniuje ograniczenie dla klucza obcego. Nie będzie możliwe wpisanie w kolumnie id_autora tabeli Ksiazki wartości, która nie istnieje w kolumnie id_autora tabeli Autor.

W klauzuli można również zdefiniować nazwę ograniczenia klucza obcego.

Przykład



Klauzula CONSTRAINT Ksiazki_FK nadaje ograniczeniu klucza obcego nazwę Ksiazki_FK.

Przy tak zdefiniowanym ograniczeniu nałożonym na klucz obcy w tabeli Ksiazki zmiana lub usunięcie klucza podstawowego z tabeli Autor powoduje skutki w tabeli Ksiazki wynikające z zasad kaskadowego usuwania i aktualizowania danych.

Kaskadowe usuwanie i aktualizowanie danych

Efekty modyfikacji klucza podstawowego w powiązanych tabelach są odzwierciedlane przez zdefiniowanie kaskadowego usuwania lub aktualizowania danych.

• Aktualizowanie klucza podstawowego wymaga aktualizacji wartości w powiązanym z nim kluczu obcym. W relacyjnej bazie danych klucze podstawowe nie powinny być w ogóle modyfikowane, więc kaskadowe aktualizowanie definiujemy tylko w wyjątkowych przypadkach.
• Usunięcie wiersza w tabeli nadrzędnej lub wartości klucza podstawowego wymaga usunięcia lub zaktualizowania wartości w powiązanym z nim kluczu obcym. Kaskadowe usuwanie może doprowadzić do usunięcia wielu wierszy z różnych tabel, a w konsekwencji do usunięcia istotnych danych, zatem należy je definiować wyłącznie dla tabel pomocniczych opisujących związki „wiele do wielu". Aktualizowanie wartości klucza obcego jest bezpieczniejsze. Jeżeli kolumna klucza obcego zezwala na wpisywanie wartości NULL, należy wartości usuniętego klucza podstawowego zastąpić tą wartością. Jeżeli jest to niemożliwe, trzeba zastąpić wartości NULL specjalnie zdefiniowaną wartością domyślną.

Kaskadowe usuwanie i aktualizowanie danych definiuje się w klauzulach ON UPDATE i ON DELETE z wartościami:

• NO ACTION - dane w powiązanych tabelach nie będą automatycznie modyfikowane. Jest to domyślna wartość.
• CASCADE - modyfikacja ma zostać automatycznie powtórzona we wszystkich powiązanych tabelach.
• SET NULL - zmodyfikowane wartości klucza podstawowego mają zostać zastąpione wartością NULL w powiązanych kolumnach klucza obcego.
• SET DEFAULT - zmodyfikowane wartości klucza podstawowego mają zostać zastąpione w powiązanych kolumnach klucza obcego wartością domyślną.

Przykład

Polecenie DROP CONSTRAINT usuwa wcześniej zdefiniowane ograniczenie. Następnie zostaje zdefiniowane nowe ograniczenie klucza obcego, w którym polecenie ON UPDATE CASCADE określa, że aktualizacja wykonana w tabeli nadrzędnej ma zostać powtórzona w kolumnie klucza obcego tabeli podrzędnej, a polecenie ON DELETE SET NULL oznacza, że gdy będzie usuwany wiersz w tabeli nadrzędnej, w kolumnie klucza obcego tabeli podrzędnej zostanie wstawiona wartość NULL.