Jak tworzyć ograniczenia klucza obcego w SQL

Każdy przedsiębiorca zdaje sobie sprawę z kluczowej roli danych w funkcjonowaniu firmy. Posiadanie odpowiednich narzędzi do zarządzania i przechowywania baz danych to inwestycja, która podnosi wartość całego przedsięwzięcia.

Bazy danych opierają się na określonych zasadach organizacji, pozwalając na strukturyzację informacji w powiązania. To podejście doprowadziło do powstania relacyjnych baz danych, które od lat 70. XX wieku stanowią standard w zarządzaniu danymi. Ich popularność na dzisiejszym rynku wynika z rozbudowanych możliwości manipulowania danymi.

Choć na rynku dostępnych jest wiele relacyjnych baz danych, MySQL plasuje się w ścisłej czołówce, zajmując drugie miejsce na świecie według danych Statista ze stycznia 2022 roku.

W serwerze SQL istotną rolę odgrywają ograniczenia, czyli predefiniowane reguły narzucane na jedną lub wiele kolumn. Te ograniczenia, ściśle powiązane z wartościami w kolumnie, pomagają utrzymać integralność, dokładność i wiarygodność danych w ramach konkretnych kolumn.

Mówiąc wprost, do kolumny trafiają tylko te dane, które spełniają ustalone reguły ograniczeń. Jeśli dane nie odpowiadają kryteriom, operacja ich wstawienia nie zostanie zakończona.

Zakładamy, że masz już pewną wiedzę na temat relacyjnych baz danych, w szczególności MySQL, i szukasz sposobów na jej poszerzenie. Na koniec tego artykułu podzielimy się kilkoma praktycznymi wskazówkami dotyczącymi pracy z ograniczeniami klucza obcego.

Kluczowe ograniczenia podstawowe – podsumowanie

W tabeli SQL, klucz podstawowy (PK) to kolumna lub zestaw kolumn, które jednoznacznie identyfikują każdy wiersz w tabeli. Klucz podstawowy jest gwarantem integralności danych w tabeli i zapewnia unikalność każdego rekordu. Często klucz podstawowy jest definiowany w kolumnie tożsamości.

Po zdefiniowaniu klucza podstawowego dla tabeli, silnik bazy danych automatycznie dba o unikalność danych, tworząc unikalne indeksy dla każdej kolumny, która wchodzi w skład klucza. Klucze podstawowe są niezwykle przydatne w zapytaniach, przyspieszając dostęp do danych.

Jeżeli ograniczenie klucza podstawowego obejmuje więcej niż jedną kolumnę, mamy do czynienia ze złożonym kluczem podstawowym. W takim przypadku poszczególne kolumny wchodzące w skład klucza mogą zawierać duplikaty, ale kombinacja wartości we wszystkich tych kolumnach musi być unikalna dla każdego wiersza.

Dobrym przykładem jest tabela z kolumnami `id`, `names` i `age`. Jeśli zdefiniujemy klucz podstawowy na kombinacji `id` i `names`, możemy mieć zduplikowane wartości `id` lub `names`. Jednak kombinacja wartości w obu kolumnach musi być unikalna. Na przykład, rekordy `id=1` i `name=Walter`, `age=22` oraz `id=1`, `name=Henry`, `age=27` są poprawne, ale nie możemy dodać kolejnego rekordu z `id=1` i `name=Walter`, ponieważ taka kombinacja już istnieje.

