Jeśli jesteś właścicielem przedsiębiorstwa, na pewno zetknąłeś się z wartością i potrzebą danych w swojej firmie. Posiadanie środków do przechowywania baz danych i manipulowania nimi zwiększa wartość firmy.
Bazy danych są zorganizowane w określonej konwencji i pozwalają na ustrukturyzowanie danych w połączeniach, co przybliża nas do relacyjnych baz danych, które jako forma zarządzania danymi zostały przyjęte od lat 70. XX wieku. A na dzisiejszym rynku relacyjne bazy danych są preferowane ze względu na ich możliwości podczas manipulowania danymi.
Chociaż istnieje wiele dostępnych relacyjnych baz danych, MySQL znalazł się w czołówce, zajmując drugie miejsce na świecie według Statista według stanu na styczeń 2022 r.
W serwerze SQL ograniczenia to predefiniowane reguły i ograniczenia wymuszane w jednej lub wielu kolumnach; są one powiązane z wartościami w kolumnie i pomagają w utrzymaniu integralności, dokładności i wiarygodności danych w określonych kolumnach.
Mówiąc najprościej, tylko dane, które spełniają regułę ograniczenia, są pomyślnie wstawiane do kolumny. Operacja wstawiania zostaje zakończona, jeśli dane nie spełniają kryteriów.
W tym poście zakładamy, że zetknąłeś się z relacyjnymi bazami danych, w szczególności – MySQL, i nie możesz się doczekać wzmocnienia swojej wiedzy w tej domenie. Na koniec podzielę się kilkoma wskazówkami dotyczącymi interakcji z ograniczeniami klucza obcego.
Spis treści:
Główne ograniczenia klucza – podsumowanie
Tabela w języku SQL obejmuje kolumnę lub kilka zawierających kluczowe wartości, które precyzyjnie określają każdy wiersz w systemach. Kolumna lub kolumny zatytułowane klucz podstawowy (PK) tabeli pełnią rolę wymuszenia integralności encji tabeli. Ograniczenia klucza podstawowego gwarantują unikatowe dane i często są definiowane w kolumnie tożsamości.
Po określeniu ograniczeń klucza podstawowego dla tabeli, silnik bazy danych automatycznie narzuca unikatowość danych, generując unikalne indeksy dla każdej kolumny podstawowej. Klucze podstawowe oferują wyjątkową przewagę, gdy są używane w zapytaniach, zapewniając szybki dostęp do danych.
Jeśli ograniczenia klucza podstawowego są zdefiniowane w wielu kolumnach, jest to nazywane złożonym lub złożonym kluczem podstawowym. W tym przypadku każda kolumna klucza podstawowego może zawierać zduplikowane wartości. Jednak połączone wartości ze wszystkich kolumn w kluczu podstawowym muszą być unikalne.
Dobrym przykładem jest przypadek, w którym masz tabelę z kolumnami `id`, `names` i `age`. Kiedy zdefiniujesz ograniczenie klucza głównego na kombinacji `id` i `names`, możesz mieć zduplikowane instancje wartości `id` lub `names`. Mimo to każda kombinacja musi być unikalna, aby uniknąć zduplikowanych wierszy. Więc możesz mieć rekordy z `id=1` i `name=Walter` oraz `age-22” i `id=1`, `name=Henry` i `age=27`, ale nie możesz mieć innych rekordów z `id=1` i `name=Walter`, ponieważ kombinacja nie jest unikalna.
Oto kilka istotnych aspektów, o których należy wiedzieć:
Ograniczenia klucza obcego — podsumowanie
Klucz obcy (FK) obejmuje kolumnę lub kombinację kilku używanych do tworzenia i wiązania łącza między dwiema tabelami i zarządza danymi, które mają być przechowywane w tabeli klucza obcego.
Odwołanie do klucza obcego pociąga za sobą utworzenie łącza między dwiema tabelami; gdy kolumna lub kolumny zawierające klucz podstawowy dla innej tabeli są przywoływane przez kolumnę lub kolumny w innej tabeli.
W scenariuszu odniesienia do klucza obcego połączenie jest tworzone między dwiema tabelami, gdy kolumna lub kolumny zawierające klucze podstawowe w tabeli są odwoływane przez kolumny w innej.
W praktycznym przypadku możesz mieć tabelę Sales.SalesOrderHeader z kluczem obcym prowadzącym do innej tabeli, Sales.Osoba, ponieważ istnieje logiczna relacja między sprzedawcami a zamówieniami sprzedaży.
Tutaj SalesPersonID w kolumnie SalesOrderHeader łączy się z kolumną klucza podstawowego tabeli SalesPerson. Kluczem obcym tabeli SalesPerson jest kolumna SalesPersonID w SalesOrderHeader.
Ta relacja definiuje regułę: wartość SalesPersonID nie może znajdować się w tabeli SalesOrderHeader, jeśli nie istnieje w tabeli SalesPerson.
Tabela może odwoływać się do 253 innych kolumn i tabel jako kluczy obcych, alternatywnie nazywanych odwołaniami wychodzącymi. Od 2016 r. serwer SQL zwiększył liczbę tabel i kolumn, do których można się odwoływać w pojedynczej tabeli, zwanej również odwołaniami przychodzącymi, z 253 do 10 000. Wzrost ten wiąże się jednak z pewnymi ograniczeniami:
Jakie są zalety kluczy obcych?
Jak wspomniano wcześniej, ograniczenia klucza obcego odgrywają zasadniczą rolę w ochronie integralności i spójności danych w relacyjnej bazie danych. Oto zestawienie powodów, dla których ograniczenia klucza obcego są niezbędne.
Indeksy ograniczeń klucza obcego
Ograniczenia klucza obcego nie tworzą automatycznie odpowiednich indeksów, takich jak podstawowy. Możesz ręcznie tworzyć indeksy dla ograniczeń klucza obcego; jest to korzystne z następujących powodów.
- Kolumny klucza obcego są często używane w kryteriach łączenia podczas łączenia danych z powiązanych tabel w zapytaniach przez dopasowanie kolumn powiązanych z ograniczeniem. Indeksy pomagają bazie danych w znalezieniu powiązanych danych w obcej tabeli.
- Jeśli zmienisz ograniczenia klucza podstawowego, zostaną one sprawdzone z obcymi w powiązanych tabelach.
Tworzenie indeksów nie jest obowiązkowe. Nadal można łączyć dane z dwóch tabel bez określania ograniczeń dotyczących klucza podstawowego i obcego. Jednak dodanie ograniczeń klucza obcego optymalizuje tabele i łączy je w kwerendzie, która spełnia kryteria używania kluczy. Jeśli zmienisz ograniczenia klucza podstawowego, zostaną one sprawdzone z obcymi w powiązanych.
Wskazówki dotyczące tworzenia ograniczeń klucza obcego w SQL
Spędziliście już dużo czasu na spekulacjach; odpowiedział dlaczego. Zmieńmy naszą uwagę i zawęźmy ją do taktyki tworzenia ograniczeń klucza obcego; odpowiedz jak.
Pole „Klucz obcy” w tabeli odnosi się do „Klucza podstawowego” innego. Tabela z kluczem podstawowym jest twoją tabelą nadrzędną. A tabela z kluczem obcym nazywana jest tabelą podrzędną. Zanurzmy się.
Tworzenie klucza obcego podczas tworzenia tabeli
Podczas tworzenia tabeli można również utworzyć ograniczenie klucza obcego, aby zachować integralność referencyjną. Oto jak to zrobić:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Powyższy kod tworzy tabelę o nazwie „orders” z głównym kluczem całkowitym „order_id”, inną liczbą całkowitą „customer_id” i datą „order_date”. W takim przypadku ograniczenie KLUCZ ZAGRANICZNY jest dodawane do kolumny „identyfikator_klienta” i odwołuje się do „identyfikatora_klienta” w tabeli „klienci”.
Tworzenie klucza obcego po utworzeniu tabeli
Załóżmy, że utworzyłeś już tabelę i chcesz dodać ograniczenie klucza obcego; użyj instrukcji `ALTER TABLE` w swoim kodzie. Spójrz na fragment kodu poniżej.
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
W tym przypadku dodano kolumnę „identyfikator_klienta” z ograniczeniem klucza obcego w tabeli „zamówienia”, aby odwoływać się do kolumny „identyfikator_klienta” w tabeli „klienci”.
Tworzenie klucza obcego bez sprawdzania istniejących danych
Po dodaniu ograniczenia klucza obcego do tabeli baza danych automatycznie sprawdza istniejące dane, aby zapewnić spójność z ograniczeniem. Jeśli jednak wiesz, że dane są spójne i chcesz dodać ograniczenie bez sprawdzania spójności, oto jak to zrobić.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) NOT VALIDATE;
Polecenie NOT VALIDATE informuje bazę danych, aby nie sprawdzała istniejących danych. Ten konkretny przypadek jest pomocny w określonych przypadkach. Na przykład, gdy masz ogromne ilości danych i chcesz zakończyć proces sprawdzania poprawności.
Tworzenie klucza obcego za pomocą DELETE/UPDATE
Tworząc ograniczenia klucza obcego, możesz kierować działaniami, które mają zostać podjęte w przypadkach, gdy odnośny wiersz zostanie zaktualizowany lub usunięty. W tym przypadku używasz kaskadowych ograniczeń integralności referencyjnej, aby dyktować działania, które należy podjąć. Zawierają:
# 1. BEZ AKCJI
Podobnie jak w przypadku wielu innych baz danych, reguła „BRAK AKCJI” jest domyślnym zachowaniem podczas tworzenia ograniczenia klucza obcego. Oznacza to, że po usunięciu lub zaktualizowaniu wiersza, do którego następuje odwołanie, nie zostanie podjęte żadne działanie.
Aparat baz danych zgłasza błąd w przypadku naruszenia ograniczenia klucza obcego. Nie jest to jednak zalecane, ponieważ może prowadzić do problemów z integralnością referencyjną, ponieważ należy wymusić ograniczenie klucza obcego. Oto przykład, jak to zrobić:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
#2. KASKADA
Reguła „KASKADA” to kolejna opcja dla akcji „ON DELETE” i „ON UPDATE” podczas tworzenia ograniczeń klucza obcego. Po wprowadzeniu oznacza to, że za każdym razem, gdy wiersz jest aktualizowany lub usuwany w tabelach nadrzędnych, wiersze, do których się odwołuje, są odpowiednio aktualizowane lub usuwane. Ta technika jest potężna przy utrzymywaniu integralności referencyjnej. Oto przykład:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE;
Należy być ostrożnym podczas korzystania z tej zasady, ponieważ może ona spowodować niepożądane konsekwencje, jeśli nie jest stosowana ostrożnie. Chciałbyś uniknąć przypadkowego usunięcia zbyt dużej ilości danych lub tworzenia odwołań cyklicznych. Dlatego używaj tej opcji tylko wtedy, gdy jest to konieczne i ostrożnie.
Istnieje kilka zasad korzystania z KASKADY:
- Nie można określić CASCADE, jeśli kolumna znacznika czasu jest częścią klucza obcego lub klucza odniesienia.
- Jeśli twoja tabela ma wyzwalacz INSTEAD OF DELETE, nie możesz określić ON DELETED CASCADE.
- Nie możesz określić ON UPDATE CASCADE, jeśli twoja tabela ma wyzwalacz INSTEAD OF UPDATE.
#3. USTAW NULL
Gdy usuniesz lub zaktualizujesz odpowiedni wiersz w tabeli nadrzędnej, wszystkie wartości tworzące klucz obcy zostaną ustawione na wartość null. Ta reguła ograniczenia wymaga, aby kolumny klucza obcego dopuszczały wartość null do wykonania i nie można jej określić dla tabel z wyzwalaczami INSTEAD OF UPDATE. Oto przykład, jak to zrobić.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL ON UPDATE SET NULL
W tym przypadku ustawiłeś kolumnę klucza obcego „identyfikator_klienta” w tabeli „zamówienia” na wartość NULL, jeśli odpowiedni wiersz w tabeli „klienci” zostanie usunięty lub zaktualizowany.
#4. USTAW DOMYŚLNE
Tutaj ustawiasz wszystkie wartości, które sprawiają, że klucz obcy jest domyślny, pod warunkiem, że wiersz, do którego się odwołuje, w tabeli nadrzędnej zostanie zaktualizowany lub usunięty.
To ograniczenie jest wykonywane, jeśli wszystkie kolumny klucza obcego mają definicje domyślne. Jeśli kolumna dopuszcza wartość null, jej wartość domyślna to NULL. Należy zauważyć, że tej opcji nie można określić dla tabel z wyzwalaczami INSTEAD OF UPDATE. Oto przykład:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
W powyższym przypadku ustawiłeś „identyfikator_klienta” w tabeli „zamówienia” na wartość domyślną, co ma miejsce, gdy odpowiedni wiersz w tabeli „klienci” zostanie usunięty lub zaktualizowany.
Ostatnie słowa
W tym przewodniku odświeżyłeś sobie ograniczenia klucza podstawowego i zagłębiłeś się w ograniczenia klucza obcego. Napotkałeś również kilka technik tworzenia ograniczeń klucza obcego. I chociaż istnieje wiele sposobów tworzenia ograniczeń klucza obcego, ten post rozwikłał metody.
I mając nadzieję, że opanowałeś nowe techniki; nie jesteś ograniczony do ich łączenia. Na przykład metody ograniczeń CASCADE, SET NULL, SET DEFAULT i NO ACTION można łączyć w tabelach z relacjami referencyjnymi.
Jeśli twoja tabela napotka BRAK AKCJI, powróci do innych reguł ograniczeń. W innych przypadkach akcja USUŃ może wywołać kombinację tych reguł, a reguła BRAK AKCJI zostanie uruchomiona jako ostatnia.
Następnie sprawdź ściągawkę SQL.