Klucz obcy - Foreign key
Klucz obcy jest zbiorem atrybutów w tabeli, która odnosi się do klucza podstawowego w innej tabeli. Klucz obcy łączy te dwie tabele. Innym sposobem, aby umieścić go: W kontekście relacyjnych baz danych , wykorzystując klucz obcy jest zbiorem atrybutów zastrzeżeniem pewnego rodzaju ograniczenia uzależnienia od włączenia, a konkretnie ograniczenie, że krotki składające się z klucza obcego atrybutów w jednej relacji , R, moszczu istnieją również w jakiejś innej (niekoniecznie odrębnej) relacji, S, a ponadto te atrybuty muszą również być kluczem kandydującym w S. Mówiąc prościej, klucz obcy to zestaw atrybutów, które odwołują się do klucza kandydującego. Na przykład tabela o nazwie TEAM może mieć atrybut MEMBER_NAME, który jest kluczem obcym odwołującym się do klucza kandydującego PERSON_NAME w tabeli PERSON. Ponieważ MEMBER_NAME jest kluczem obcym, każda wartość występująca jako imię członka zespołu TEAM musi również istnieć jako imię i nazwisko osoby w tabeli PERSON; innymi słowy, każdy członek ZESPOŁU jest również OSOBĄ.
Streszczenie
Tabela zawierająca klucz obcy nazywana jest tabelą podrzędną, a tabela zawierająca klucz kandydujący nazywana jest tabelą odniesienia lub tabelą nadrzędną. W modelowaniu i implementacji relacyjnej bazy danych klucz kandydujący to zestaw zero lub więcej atrybutów, których wartości są gwarantowane jako unikatowe dla każdej krotki (wiersza) w relacji. Wartość lub kombinacja wartości kandydujących atrybutów klucza dla żadnej krotki nie może być duplikowana dla żadnej innej krotki w tej relacji.
Ponieważ celem klucza obcego jest identyfikacja określonego wiersza tabeli, do której się odwołuje, ogólnie wymagane jest, aby klucz obcy był równy kluczowi kandydującemu w pewnym wierszu tabeli podstawowej lub nie miał żadnej wartości ( wartość NULL ). . Ta reguła jest nazywana ograniczeniem integralności referencyjnej między dwiema tabelami. Ponieważ naruszenia tych ograniczeń mogą być źródłem wielu problemów z bazą danych, większość systemów zarządzania bazami danych udostępnia mechanizmy zapewniające, że każdy klucz obcy inny niż null odpowiada wierszowi tabeli, do której istnieje odwołanie.
Rozważmy na przykład bazę danych z dwiema tabelami: tabelą KLIENT, która zawiera wszystkie dane klientów i tabelą ZAMÓWIENIE, która zawiera wszystkie zamówienia klientów. Załóżmy, że firma wymaga, aby każde zamówienie dotyczyło jednego klienta. Aby odzwierciedlić to w bazie danych, do tabeli ORDER dodawana jest kolumna klucza obcego (np. CUSTOMERID), która odwołuje się do klucza podstawowego CUSTOMER (np. ID). Ponieważ klucz podstawowy tabeli musi być unikalny, a CUSTOMERID zawiera tylko wartości z tego pola klucza podstawowego, możemy założyć, że jeśli ma wartość, CUSTOMERID zidentyfikuje konkretnego klienta, który złożył zamówienie. Jednak nie można już tego zakładać, jeśli tabela ORDER nie jest aktualizowana po usunięciu wierszy tabeli CUSTOMER lub zmianie kolumny ID, a praca z tymi tabelami może stać się trudniejsza. Wiele rzeczywistych baz danych pozwala obejść ten problem poprzez „dezaktywację” zamiast fizycznego usuwania kluczy obcych z tabeli głównej lub przez złożone programy aktualizacyjne, które modyfikują wszystkie odniesienia do klucza obcego, gdy zachodzi potrzeba zmiany.
Klucze obce odgrywają zasadniczą rolę w projektowaniu bazy danych . Ważną częścią projektu bazy danych jest upewnienie się, że relacje między jednostkami świata rzeczywistego są odzwierciedlane w bazie danych za pomocą odwołań, przy użyciu kluczy obcych do odwoływania się z jednej tabeli do drugiej. Inną ważną częścią projektowania bazy danych jest normalizacja bazy danych , w której tabele są rozbijane, a klucze obce umożliwiają ich rekonstrukcję.
Wiele wierszy w tabeli odniesienia (lub tabeli podrzędnej) może odnosić się do tego samego wiersza w tabeli odniesienia (lub tabeli nadrzędnej). W takim przypadku relacja między dwiema tabelami jest nazywana relacją jeden do wielu między tabelą odniesienia a tabelą odniesienia.
Ponadto tabela potomna i tabela nadrzędna mogą w rzeczywistości być tą samą tabelą, tzn. klucz obcy odsyła do tej samej tabeli. Taki klucz obcy jest znany w SQL:2003 jako samoodwołujący się lub rekurencyjny klucz obcy. W systemach zarządzania bazami danych często osiąga się to poprzez powiązanie pierwszego i drugiego odniesienia z tą samą tabelą.
Tabela może mieć wiele kluczy obcych, a każdy klucz obcy może mieć inną tabelę nadrzędną. Każdy klucz obcy jest wymuszany niezależnie przez system bazy danych . Dlatego kaskadowe relacje między tabelami można ustanowić przy użyciu kluczy obcych.
Klucz obcy jest definiowany jako atrybut lub zestaw atrybutów w relacji, której wartości odpowiadają kluczowi podstawowemu w innej relacji. Składnia dodawania takiego ograniczenia do istniejącej tabeli jest zdefiniowana w SQL:2003, jak pokazano poniżej. Pominięcie listy kolumn w REFERENCESklauzuli oznacza, że klucz obcy będzie odwoływał się do klucza podstawowego tabeli, do której się odwołuje. Podobnie klucze obce mogą być zdefiniowane jako część instrukcji CREATE TABLESQL.
CREATE TABLE child_table (
col1 INTEGER PRIMARY KEY,
col2 CHARACTER VARYING(20),
col3 INTEGER,
col4 INTEGER,
FOREIGN KEY(col3, col4) REFERENCES parent_table(col1, col2) ON DELETE CASCADE
)
Jeśli klucz obcy jest tylko pojedynczą kolumną, kolumnę można oznaczyć jako taką przy użyciu następującej składni:
CREATE TABLE child_table (
col1 INTEGER PRIMARY KEY,
col2 CHARACTER VARYING(20),
col3 INTEGER,
col4 INTEGER REFERENCES parent_table(col1) ON DELETE CASCADE
)
Klucze obce można zdefiniować za pomocą instrukcji procedury składowanej .
sp_foreignkey child_table, parent_table, col3, col4
- child_table : nazwa tabeli lub widoku, który zawiera klucz obcy do zdefiniowania.
- parent_table : nazwa tabeli lub widoku, który ma klucz podstawowy, do którego ma zastosowanie klucz obcy. Klucz podstawowy musi być już zdefiniowany.
- col3 i col4 : nazwy kolumn tworzących klucz obcy. Klucz obcy musi mieć co najmniej jedną kolumnę i maksymalnie osiem kolumn.
Działania referencyjne
Ponieważ system zarządzania bazą danych wymusza ograniczenia referencyjne, musi zapewniać integralność danych, jeśli wiersze w tabeli, do której się odwołuje, mają zostać usunięte (lub zaktualizowane). Jeśli wiersze zależne w tabelach odwołań nadal istnieją, należy wziąć pod uwagę te odwołania. SQL:2003 określa 5 różnych działań referencyjnych, które będą miały miejsce w takich przypadkach:
KASKADA
Za każdym razem, gdy wiersze w tabeli nadrzędnej (odwołującej się) zostaną usunięte (lub zaktualizowane), odpowiednie wiersze tabeli podrzędnej (odwołującej się) z pasującą kolumną klucza obcego również zostaną usunięte (lub zaktualizowane). Nazywa się to usuwaniem kaskadowym (lub aktualizacją).
OGRANICZAĆ
Nie można zaktualizować ani usunąć wartości, jeśli w tabeli odwołującej się lub tabeli podrzędnej istnieje wiersz, który odwołuje się do wartości w tabeli, do której istnieje odwołanie.
Podobnie nie można usunąć wiersza, o ile istnieje odwołanie do niego z tabeli odniesienia lub tabeli podrzędnej.
Aby lepiej zrozumieć RESTRICT (i CASCADE), pomocne może być zauważenie następującej różnicy, która może nie być od razu oczywista. Akcja referencyjna KASKADA modyfikuje „zachowanie” samej tabeli (podrzędnej), w której używane jest słowo KASKADA. Na przykład ON DELETE CASCADE skutecznie mówi „Gdy wiersz, do którego istnieje odwołanie, zostanie usunięty z innej tabeli (tabeli głównej), usuń również ze mnie ”. Jednak akcja referencyjna RESTRICT modyfikuje „zachowanie” tabeli głównej, a nie tabeli podrzędnej, chociaż słowo RESTRICT pojawia się w tabeli podrzędnej, a nie w tabeli głównej! Tak więc ON DELETE RESTRICT skutecznie mówi: „Kiedy ktoś próbuje usunąć wiersz z innej tabeli (tabeli głównej), zapobiegaj usuwaniu z tej innej tabeli (i oczywiście nie usuwaj ode mnie, ale nie o to chodzi tutaj)."
RESTRICT nie jest obsługiwany przez Microsoft SQL 2012 i wcześniejsze.
BEZ AKCJI
ŻADNE DZIAŁANIE i OGRANICZENIE są bardzo podobne. Główna różnica między NO ACTION a RESTRICT polega na tym, że w przypadku NO ACTION sprawdzanie integralności referencyjnej jest wykonywane po próbie zmiany tabeli. RESTRICT wykonuje sprawdzenie przed próbą wykonania instrukcji UPDATE lub DELETE . Obie akcje referencyjne działają tak samo, jeśli sprawdzenie integralności referencyjnej nie powiedzie się: instrukcja UPDATE lub DELETE spowoduje błąd.
Innymi słowy, gdy instrukcja UPDATE lub DELETE jest wykonywana na tabeli, do której się odwołuje, przy użyciu akcji referencyjnej NO ACTION, SZBD sprawdza na końcu wykonywania instrukcji, czy żadna z relacji referencyjnych nie została naruszona. Różni się to od RESTRICT, który zakłada na początku, że operacja naruszy ograniczenie. Używając opcji NO ACTION, wyzwalacze lub semantyka samej instrukcji mogą dawać stan końcowy, w którym żadne relacje klucza obcego nie zostaną naruszone do czasu ostatecznego sprawdzenia ograniczenia, co umożliwi pomyślne zakończenie instrukcji.
USTAW NULL, USTAW DOMYŚLNE
Ogólnie rzecz biorąc, akcja podejmowana przez DBMS dla SET NULL lub SET DEFAULT jest taka sama dla obu opcji ON DELETE lub ON UPDATE: wartość odnośnych atrybutów odniesienia jest zmieniana na NULL dla SET NULL i na określoną wartość domyślną dla SET DEFAULT .
Wyzwalacze
Akcje referencyjne są zazwyczaj implementowane jako niejawne wyzwalacze (tj. wyzwalacze z nazwami generowanymi przez system, często ukryte). uważany za; w niektórych przypadkach może być konieczne zastąpienie akcji referencyjnej jej równoważnym wyzwalaczem zdefiniowanym przez użytkownika, aby zapewnić właściwą kolejność wykonywania lub obejście ograniczeń tabeli mutacji.
Inne ważne ograniczenie pojawia się w przypadku izolacji transakcji : zmiany wprowadzone w wierszu mogą nie być w pełni kaskadowe, ponieważ do wiersza odwołują się dane, których transakcja nie "widzi", a zatem nie może być kaskadowa. Przykład: podczas gdy twoja transakcja próbuje zmienić numer konta klienta, jednoczesna transakcja próbuje utworzyć nową fakturę dla tego samego klienta; podczas gdy reguła KASKADA może naprawić wszystkie wiersze faktur, które Twoja transakcja może zobaczyć, aby zachować ich spójność z wierszem klienta o zmienionej numeracji, nie będzie sięgać do innej transakcji, aby naprawić tam dane; ponieważ baza danych nie może zagwarantować spójnych danych, gdy dwie transakcje zostaną zatwierdzone, jedna z nich zostanie zmuszona do wycofania (często na zasadzie „kto pierwszy, ten lepszy”).
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
Przykład
Jako pierwszy przykład ilustrujący klucze obce, załóżmy, że baza danych kont zawiera tabelę z fakturami, a każda faktura jest powiązana z określonym dostawcą. Dane dostawcy (takie jak nazwa i adres) są przechowywane w osobnej tabeli; każdy dostawca otrzymuje „numer dostawcy”, aby go zidentyfikować. Każdy rekord faktury ma atrybut zawierający numer dostawcy dla tej faktury. Wtedy „numer dostawcy” jest kluczem podstawowym w tabeli Dostawca. Klucz obcy w tabeli Faktura wskazuje na ten klucz podstawowy. Schemat relacyjny jest następujący. Klucze podstawowe są pogrubione, a klucze obce kursywą.
Supplier (SupplierNumber, Name, Address) Invoice (InvoiceNumber, Text, SupplierNumber)
Odpowiednia instrukcja języka definicji danych jest następująca.
CREATE TABLE Supplier (
SupplierNumber INTEGER NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL,
CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber),
CONSTRAINT number_value CHECK(SupplierNumber > 0)
)
CREATE TABLE Invoice (
InvoiceNumber INTEGER NOT NULL,
Text VARCHAR(4096),
SupplierNumber INTEGER NOT NULL,
CONSTRAINT invoice_pk PRIMARY KEY(InvoiceNumber),
CONSTRAINT inumber_value CHECK (InvoiceNumber > 0),
CONSTRAINT supplier_fk
FOREIGN KEY(SupplierNumber) REFERENCES Supplier(SupplierNumber)
ON UPDATE CASCADE ON DELETE RESTRICT
)