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.
- 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ą.
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
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.