- 07/25/2017
- 9 minut na przeczytanie
-
-
s -
M -
M -
c -
J -
+1
-
Odnosi się do: 
SQL Server 2016 (13.x) i nowszych 
Azure SQL Database Azure SQL Managed Instance
Klucze główne i klucze obce to dwa typy ograniczeń, które mogą być używane do wymuszania integralności danych w tabelach SQL Server. Są to ważne obiekty bazy danych.
Ten temat zawiera następujące sekcje.
Klucz główny
Klucz obcy
Zadania powiązane
Klucz główny
Tabela zazwyczaj posiada kolumnę lub kombinację kolumn, które zawierają wartości jednoznacznie identyfikujące każdy wiersz w tabeli. Ta kolumna lub kolumny nazywane są kluczem głównym (PK) tabeli i wymuszają integralność encji tabeli. Ponieważ ograniczenia klucza głównego gwarantują unikalność danych, często definiuje się je na kolumnie tożsamości.
Gdy określisz ograniczenie klucza głównego dla tabeli, silnik bazy danych wymusza unikalność danych poprzez automatyczne utworzenie unikalnego indeksu dla kolumn klucza głównego. Indeks ten pozwala również na szybki dostęp do danych, gdy klucz główny jest używany w zapytaniach. Jeśli ograniczenie klucza głównego jest zdefiniowane na więcej niż jednej kolumnie, wartości mogą być powielane w ramach jednej kolumny, ale każda kombinacja wartości wszystkich kolumn w definicji ograniczenia klucza głównego musi być unikalna.
Jak widać na poniższej ilustracji, kolumny ProductID i VendorID w tabeli Purchasing.ProductVendor tworzą złożone ograniczenie klucza głównego dla tej tabeli. Gwarantuje to, że każdy wiersz w tabeli ProductVendor posiada unikalną kombinację ProductID i VendorID. Zapobiega to wstawianiu duplikatów wierszy.
-
Tabela może zawierać tylko jedno ograniczenie klucza głównego.
-
Klucz główny nie może przekraczać 16 kolumn i całkowitej długości klucza 900 bajtów.
-
Indeks wygenerowany przez ograniczenie klucza głównego nie może spowodować, że liczba indeksów na tabeli przekroczy 999 indeksów nieklastrowych i 1 indeks klastrowy.
-
Jeśli nie określono klastrowany lub nieklastrowany dla ograniczenia klucza głównego, klastrowany jest używany, jeśli nie ma indeksu klastrowego na tabeli.
-
Wszystkie kolumny zdefiniowane w ograniczeniu klucza głównego muszą być zdefiniowane jako nie puste. Jeśli nullability nie jest określone, wszystkie kolumny biorące udział w ograniczeniu klucza głównego mają ustawioną nullability na not null.
-
Jeśli klucz główny jest zdefiniowany na kolumnie typu zdefiniowanego przez użytkownika CLR, implementacja typu musi wspierać porządkowanie binarne.
Określenia klucza obcego
Klucz obcy (FK) jest kolumną lub kombinacją kolumn, która jest używana do ustanowienia i egzekwowania powiązania między danymi w dwóch tabelach w celu kontroli danych, które mogą być przechowywane w tabeli klucza obcego. W odwołaniu do klucza obcego, powiązanie jest tworzone między dwoma tabelami, gdy kolumna lub kolumny, które przechowują wartość klucza głównego dla jednej tabeli, są przywoływane przez kolumnę lub kolumny w innej tabeli. Ta kolumna staje się kluczem obcym w drugiej tabeli.
Na przykład tabela Sales.SalesOrderHeader ma powiązanie klucza obcego z tabelą Sales.SalesPerson, ponieważ istnieje logiczna relacja między zamówieniami sprzedaży a sprzedawcami. Kolumna SalesPersonID w tabeli SalesOrderHeader pasuje do kolumny klucza podstawowego tabeli SalesPerson. Kolumna SalesPersonID w tabeli SalesOrderHeader jest kluczem obcym do tabeli SalesPerson. Dzięki utworzeniu tej relacji klucza obcego nie można wstawić wartości SalesPersonID do tabeli SalesOrderHeader, jeśli nie istnieje ona jeszcze w tabeli SalesPerson.
Tabela może odwoływać się do maksymalnie 253 innych tabel i kolumn jako kluczy obcych (referencje wychodzące). SQL Server 2016 (13.x) zwiększa limit liczby innych tabel i kolumn, które mogą odwoływać się do kolumn w jednej tabeli (referencje przychodzące), z 253 do 10 000. (Wymaga co najmniej 130 poziomu zgodności.) Zwiększenie to ma następujące ograniczenia:
-
Większe niż 253 referencje klucza obcego są obsługiwane tylko dla operacji DELETE DML. Operacje UPDATE i MERGE nie są obsługiwane.
-
Tabela z odniesieniem do własnego klucza obcego jest nadal ograniczona do 253 odniesień do klucza obcego.
-
Większe niż 253 odniesienia do klucza obcego nie są obecnie dostępne dla indeksów typu columnstore, tabel zoptymalizowanych pod kątem pamięci, Stretch Database lub tabel z partycjonowanym kluczem obcym.
Indeksy na ograniczeniach klucza obcego
W przeciwieństwie do ograniczeń klucza głównego, utworzenie ograniczenia klucza obcego nie tworzy automatycznie odpowiedniego indeksu. Jednak ręczne tworzenie indeksu na kluczu obcym jest często przydatne z następujących powodów:
-
Kolumny klucza obcego są często używane w kryteriach złączenia, gdy dane z powiązanych tabel są łączone w zapytaniach poprzez dopasowanie kolumny lub kolumn w ograniczeniu klucza obcego jednej tabeli do kolumny lub kolumn klucza głównego lub unikalnego w innej tabeli. Indeks pozwala na szybkie odnalezienie powiązanych danych w tabeli klucza obcego. Tworzenie indeksu nie jest jednak wymagane. Dane z dwóch powiązanych tabel mogą być łączone nawet, jeśli nie zdefiniowano ograniczeń klucza głównego lub obcego między tabelami, ale relacja klucza obcego między dwiema tabelami wskazuje, że dwie tabele zostały zoptymalizowane do łączenia w zapytaniu, które używa kluczy jako kryteriów.
-
Zmiany w ograniczeniach klucza głównego są sprawdzane z ograniczeniami klucza obcego w powiązanych tabelach.
Integralność
Głównym celem ograniczenia klucza obcego jest kontrola danych, które mogą być przechowywane w tabeli klucza obcego, kontroluje również zmiany danych w tabeli klucza głównego. Na przykład, jeśli wiersz dla sprzedawcy zostanie usunięty z tabeli Sales.SalesPerson, a identyfikator sprzedawcy jest używany w zamówieniach sprzedaży w tabeli Sales.SalesOrderHeader, integralność relacyjna między tymi dwiema tabelami zostaje naruszona; zamówienia sprzedaży usuniętego sprzedawcy są osierocone w tabeli SalesOrderHeader bez powiązania z danymi w tabeli SalesPerson.
Konwencja klucza obcego zapobiega tej sytuacji. Ograniczenie wymusza integralność referencyjną, gwarantując, że zmiany nie mogą być dokonane w danych w tabeli klucza głównego, jeśli te zmiany unieważniają połączenie z danymi w tabeli klucza obcego. Jeśli zostanie podjęta próba usunięcia wiersza w tabeli klucza głównego lub zmiany wartości klucza głównego, akcja nie powiedzie się, jeśli usunięta lub zmieniona wartość klucza głównego odpowiada wartości w ograniczeniu klucza obcego innej tabeli. Aby zmienić lub usunąć wiersz, musisz najpierw usunąć dane klucza obcego lub zmienić dane klucza obcego, który łączy klucz obcy z innymi danymi klucza głównego.
Kaskadowa integralność
Używając kaskadowych ograniczeń integralności, możesz określić, które akcje zostaną podjęte przez silnik bazy danych, gdy użytkownik spróbuje usunąć lub zaktualizować klucz, na który wskazują istniejące klucze obce. Następujące akcje kaskadowe mogą być zdefiniowane.
BRAK AKCJI
Silnik bazy danych podnosi błąd i akcja usuwania lub aktualizacji wiersza w tabeli nadrzędnej jest cofana.
KASKADOWE
Korespondujące wiersze są aktualizowane lub usuwane w tabeli odniesienia, gdy wiersz jest aktualizowany lub usuwany w tabeli nadrzędnej. CASCADE nie może być określone, jeśli kolumna znacznika czasu jest częścią klucza obcego lub klucza odniesienia. Kaskada ON DELETE nie może być określona dla tabeli, która posiada triger INSTEAD OF DELETE. ON UPDATE CASCADE nie może być określone dla tabel, które posiadają triger INSTEAD OF UPDATE.
SET NULL
Wszystkie wartości klucza obcego są ustawiane na NULL, gdy odpowiadający im wiersz w tabeli nadrzędnej jest aktualizowany lub usuwany. Aby warunek został wykonany, kolumny klucza obcego muszą być zerowe. Nie może być określone dla tabel, które posiadają triger INSTEAD OF UPDATE.
SET DEFAULT
Wszystkie wartości klucza obcego są ustawiane na wartości domyślne, jeśli odpowiadający im wiersz w tabeli nadrzędnej jest aktualizowany lub usuwany. Aby warunek został wykonany, wszystkie kolumny klucza obcego muszą mieć domyślne definicje. Jeśli kolumna jest nullable i nie ma jawnie ustawionej wartości domyślnej, NULL staje się domyślną wartością domyślną kolumny. Nie może być określone dla tabel, które posiadają trigery INSTEAD OF UPDATE.
CASCADE, SET NULL, SET DEFAULT i NO ACTION mogą być połączone na tabelach, które mają wzajemne relacje odniesienia. Jeśli silnik bazy danych napotka NO ACTION, zatrzyma i cofnie powiązane akcje CASCADE, SET NULL i SET DEFAULT. Jeśli instrukcja DELETE powoduje kombinację akcji CASCADE, SET NULL, SET DEFAULT i NO ACTION, wszystkie akcje CASCADE, SET NULL i SET DEFAULT są stosowane przed sprawdzeniem NO ACTION.
Wyzwalacze i kaskadowe akcje referencyjne
Kaskadowe akcje referencyjne wywołują wyzwalacze AFTER UPDATE lub AFTER DELETE w następujący sposób:
-
Wszystkie kaskadowe akcje referencyjne wywołane bezpośrednio przez oryginalne DELETE lub UPDATE wykonywane są w pierwszej kolejności.
-
Jeśli istnieją jakiekolwiek wyzwalacze AFTER zdefiniowane na tabelach, których dotyczą, zostaną one uruchomione po wykonaniu wszystkich akcji kaskadowych. Wyzwalacze te odpalają się w kolejności odwrotnej do akcji kaskadowej. Jeśli na jednej tabeli znajduje się wiele trigerów, są one wywoływane w losowej kolejności, chyba że istnieje dedykowany pierwszy lub ostatni triger dla danej tabeli. Kolejność ta jest określona przez użycie sp_settriggerorder.
-
Jeśli wiele łańcuchów kaskadowych pochodzi z tabeli, która była bezpośrednim celem akcji UPDATE lub DELETE, kolejność w jakiej te łańcuchy odpalają swoje odpowiednie trigery jest nieokreślona. Jednakże, jeden łańcuch zawsze wywołuje wszystkie swoje trigery przed rozpoczęciem wywoływania innego łańcucha.
-
Wyzwalacz AFTER na tabeli będącej bezpośrednim celem akcji UPDATE lub DELETE wywołuje się niezależnie od tego, czy zostały naruszone jakieś wiersze. W tym przypadku nie ma innych tabel dotkniętych kaskadowaniem.
-
Jeśli którykolwiek z poprzednich trigerów wykona operacje UPDATE lub DELETE na innych tabelach, akcje te mogą rozpocząć wtórne łańcuchy kaskadowe. Te drugorzędne łańcuchy są przetwarzane dla każdej operacji UPDATE lub DELETE na raz po wystrzeleniu wszystkich wyzwalaczy we wszystkich łańcuchach pierwotnych. Proces ten może być powtarzany rekurencyjnie dla kolejnych operacji UPDATE lub DELETE.
-
Wykonanie operacji CREATE, ALTER, DELETE lub innych operacji języka definicji danych (DDL) wewnątrz wyzwalaczy może spowodować wywołanie wyzwalaczy DDL. Może to następnie wykonać operacje DELETE lub UPDATE, które uruchamiają dodatkowe kaskadowe łańcuchy i wyzwalacze.
-
Jeśli błąd zostanie wygenerowany wewnątrz dowolnego konkretnego kaskadowego łańcucha akcji referencyjnych, zostanie podniesiony błąd, żadne wyzwalacze AFTER nie zostaną uruchomione w tym łańcuchu, a operacja DELETE lub UPDATE, która utworzyła łańcuch, zostanie cofnięta.
-
Tabela, która posiada triger INSTEAD OF nie może posiadać klauzuli REFERENCES określającej akcję kaskadową. Jednakże triger AFTER może wywołać polecenie INSERT, UPDATE lub DELETE w innej tabeli lub widoku, w którym wywołano triger INSTEAD OF zdefiniowany na tym obiekcie.
Zadania pokrewne
Poniższa tabela przedstawia listę zadań związanych z kluczem podstawowym i obcym.
| Zadanie | Temat |
|---|---|
| Opisuje jak utworzyć klucz główny. | Create Primary Keys |
| Opisuje, jak usunąć klucz główny. | Delete Primary Keys |
| Opisuje, jak zmodyfikować klucz główny. | Modify Primary Keys |
| Opisuje, jak tworzyć relacje klucza obcego | Create Foreign Key Relationships |
| Opisuje, jak modyfikować relacje klucza obcego. | Modify Foreign Key Relationships |
| Opisuje, jak usunąć relacje klucza obcego. | Delete Foreign Key Relationships |
| Opisuje, jak wyświetlić właściwości klucza obcego. | View Foreign Key Properties |
| Opisuje, jak wyłączyć ograniczenia klucza obcego dla replikacji. | Disable Foreign Key Constraints for Replication |
| Opisuje, jak wyłączyć ograniczenia klucza obcego podczas instrukcji INSERT lub UPDATE. | Disable Foreign Key Constraints with INSERT and UPDATE Statements |