Projektowanie ograniczeń klucza obcego (referential)

Spójność referencyjna jest narzucana przez dodanie ograniczeń klucza obcego (lub referencyjnego) do definicji tabeli i kolumny oraz utworzenie indeksu dla wszystkich kolumn klucza obcego. Po zdefiniowaniu ograniczeń klucza indeksu i klucza obcego zmiany w danych w tabelach i kolumnach są sprawdzane pod kątem zdefiniowanego ograniczenia. Zakończenie żądanego działania zależy od wyniku sprawdzenia ograniczenia.

O tym zadaniu

Ograniczenia referencyjne są ustanawiane z klauzulą FOREIGN KEY i klauzulą REFERENCES w instrukcjach CREATE TABLE lub ALTER TABLE. Istnieją efekty z ograniczenia referencyjnego dla tabeli o typie strukturalnym lub tabeli nadrzędnej, która jest tabelą o typie strukturalnym, którą należy wziąć pod uwagę przed utworzeniem ograniczenia referencyjnego.

Identyfikacja kluczy obcych wymusza ograniczenia wartości w wierszach tabeli lub między wierszami dwóch tabel. Menedżer bazy danych sprawdza ograniczenia określone w definicji tabeli i zachowuje odpowiednio relacje. Celem jest zachowanie integralności za każdym razem, gdy jeden obiekt bazy danych odwołuje się do innego obiektu, bez pogorszenia wydajności.

Na przykład klucze podstawowe i klucze obce mają kolumnę numer wydziału. W przypadku tabeli EMPLOYEE nazwa kolumny to WORKDEPT, a dla tabeli DEPARTMENT-nazwa to DEPTNO. Relacja między tymi dwoma tabelami jest zdefiniowana przez następujące ograniczenia:

W tabeli EMPLOYEE znajduje się tylko jeden numer działu dla każdego pracownika, a ten numer istnieje w tabeli DEPARTMENT.
Każdy wiersz w tabeli EMPLOYEE jest powiązany z nie więcej niż jednym wierszem w tabeli DEPARTMENT. Istnieje unikalna relacja między tabelami.
Każdy wiersz w tabeli EMPLOYEE, który ma wartość inną niż NULL dla WORKDEPT, jest powiązany z wierszem w kolumnie DEPTNO tabeli DEPARTMENT.
Tabela DEPARTMENT jest tabelą nadrzędną, a tabela EMPLOYEE jest tabelą zależną.

Instrukcja definiująca tabelę nadrzędną, DEPARTMENT, jest następująca:

CREATE TABLE DEPARTMENT
       (DEPTNO    CHAR(3)     NOT NULL,
        DEPTNAME  VARCHAR(29) NOT NULL,
        MGRNO     CHAR(6),
        ADMRDEPT  CHAR(3)     NOT NULL,
        LOCATION  CHAR(16),
           PRIMARY KEY (DEPTNO))
    IN RESOURCE

Instrukcja definiująca tabelę zależną, EMPLOYEE, to:

CREATE TABLE EMPLOYEE
       (EMPNO     CHAR(6)     NOT NULL PRIMARY KEY,
        FIRSTNME  VARCHAR(12) NOT NULL,
        LASTNAME  VARCHAR(15) NOT NULL,
        WORKDEPT  CHAR(3),
        PHONENO   CHAR(4),
        PHOTO     BLOB(10m)   NOT NULL,
           FOREIGN KEY DEPT (WORKDEPT)
           REFERENCES DEPARTMENT ON DELETE NO ACTION)
    IN RESOURCE

Określając kolumnę DEPTNO jako klucz podstawowy tabeli DEPARTMENT i WORKDEPT jako klucz obcy tabeli EMPLOYEE, definiujesz ograniczenie referencyjne dla wartości WORKDEPT. To ograniczenie wymusza spójność referencyjną między wartościami obu tabel. W takim przypadku wszyscy pracownicy, którzy są dodawani do tabeli EMPLOYEE, muszą mieć numer działu, który można znaleźć w tabeli DEPARTMENT.

Reguła usuwania dla ograniczenia referencyjnego w tabeli pracowników to NO ACTION, co oznacza, że nie można usunąć działu z tabeli DEPARTMENT, jeśli w tym dziale znajdują się pracownicy.

Chociaż w poprzednich przykładach użyto instrukcji CREATE TABLE w celu dodania ograniczenia referencyjnego, można również użyć instrukcji ALTER TABLE.

Inny przykład: Te same definicje tabel są używane jako te w poprzednim przykładzie. Ponadto tabela DEPARTMENT jest tworzona przed tabelą EMPLOYEE. Każdy wydział ma menedżera, a menedżer ten jest wymieniony w tabeli EMPLOYEE. Tabela MGRNO tabeli DEPARTMENT jest w rzeczywistości kluczem obcym tabeli EMPLOYEE. Ze względu na ten cykl referencjalny ograniczenie to stanowi nieznaczny problem. Później można dodać klucz obcy. Można również użyć instrukcji CREATE SCHEMA w celu utworzenia jednocześnie tabel EMPLOYEE i DEPARTMENT.

Patrz także: Klucze obce w ograniczeniach referencyjnych.