Ograniczenia klucza podstawowego i obcego

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL Database w usłudze Microsoft Fabric

Klucze podstawowe i klucze obce to dwa typy ograniczeń, których można użyć do wymuszania integralności danych w tabelach programu SQL Server. Są to ważne obiekty bazy danych.

Ograniczenia klucza podstawowego

Tabela zazwyczaj zawiera kolumnę lub kombinację kolumn, które zawierają wartości, które jednoznacznie identyfikują każdy wiersz w tabeli. Ta kolumna lub kolumny jest nazywana kluczem podstawowym (PK) tabeli i wymusza integralność jednostki tabeli. Ponieważ ograniczenia klucza podstawowego gwarantują unikatowe dane, są one często definiowane w kolumnie tożsamości.

Po określeniu ograniczenia klucza podstawowego dla tabeli aparat bazy danych wymusza unikatowość danych przez automatyczne utworzenie unikatowego indeksu dla kolumn klucza podstawowego. Ten indeks umożliwia również szybki dostęp do danych, gdy klucz podstawowy jest używany w zapytaniach. Jeśli ograniczenie klucza podstawowego jest zdefiniowane w więcej niż jednej kolumnie, wartości mogą być duplikowane w jednej kolumnie, ale każda kombinacja wartości ze wszystkich kolumn w definicji ograniczenia klucza podstawowego musi być unikatowa.

Jak pokazano na poniższej ilustracji, kolumny ProductID i VendorID w tabeli Purchasing.ProductVendor tworzą ograniczenie złożonego klucza podstawowego dla tej tabeli. Dzięki temu każdy wiersz w tabeli ProductVendor ma unikatową kombinację ProductID i VendorID. To zapobiega wstawianiu zduplikowanych wierszy.

• Tabela może zawierać tylko jedno ograniczenie klucza podstawowego.
• Klucz podstawowy nie może przekraczać 16 kolumn i całkowitej długości klucza 900 bajtów.
• Indeks wygenerowany przez ograniczenie klucza podstawowego nie może spowodować, że liczba indeksów w tabeli przekroczy 999 indeksów nieklastrowanych i 1 indeks klastrowany.
• Jeśli dla ograniczenia klucza podstawowego nie określono, czy ma być klasterowane czy nieklasterowane, to zostanie użyte klasterowane, o ile w tabeli nie istnieje indeks klasterowany.
• Wszystkie kolumny w ramach ograniczenia klucza podstawowego muszą być niepuste. Jeśli nullowalność nie jest określona, wszystkie kolumny uczestniczące w ograniczeniu klucza podstawowego mają ustawioną nullowalność na brak wartości null.
• Jeśli klucz podstawowy jest zdefiniowany w kolumnie typu zdefiniowanego przez użytkownika CLR, implementacja typu musi obsługiwać porządkowanie binarne.

Ograniczenia klucza obcego

Klucz obcy (FK) to kolumna lub kombinacja kolumn używanych do ustanawiania i wymuszania połączenia między danymi w dwóch tabelach w celu kontrolowania danych, które mogą być przechowywane w tabeli kluczy obcych. W odwołaniu do klucza obcego tworzy się łącze między dwiema tabelami, gdy kolumna lub kolumny przechowujące wartość klucza podstawowego dla jednej tabeli są referowane przez kolumnę lub kolumny w innej tabeli. Ta kolumna staje się kluczem obcym w drugiej tabeli.

Na przykład tabela Sales.SalesOrderHeader ma link klucza obcego do tabeli Sales.SalesPerson, ponieważ istnieje logiczna relacja między zamówieniami sprzedaży a sprzedawcami. Kolumna SalesPersonID w tabeli SalesOrderHeader jest zgodna z kolumną klucza podstawowego tabeli SalesPerson. Kolumna SalesPersonID w tabeli SalesOrderHeader jest kluczem obcym do tabeli SalesPerson. Tworząc tę relację klucza obcego, nie można wstawić wartości SalesPersonID do tabeli SalesOrderHeader, jeśli nie istnieje już w tabeli SalesPerson.

Tabela może odwoływać się jako klucz obcy do maksymalnie 253 innych tabel i kolumn (odwołania wychodzące). Program SQL Server 2016 (13.x) zwiększa limit liczby innych tabel i kolumn, które mogą odwoływać się do kolumn w pojedynczej tabeli (odwołania przychodzące), od 253 do 10 000. (Wymaga co najmniej poziomu zgodności 130.) Wzrost ma następujące ograniczenia:

• Więcej niż 253 odwołania do kluczy obcych są obsługiwane tylko w przypadku operacji DML DELETE. operacje UPDATE i MERGE nie są obsługiwane.

• Tabela odwołująca się do samej siebie kluczem obcym jest nadal ograniczona do 253 odwołań kluczy obcych.

