Ponieważ bazy danych przechowują dane w sposób trwały to tabele w bazie danych potrzebują sposobów na egzekwowanie różnych typów walidacji danych niezależnie od zmian jakie mogą zachodzić w źródłach zewnętrznych. Rodzaje walidacji wykraczają poza sprawdzanie typu danych, obejmują one unikalność kolumn, zakres wartości jakie można wstawić do kolumny oraz kiedy wartość w kolumnie musi odpowiadać wartości w innej tabeli. Sprawdzanie takiej poprawności danych wewnątrz definicji samej tabeli nazywa się deklarowaniem integralności danych. Integralność jest realizowana za pomocą ograniczeń tabeli i standardowych poleceń ISO do tworzenia tych ograniczeń.
Użycie ograniczeń
Najlepszym sposobem do egzekwowania integralności danych w SQL Serwerze jest deklaracja ograniczeń na tabelach. Stosuje się je przy użyciu komend CREATE TABLE i ALTER TABLE. Wszystkie ograniczenia w SQL Serwerze są obiektami bazy danych, tak jak tabele, widoki, procedury składowane itp. Dlatego ograniczenia muszą mieć unikalne nazwy dla całej bazy danych. Ponieważ ograniczenia dotyczą pojedynczych tabel to sensowne jest użycie konwencji nazewnictwa które zawiera rodzaj ograniczenia, nazwę tabeli i użyte kolumny. Np tabela Production.Categories posiada klucz główny nazwany PK_Categories.
Klucz główny (Primary Key Constraints)
Każda tabela w tabeli relacyjnej powinna posiadać sposób na odróżnienie każdego wiersza. Najczęściej stosowanym sposobem jest dodanie kolumny z unikalną wartością dla każdego wiersza jako klucza głównego. Czasami trzeba użyć kombinacji kilku kolumn. Kolumny które identyfikują każdy wiersz nazywane są kluczami naturalnymi lub biznesowymi. Można użyć takich kolumn jako klucza głównego, lecz projektanci baz danych najczęściej tworzą dodatkową kolumnę z typem numerycznym(np int), która ma unikalną wartość nie posiadającą żadnego znaczenia biznesowego zwaną kluczem zastępczym. Klucz zastępczy służy jako klucz główny i naturalny z unikalnością wartości egzekwowaną przez ograniczenie UNIQUE. Rozpatrzmy przykład tabeli Production.Categories:
CREATE TABLE Production.Categories ( categoryid INT NOT NULL IDENTITY, categoryname NVARCHAR(15) NOT NULL, description NVARCHAR(200) NOT NULL, CONSTRAINT PK_Categories PRIMARY KEY(categoryid) );
W tej tabeli kluczem głównym jest kolumna categoryid ponieważ na końcu deklaracji dodane zostało ograniczenie PK_Categories. Innym sposobem na deklarację klucza głównego jest użycie ALTER TABLE:
ALTER TABLE Production.Categories ADD CONSTRAINT PK_Categories PRIMARY KEY(categoryid); GO
Kolumny które są kluczami głównymi są używane w innych tabelach jako klucze obce służące do odniesienia się do tej tabeli. Dobrą praktyką jest nazywanie tych kolumn w taki sam sposób w obu tabelach, jeżeli to możliwe. Można to zrealizować nazywając kolumny klucza głównego imieniem pochodzącym od nazwy tabeli. Dzięki temu można łatwo rozpoznać z którą tabelą łączy dany klucz obcy. Wymagania dla kolumny klucza głównego:
- Nie może zezwalać na wartość NULL.
- Jeżeli są już wartości w tych kolumnach to muszą być unikalne, inaczej ALTER TABLE zgłosi błąd.
- W jednej tabeli może istnieć tylko jeden klucz główny.
Tworząc klucz główny nie trzeba nadawać ograniczenia unikalności, SQL Serwer zrobi to sam. Listę kluczy głównych w tabeli można przejrzeć w widoku systemowym sys.key_constraints filtrując go dla typu PK.
SELECT * FROM sys.key_constraints WHERE type = 'PK';
Można również znaleźć indeksy unikalne odpytując widok sys.indexes. Np zapytanie pokazujące indeksy unikalne dla tabeli Production.Categories:
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('Production.Categories') AND name = 'PK_Categories';
Ograniczenie unikalności(UNIQUE CONSTRAINTS)
Ograniczenie UNIQUE jest bardzo podobne do klucza głównego. Dla kluczy naturalnych i biznesowych egzekwuje się unikalność. Np w tabeli Production.Categories chcemy wyegzekwować aby wszystkie nazwy kategorii były unikalne:
ALTER TABLE Production.Categories ADD CONSTRAINT UC_Categories UNIQUE (categoryname); GO
Tak samo jak klucz główny, ograniczenie UNIQUE automatycznie tworzy unikalny indeks z taką samą nazwą jak ograniczenie. Domyślnie indeks ten jest nieklajstrowany. Ograniczenie UNIQUE pozwala na wartość NULL lecz tylko jeden wiersz może mieć taką wartość dla unikalnej kolumny. Klucz główny i ograniczenie unikalności mogą być założone na maksymalnie 16 kolumnach z ograniczeniem maksymalnie 900 bajtów zajętości przez te kolumny. Listę kluczy unikalnych można otzrymać odpytując widok systemowy sys.key_constraints:
SELECT * FROM sys.key_constraints WHERE type = 'UQ';
Klucz obcy
Klucz obcy jest kolumną lub kombinacją kolumn w jednej tabeli które są odnośnikiem do danych w innej tabeli relacyjnej. W drugiej tabeli ta kolumna lub te kolumny są kluczem głównym lub ograniczeniem typu UNIQUE. Wartości w kluczu obcym nie muszą być unikalne, może istnieć więcej niż jeden odnośnik do jednego wiersza w tablei głównej (klucza głównego). Jako przykład może posłużyć tabela Production.Products. Kolumna categoryid jest kluczem obcym odwołującym się do tabeli Production.Categories. Czyli dla każdego produktu możemy odnaleźć odpowiadającą mu kategorię. Oto kod tworzący klucz obcy:
ALTER TABLE Production.Products WITH CHECK ADD CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid) REFERENCES Production.Categories (categoryid) GO
Działanie komendy:
- Zawsze deklarujemy klucz obcy dla tabeli w której ma on powstać, dlatego zmieniamy definicję tabeli Production.Products.
- Można zdecydować czy podczas tworzenia może zostać naruszone ograniczenie poprzez dodanie lub pominięcie klauzuli WITH CHECK.
- Dodając ograniczenie klucza obcego nadajemy mu nazwę.
- Po wybraniu typu ograniczenia wybieramy w nawiasie () kolumnę lub kolumny które chcemy łączyć do tabeli głównej.
- Za pomocą słowa kluczowego REFERENCES wybieramy tabelę główną do jakiej chcemy się łączyć oraz w nawiasie kolumny klucza głównego lub unikalnego które mają być połączone.
Reguły dotyczące tworzenia kluczy obcych:
- Ilość kolumn, typy tych kolumn i COLLATION jeżeli są ciągi znaków muszą być identyczne dla obu łączonych tabel.
- Kolumny tabeli głównej z którą łączy się klucz obcy muszą mieć ograniczenie UNIQUE.
- Można tworzyć klucz obcy na kolumnach wyliczanych (computed columns).
Tabele bardzo często łączy się w zapytaniach na podstawie kluczy obcych. Np poniższe zapytanie zwróci nazwy kategorii dla zbioru produktów z tabeli Production.Products:
SELECT P.productname, C.categoryname FROM Production.Products AS P JOIN Production.Categories AS C ON P.categoryid = C.categoryid;
Ponieważ złączenia często występują na kolumnach klucza obcego, to pomocne dla wydajności zapytań może być załorzenie nieklajstrowanego indeksu na kolumnach tego klucza. W tabeli głównej istnieje już przynajmniej indeks unikalny. Dla tabel z wieloma wierszami założenie takiego indeksu może zwiększyć wydajność zapytań. Klucze obce bazy danych możemy podejrzeć w widoku sys.foreign_keys:
SELECT * FROM sys.foreign_keys WHERE name = 'FK_Products_Categories';
Ograniczenia typu CHECK
Używając ograniczeń sprawdzających można zadeklarować dopuszczalne wartości kolumn. Wartości są już ograniczone typem danych lecz ograniczenie CHECK dodaje bardziej szczegółowe sprawdzanie. Definiując to ograniczenie używamy wyrażenia instruującego SQL Serwer o dopuszczalnych wartościach. Wyrażenie może używać innych kolumn w tym samym wierszu oraz używać wbudowanych funkcji T-SQL. Przykładowo chcemy aby kolumna ceny jednostkowej w tabeli Production.Products była większa od zera:
ALTER TABLE Production.Products WITH CHECK ADD CONSTRAINT CHK_Products_unitprice CHECK (unitprice>=0); GO
Dodawanie ograniczeń sprawdzających ma wiele zalet:
- Wyrażenia w tych ograniczeniach są podobne do filtrów w klauzuli WHERE zapytania SELECT.
- Ograniczenia te są nałożone na całą tabelę więc będą zawsze wymagane.
Na co należy zwrócić uwagę przy ograniczeniach CHECK:
- Jeżeli kolumna zezwala na NULL, to uwzględnij to w wyrażeniu. NULL przejdzie bez błędu przez sprawdzanie unitprice>=0.
- Nie można dostosować informacji o błędzie przy ograniczeniach o błędzie.
- Ograniczenie sprawdzające nie może odwoływać się do poprzedniej swojej wartości przy aktualizacji. Np gdy chcielibyśmy aby wartość kolumny nie mogła wzrosnąć o więcej niż 20% to musielibyśmy użyć wyzwalacza.
Listę ograniczeń typu CHECK można przejrzeć odpytując widok systemowy sys.check_constraints:
SELECT *
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID('Production.Products');
Ograniczenie typu DEFAULT
Domyślna wartość dla kolumny jest najbardziej użyteczna gdy kolumna nie zezwala na NULL i chcemy zapobiec błędnemu działaniu komendy INSERT która nie uwzględnia wstawienia wszystkich kolumn. Przykładowo dla kolumny ceny jednostkowej tabeli Production.Products zdefiniujmy domyślną wartość na 0.
CREATE TABLE Production.Products ( productid INT NOT NULL IDENTITY, productname NVARCHAR(40) NOT NULL, supplierid INT NOT NULL, categoryid INT NOT NULL, unitprice MONEY NOT NULL CONSTRAINT DFT_Products_unitprice DEFAULT(0), discontinued BIT NOT NULL CONSTRAINT DFT_Products_discontinued DEFAULT(0), --… );
W tym wypadku ograniczenie jest wymienione po typie danych kolumny. Posiada ono nadaną bezpośrednio nazwę. Jeżeli nie określimy nazwy bezpośrednio to SQL Serwer nada automatycznie unikalną. Listę wartości domyślnych możemy przejrzeć odpytując widok systemowy sys.default_constraints:
SELECT *
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('Production.Products');
Ćwiczenia
I. Praca z kluczami głównymi i obcymi
- Zbadajmy skrypt tworzenia tabeli Production.Products.
/* -- Create table Production.Products CREATE TABLE Production.Products ( productid INT NOT NULL IDENTITY, productname NVARCHAR(40) NOT NULL, supplierid INT NOT NULL, categoryid INT NOT NULL, unitprice MONEY NOT NULL CONSTRAINT DFT_Products_unitprice DEFAULT(0), discontinued BIT NOT NULL CONSTRAINT DFT_Products_discontinued DEFAULT(0), CONSTRAINT PK_Products PRIMARY KEY(productid), CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid) REFERENCES Production.Categories(categoryid), CONSTRAINT FK_Products_Suppliers FOREIGN KEY(supplierid) REFERENCES Production.Suppliers(supplierid), CONSTRAINT CHK_Products_unitprice CHECK(unitprice >= 0) ); */
- Przetestuj klucz główny używając zapytania:
SELECT productname FROM Production.Products WHERE productid = 1; SET IDENTITY_INSERT Production.Products ON; GO INSERT INTO Production.Products (productid, productname, supplierid, categoryid, unitprice, discontinued) VALUES (1, N'Product TEST', 1, 1, 18, 0); GO SET IDENTITY_INSERT Production.Products OFF;
- Wstaw nowy wiersz do tabeli tak aby IDENTITY wstawiło nową wartość:
INSERT INTO Production.Products (productname, supplierid, categoryid, unitprice, discontinued) VALUES (N'Product TEST', 1, 1, 18, 0); GO
- Usuń testowy wiersz:
DELETE FROM Production.Products WHERE productname = N'Product TEST'; GO
- Usuń klucz obcy FK_Products_Categories.
ALTER TABLE Production.Products DROP CONSTRAINT FK_Products_Categories; GO
- Dodaj klucz obcy z powrotem.
ALTER TABLE Production.Products WITH CHECK ADD CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid) REFERENCES Production.Categories (categoryid); GO
- Usuń testową kolumnę.
DELETE FROM Production.Products WHERE productname = N'Product TEST'; GO
II. Tworzenie ograniczeń UNIQUE
- Sprawdź czy wszystkie nazwy produktów w tabeli Production.Products są unialne:
SELECT productname, COUNT(*) AS productnamecount FROM Production.Products GROUP BY productname HAVING COUNT(*) > 1;
- Dodaj ograniczenie unikalności do kolumny productname.
ALTER TABLE Production.Products ADD CONSTRAINT U_Productname UNIQUE (productname);
- Usuń ograniczenie.
ALTER TABLE Production.Products DROP CONSTRAINT U_Productname;
Podsumowanie
- Aby pomóc zachować integralność danych w bazie danych deklarujemy ograniczenia które są zapisane w bazie.
- Ograniczenia zapewniają, że dane wprowadzone do tabeli muszą przestrzegać bardziej zaawansowanych zasad niż wskazuje na to typ danych i możliwość wprowadzania NULL.
- Ograniczenia tabeli zawierają klucze główne i ograniczenia unikalności dla których SQL Serwer definiuje unikalny indeks.
- Ograniczenia tabeli zawierają również klucze obce, ograniczenia sprawdzania CHECK CONSTRAINTSi wartości domyślne.