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ć:
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:
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:
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