• Referencje kluczy obcych większe niż 253 nie są obsługiwane dla indeksów kolumnowych, tabel zoptymalizowanych pod kątem pamięci, Stretch Database ani partycjonowanych tabel kluczy obcych.

  Ważny

  Baza danych Stretch jest przestarzała w programie SQL Server 2022 (16.x) i usłudze Azure SQL Database. Ta funkcja zostanie usunięta w przyszłej wersji silnika bazy danych. Unikaj używania tej funkcji w nowych pracach programistycznych i zaplanuj modyfikowanie aplikacji, które obecnie korzystają z tej funkcji.

Indeksy dotyczące ograniczeń klucza obcego

W przeciwieństwie do ograniczeń klucza podstawowego utworzenie ograniczenia klucza obcego nie powoduje automatycznego utworzenia odpowiedniego indeksu. Jednak ręczne tworzenie indeksu klucza obcego jest często przydatne z następujących powodów:

• Kolumny kluczy obcych są często używane w kryteriach łączenia, gdy dane z powiązanych tabel są łączone w zapytaniach poprzez dopasowanie kolumn w ograniczeniu klucza obcego jednej tabeli do kolumny lub kolumn klucza głównego albo unikatowego w innej tabeli. Indeks umożliwia aparatowi bazy danych szybkie znajdowanie powiązanych danych w tabeli kluczy obcych. Jednak utworzenie tego indeksu nie jest wymagane. Dane z dwóch powiązanych tabel można łączyć nawet wtedy, gdy między tabelami nie zdefiniowano żadnych ograniczeń klucza podstawowego lub klucza obcego, ale relacja klucza obcego między dwiema tabelami wskazuje, że dwie tabele zostały zoptymalizowane pod kątem łączenia w zapytaniu, które używa kluczy jako kryteriów.

• Zmiany ograniczeń klucza podstawowego są sprawdzane przy użyciu ograniczeń klucza obcego w powiązanych tabelach.

Integralność referencyjna

Chociaż głównym celem ograniczenia klucza obcego jest kontrolowanie danych, które mogą być przechowywane w tabeli kluczy obcych, kontroluje również zmiany danych w tabeli kluczy podstawowych. Jeśli na przykład wiersz sprzedawcy zostanie usunięty z tabeli Sales.SalesPerson, a identyfikator sprzedawcy jest używany do zamówień sprzedaży w tabeli Sales.SalesOrderHeader, integralność relacyjna między tymi dwiema tabelami zostaje zerwana; zamówienia sprzedaży usuniętego sprzedawcy w tabeli SalesOrderHeader stają się osierocone, bez połączenia z danymi w tabeli SalesPerson.

Ograniczenie klucza obcego zapobiega tej sytuacji. Ograniczenie wymusza integralność referencyjną przez zagwarantowanie, że zmiany nie mogą zostać wprowadzone w danych w tabeli kluczy podstawowych, jeśli te zmiany unieważniają łącze do danych w tabeli kluczy obcych. Jeśli zostanie podjęta próba usunięcia wiersza w tabeli klucza podstawowego lub zmiany wartości klucza podstawowego, akcja nie powiedzie się, gdy usunięta lub zmieniona wartość klucza podstawowego odpowiada wartości w ograniczeniu klucza obcego innej tabeli. Aby pomyślnie zmienić lub usunąć wiersz w ograniczeniu klucza obcego, należy najpierw usunąć dane klucza obcego w tabeli kluczy obcych lub zmienić dane klucza obcego w tabeli kluczy obcych, która łączy klucz obcy z różnymi danymi klucza podstawowego.

Kaskadowa integralność referencyjna

Korzystając z kaskadowych ograniczeń integralności referencyjnej, można zdefiniować akcje podejmowane przez silnik bazy danych, gdy użytkownik próbuje usunąć lub zaktualizować klucz, do którego odnoszą się istniejące klucze obce. Można zdefiniować następujące akcje kaskadowe.

• NO ACTION

  Aparat bazy danych zgłasza błąd, a akcja usuwania lub aktualizacji w wierszu tabeli nadrzędnej jest cofana.

• CASCADE

  Odpowiednie wiersze są aktualizowane lub usuwane w tabeli odwołującej się po zaktualizowaniu lub usunięciu tego wiersza w tabeli nadrzędnej. CASCADE nie można określić, jeśli kolumna znacznika czasu jest częścią klucza obcego lub klucza, do którego się odnosi. ON DELETE CASCADE nie można określić dla tabeli, która ma wyzwalacz INSTEAD OF DELETE. ON UPDATE CASCADE nie można określić dla tabel, które mają wyzwalacze INSTEAD OF UPDATE.

• SET NULL

  Wszystkie wartości tworzące klucz obcy są ustawione na NULL po zaktualizowaniu lub usunięciu odpowiedniego wiersza w tabeli nadrzędnej. Aby to ograniczenie było wykonywane, kolumny klucza obcego muszą mieć wartość null. Nie można ustawić dla tabel, które mają wyzwalacze INSTEAD OF UPDATE.