Oto kilka kluczowych aspektów, o których warto pamiętać:

  • W tabeli może być zdefiniowane tylko jedno ograniczenie klucza podstawowego.
  • Klucz podstawowy nie może składać się z więcej niż 16 kolumn i nie może przekraczać 900 znaków.
  • Indeksy generowane przez klucze podstawowe mogą wpływać na liczbę indeksów w tabeli. Liczba indeksów klastrowanych nie może przekroczyć 1, a liczba indeksów nieklastrowanych jest ograniczona do 999.
  • Jeśli typ indeksu (klastrowany lub nieklastrowany) nie zostanie określony, domyślnie tworzony jest indeks klastrowany.
  • Wszystkie kolumny wchodzące w skład klucza podstawowego muszą być zdefiniowane jako NOT NULL. Jeżeli nie są, automatycznie uzyskują to ustawienie.
  • W przypadku, gdy klucz podstawowy jest zdefiniowany na kolumnie typu zdefiniowanego przez użytkownika (CLR), implementacja tego typu musi obsługiwać porównywanie binarne.
  • Ograniczenia klucza obcego – podsumowanie

    Klucz obcy (FK) to kolumna lub zestaw kolumn, które służą do tworzenia połączeń między dwiema tabelami. Definiuje on reguły, według których dane mogą być przechowywane w tabeli z kluczem obcym.

    Odwołanie do klucza obcego oznacza utworzenie relacji między dwiema tabelami, gdzie kolumna lub zestaw kolumn w jednej tabeli (tabela potomna) odwołuje się do kolumny lub zestawu kolumn, które są kluczem podstawowym w innej tabeli (tabela macierzysta).

    W praktyce, może istnieć tabela Sales.SalesOrderHeader z kluczem obcym, który odwołuje się do tabeli *Sales.*Person. Powodem jest logiczna zależność pomiędzy sprzedawcami a zamówieniami sprzedaży.

    W tym przykładzie, kolumna SalesPersonID w tabeli SalesOrderHeader jest powiązana z kolumną, która jest kluczem podstawowym tabeli SalesPerson. Oznacza to, że SalesPersonID w SalesOrderHeader jest kluczem obcym.

    Tak zdefiniowana relacja określa zasadę: wartość SalesPersonID w tabeli SalesOrderHeader musi istnieć w tabeli SalesPerson.

    Jedna tabela może zawierać odwołania do maksymalnie 253 kolumn i tabel jako klucze obce (odwołania wychodzące). Od 2016 roku serwer SQL zwiększył liczbę odwołań, które można kierować do pojedynczej tabeli (odwołania przychodzące), z 253 do 10 000. Jednak to zwiększenie ma swoje ograniczenia:

  • Odwołania do klucza obcego powyżej 253 są dostępne tylko dla operacji DELETE DML. Operacje MERGE i UPDATE nie są obsługiwane.
  • Maksymalna liczba odwołań wychodzących dla jednej tabeli to 253.
  • W przypadku indeksów magazynu kolumn, tabel zoptymalizowanych pod kątem pamięci oraz tabel partycjonowanych, ograniczenie liczby odwołań do klucza obcego wynosi 253.
  • Jakie korzyści płyną z używania kluczy obcych?

    Ograniczenia klucza obcego pełnią fundamentalną rolę w ochronie integralności i spójności danych w relacyjnych bazach danych. Oto najważniejsze powody, dla których klucze obce są niezbędne:

  • Integralność referencyjna – klucze obce gwarantują, że każdy rekord w tabeli potomnej odpowiada rekordowi w tabeli macierzystej, zapewniając spójność danych w obu tabelach.
  • Zapobieganie osieroconym rekordom – ograniczenia klucza obcego uniemożliwiają wystąpienie sytuacji, w której tabela nadrzędna zostaje usunięta, podczas gdy powiązane z nią dane w tabeli podrzędnej pozostają bez odniesienia.
  • Poprawa wydajności – klucze obce pozwalają systemowi zarządzania bazą danych optymalizować zapytania na podstawie relacji między tabelami, co przekłada się na szybsze działanie.
  • Indeksy ograniczeń klucza obcego

    Ograniczenia klucza obcego, w przeciwieństwie do klucza podstawowego, nie powodują automatycznego utworzenia indeksów. Indeksy dla kluczy obcych można utworzyć ręcznie, co przynosi wymierne korzyści:

    • Kolumny klucza obcego są często wykorzystywane w warunkach łączenia danych z powiązanych tabel. Indeksy ułatwiają bazie danych wyszukiwanie powiązanych danych w tabeli obcej.
    • Zmiany w kluczu podstawowym są sprawdzane pod kątem ich wpływu na tabele powiązane kluczem obcym.

    Tworzenie indeksów nie jest obowiązkowe. Możliwe jest łączenie danych z dwóch tabel bez konieczności definiowania ograniczeń klucza podstawowego i obcego. Jednak dodanie ograniczeń klucza obcego optymalizuje tabele i przyspiesza wykonywanie zapytań. Zmiany w kluczu podstawowym są sprawdzane pod kątem spójności z kluczami obcymi w tabelach powiązanych.

    Wskazówki dotyczące tworzenia ograniczeń klucza obcego w SQL

    Przedyskutowaliśmy już teoretyczne aspekty kluczy obcych. Teraz przejdźmy do praktyki, czyli do tego jak tworzyć ograniczenia klucza obcego.

    Pole „Klucz obcy” w tabeli odnosi się do „Klucza podstawowego” w innej. Tabela, która zawiera klucz podstawowy, jest tabelą nadrzędną, a tabela zawierająca klucz obcy jest tabelą podrzędną. Przejdźmy do konkretów.

    Tworzenie klucza obcego podczas tworzenia tabeli

    Ograniczenie klucza obcego można utworzyć równocześnie z tworzeniem tabeli. Oto przykład:

    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 kluczem podstawowym „order_id” typu INT, kolumną „customer_id” typu INT i kolumną „order_date” typu DATE. Ograniczenie FOREIGN KEY jest dodawane do kolumny „customer_id” i odwołuje się do kolumny „customer_id” w tabeli „customers”.

    Tworzenie klucza obcego po utworzeniu tabeli

    Załóżmy, że tabela już istnieje i chcesz do niej dodać ograniczenie klucza obcego. W takim przypadku użyjemy instrukcji `ALTER TABLE`. Spójrz na poniższy przykład:

    ALTER TABLE orders
    ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

    W tym przykładzie dodajemy kolumnę „customer_id” z ograniczeniem klucza obcego do tabeli „orders”, która odwołuje się do kolumny „customer_id” w tabeli „customers”.

    Tworzenie klucza obcego bez sprawdzania istniejących danych

    Standardowo, po dodaniu ograniczenia klucza obcego, baza danych automatycznie sprawdza istniejące dane pod kątem zgodności z tym ograniczeniem. Jeśli jesteś pewien, że dane są spójne, i chcesz dodać ograniczenie bez tego sprawdzenia, możesz to zrobić w następujący sposób:

    ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    NOT VALIDATE;

    Klauzula `NOT VALIDATE` informuje bazę danych, aby pominęła sprawdzanie istniejących danych. Jest to przydatne w sytuacjach, gdy mamy do czynienia z ogromną ilością danych, a sam proces sprawdzania poprawności jest zbyt czasochłonny.

    Tworzenie klucza obcego za pomocą DELETE/UPDATE

    Podczas definiowania klucza obcego można określić, jakie działania mają być podejmowane w przypadku aktualizacji lub usunięcia wiersza, do którego ten klucz się odwołuje. W tym celu używa się kaskadowych ograniczeń integralności referencyjnej. Najważniejsze opcje to:

    # 1. NO ACTION

    Opcja `NO ACTION` to domyślne zachowanie przy tworzeniu ograniczenia klucza obcego. Oznacza to, że jeśli spróbujemy usunąć lub zaktualizować wiersz, do którego się odwołujemy, żadna akcja nie zostanie podjęta.

    Silnik bazy danych zwróci błąd informujący o naruszeniu ograniczenia klucza obcego. Chociaż jest to domyślne zachowanie, nie jest ono zalecane, ponieważ może prowadzić do problemów z integralnością referencyjną. Oto przykład:

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

    Opcja `CASCADE` umożliwia automatyczne aktualizowanie lub usuwanie wierszy w tabeli podrzędnej w przypadku zmiany w tabeli nadrzędnej. Jest to potężna opcja w kontekście zachowania integralności referencyjnej. Poniżej znajduje się przykład:

    ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

    Używając `CASCADE`, należy zachować szczególną ostrożność, ponieważ nieumiejętne użycie tej opcji może prowadzić do przypadkowej utraty dużej ilości danych lub do powstania cyklicznych odwołań. Z tego powodu należy używać tej opcji tylko wtedy, gdy jest to absolutnie konieczne.

    Istnieje kilka zasad dotyczących użycia `CASCADE`:

    • Nie można określić CASCADE, jeśli kolumna znacznika czasu jest częścią klucza obcego lub odniesienia.
    • Nie można użyć opcji `ON DELETE CASCADE`, jeśli tabela posiada wyzwalacz INSTEAD OF DELETE.
    • Nie można określić `ON UPDATE CASCADE`, jeśli tabela posiada wyzwalacz INSTEAD OF UPDATE.

    #3. SET NULL

    W przypadku ustawienia opcji `SET NULL`, wartości klucza obcego zostaną ustawione na wartość NULL, w przypadku usunięcia lub aktualizacji odpowiedniego wiersza w tabeli macierzystej. Aby ta opcja mogła działać, kolumny klucza obcego muszą dopuszczać wartość NULL. Ta opcja nie jest dostępna w przypadku tabel z wyzwalaczami INSTEAD OF UPDATE. Poniżej przykład:

    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 przykładzie ustawiamy kolumnę „customer_id” w tabeli „orders” na wartość NULL, gdy odpowiedni wiersz w tabeli „customers” zostanie usunięty lub zaktualizowany.

    #4. SET DEFAULT

    Opcja `SET DEFAULT` ustawia wszystkie wartości klucza obcego na wartość domyślną, gdy wiersz, do którego się odwołują, zostaje usunięty lub zaktualizowany.
    Ograniczenie to działa, jeśli wszystkie kolumny wchodzące w skład klucza obcego mają zdefiniowane wartości domyślne. Jeśli kolumna dopuszcza wartość NULL, domyślną wartością będzie NULL. Tej opcji nie można użyć w przypadku 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 przykładzie, ustawiamy kolumnę „customer_id” w tabeli „orders” na jej wartość domyślną, gdy odpowiedni wiersz w tabeli „customers” zostanie usunięty lub zaktualizowany.

    Podsumowanie

    W tym artykule odświeżyłeś sobie wiedzę na temat ograniczeń klucza podstawowego i zgłębiłeś tajniki klucza obcego. Przedstawiliśmy także różne metody tworzenia kluczy obcych. Mimo istnienia wielu sposobów na tworzenie kluczy obcych, ten artykuł skupił się na najważniejszych metodach.

    Mamy nadzieję, że zapoznałeś się z nowymi technikami. Warto pamiętać, że nie jesteś ograniczony do korzystania z nich pojedynczo. Metody takie jak `CASCADE`, `SET NULL`, `SET DEFAULT` i `NO ACTION` można łączyć w tabelach, które tworzą relacje referencyjne.

    W przypadku, gdy tabela napotka ograniczenie `NO ACTION`, przejdzie do kolejnych reguł ograniczeń. W innych przypadkach akcja `DELETE` może wywołać kombinację tych reguł, a ograniczenie `NO ACTION` zostanie uruchomione na końcu.

    Na koniec zachęcamy do zapoznania się ze ściągawką SQL.


    newsblog.pl