• SET DEFAULT

  Wszystkie wartości tworzące klucz obcy są ustawione na wartości domyślne, jeśli odpowiedni wiersz w tabeli nadrzędnej zostanie zaktualizowany lub usunięty. Aby to ograniczenie było wykonywane, wszystkie kolumny klucza obcego muszą mieć definicje domyślne. Jeśli kolumna może mieć wartość null i nie ma jawnej wartości domyślnej, NULL staje się domyślną wartością kolumny. Nie można określić dla tabel, które mają wyzwalacze INSTEAD OF UPDATE.

CASCADE, SET NULL, SET DEFAULTi NO ACTION można łączyć w tabelach, które mają relacje referencyjne między sobą. Jeśli silnik bazy danych napotka NO ACTION, zatrzymuje się i cofa powiązane operacje CASCADE, SET NULLi SET DEFAULT. Gdy instrukcja DELETE powoduje połączenie akcji CASCADE, SET NULL, SET DEFAULTlub NO ACTION, wszystkie akcje CASCADE, SET NULLi SET DEFAULT są stosowane przed sprawdzeniem przez silnik bazy danych jakichkolwiek NO ACTION.

Wyzwalacze i kaskadowe operacje referencyjne

Kaskadowe akcje referencyjne uruchamiają wyzwalacze AFTER UPDATE lub AFTER DELETE w następujący sposób:

• Wszystkie kaskadowe akcje referencyjne bezpośrednio spowodowane przez oryginalne DELETE lub UPDATE są wykonywane jako pierwsze.

• Jeśli w tabelach, których dotyczy problem, istnieją jakiekolwiek wyzwalacze AFTER, to te wyzwalacze są uruchamiane, kiedy wszystkie akcje kaskadowe zostaną wykonane. Te wyzwalacze są uruchamiane w odwrotnej kolejności akcji kaskadowej. Jeśli istnieje wiele wyzwalaczy w jednej tabeli, są uruchamiane w kolejności losowej, chyba że istnieje dedykowany pierwszy lub ostatni wyzwalacz dla tabeli. Ta kolejność jest określona przy użyciu 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 uruchamiają swoje odpowiednie wyzwalacze, jest nieokreślona. Jednak jeden łańcuch zawsze uruchamia wszystkie swoje wyzwalacze, zanim inny łańcuch zacznie się uruchamiać.

• Wyzwalacz AFTER w tabeli, który jest bezpośrednim celem akcji UPDATE lub DELETE, zostaje uruchomiony niezależnie od tego, czy ma to wpływ na jakiekolwiek wiersze. W tym przypadku żadna z innych tabel nie jest dotknięta przez kaskadowe operacje.

• Jeśli którykolwiek z poprzednich wyzwalaczy wykona operacje UPDATE lub DELETE w innych tabelach, te akcje mogą uruchomić dodatkowe łańcuchy kaskadowe. Te łańcuchy pomocnicze są przetwarzane dla każdej operacji UPDATE lub DELETE jednocześnie po uruchomieniu wszystkich wyzwalaczy we wszystkich łańcuchach podstawowych. Ten proces może być cyklicznie powtarzany w przypadku kolejnych operacji UPDATE lub DELETE.

• Wykonywanie CREATE, ALTER, DELETElub innych operacji języka definicji danych (DDL) wewnątrz wyzwalaczy może spowodować uruchomienie wyzwalaczy DDL. Może to następnie wykonywać operacje DELETE lub UPDATE, które uruchamiają dodatkowe kaskadowe łańcuchy i wyzwalacze.

• Jeśli wewnątrz dowolnego kaskadowego łańcucha akcji referencyjnych zostanie wygenerowany błąd, zostanie on zasygnalizowany, w tym łańcuchu nie zostaną uruchomione żadne wyzwalacze AFTER, a operacja DELETE lub UPDATE, która utworzyła ten łańcuch, zostanie cofnięta.

• Tabela z wyzwalaczem INSTEAD OF nie może również mieć klauzuli REFERENCES, która określa akcję kaskadową. Jednak wyzwalacz AFTER w tabeli objętej akcją kaskadową może wykonać instrukcję INSERT, UPDATElub DELETE w innej tabeli lub widoku, który uruchamia wyzwalacz INSTEAD OF zdefiniowany na tym obiekcie.

Powiązana zawartość

• Tworzenie Kluczy Podstawowych
• usuń klucze podstawowe
• modyfikowanie kluczy podstawowych
• Tworzenie Relacji Kluczy Obcych
• Modyfikowanie relacji kluczy obcych
• usuń relacje kluczy obcych
• wyświetl właściwości klucza obcego
• Wyłącz ograniczenia kluczy obcych dla replikacji
• wyłącz ograniczenia klucza obcego za pomocą instrukcji INSERT i UPDATE