Spis treści:

Kategoria:IndeksyOptymalizacja SQLSQL Server


Więzy unikalności (UNIQUE CONSTRAINT) w SQL Server

Jak założyć więzy unikalności (UNIQUE CONSTRAINT)

Zdaję sobie sprawę, że większość osób nie jest zainteresowanych konsekwencjami założenia takich więzów, ich możliwości, różnicy pomiędzy takim więzem a kluczem głównym, potencjalnymi możliwościami występowania jako klucz obcy i paru innych właściwości. Większość zwyczajnie szuka składni. To dlatego najpierw przedstawię kilka przykładów, a potem przejdę do szczegółów, które mogą być nieznane.

Przyjrzyjmy się zatem przykładowemu kluczowi unikalnemu:

CREATE TABLE UniqueTable
(
  ID int IDENTITY,
  TableCode varchar(20)
)

ALTER TABLE UniqueTable
ADD CONSTRAINT UQ_TableCode
UNIQUE (TableCode)

To oczywiście jeden ze sposobów, dość powzechnym zwyczajem jest zakładanie więzu unikalności podczas tworzenia tabeli (należy wcześniej usunąć poprzednio stworzoną tabelę UniqueTable). Ilość kodu będzie mniejsza, a sama deklaracja bardziej spójna:

CREATE TABLE UniqueTable
(
  ID int IDENTITY,
  TableCode varchar(20),
  CONSTRAINT UQ_TableCode
  UNIQUE (TableCode)
)

Więzy unikalności mogą obejmować wiele kolumn i jest do dość często praktykowane. Unikatowość jednej lub kilku kolumn jest pewnym wskazaniem - taka kolumna lub zestaw kolumn staje się naturalnym kandydatem na klucz główny i takie jest też zalecenie normalizacji. Pewne względy wydajnościowe, związane ze złożonością, być może z pracochłonnością rozwiązań mogą doprowadzać, i często doprowadzają, do utworzenia klucza sztucznego - IDENTITY. Aby utrzymać wszystko w ryzach, mam tu na myśli realną unikalność rozważanych kolumn, zakłada się, oprócz klucza głównego na IDENTITY, klucz unikatowy. Przyjrzyjmy się pokazanemu poniżej przykładowi:

CREATE TABLE Player
(
  ID int IDENTITY,
  TeamID int NOT NULL,
  Number int NOT NULL
)

ALTER TABLE Player
ADD CONSTRAINT UQ_PlayerTeamNumber
UNIQUE (TeamID, Number)

Oczywiście można zrobić wszystko podczas definiowania tabeli:

CREATE TABLE Player
(
  ID int IDENTITY,
  TeamID int NOT NULL,
  Number int NOT NULL,
  CONSTRAINT UQ_PlayerTeamNumber
  UNIQUE(TeamID, Number)
)

To są podstawowe sposoby zakładania więzów unikalności. Istnieje możliwość założenia indeksu unikalnego (więzy unikalności automatycznie zakładają indeks unikalny), ale nie będzie to dokładnie to samo. Takie drobne różnice wymagają pogłębionej analizy, dlatego zajmę się nimi oddzielnie.

Więzy unikalności a sprawa indeksu

Jedną z najważniejszych rzeczy dotyczących zakładania więzów unikalności jest pewien fakt, który należy zapamiętać: więzy unikalności zakładają unikalny indeks. Stwierdzenie pogrubiłem, bo jest niezwykle istotne. Indeks założy się automatycznie, otrzyma taką samą nazwę jak nazwa więzu, a my w konsekwencji otrzymamy wszystkie wady i zalety takiego unikalnego indeksu. To tak jak w małżeństwie - razem z żoną otrzymujemy teściową, z jej wszystkimi wadami i zaletami. Korzystając z wspomnianej właściwości można spróbować założyć zaprezentowane więzy w jeszcze inny sposób:

CREATE TABLE UniqueTable
(
  ID int IDENTITY,
  TableCode varchar(20)
)

CREATE UNIQUE INDEX UQ_TableCode
ON UniqueTable(TableCode)

Konstrukcja nieco inna, ale podstawową funkcję spełnia. Spróbujmy do unikalnej kolumny wstawić dwa takie same kody:

INSERT INTO UniqueTable VALUES
('Kod1'),('Kod1')
--Msg 2601, Level 14, State 1, Line 1
--Cannot insert duplicate key row in object 'dbo.UniqueTable' with unique index 'UQ_TableCode'. The duplicate key value is (Kod1).

Wstawienie się nie powiedzie - to jest to, o co nam przecież chodzi. Jest jednak drobna różnica. Tworzenie więzu integralności w sposób tradycyjny daje w efekcie parę obiektów - klucz unikatowy i indeks unikatowy. Tak jak klucz główny tworzy indeks, tak samo klucz unikatowy (unikatowe więzy integralności) tworzy unikatowy indeks. Popatrzmy na poniższe zapytanie, które pobiera informację o istnieniu bądź nieistnieniu klucza:

SELECT 'TAK' [Klucz istnieje]
WHERE EXISTS
(
  SELECTFROM sys.key_constraints
  WHERE name LIKE 'UQ_TableCode'
)

Gdy puścimy powyższe zapytanie na tabeli z unikatowym indeksem, otrzymamy taki rezultat:

Klucz istnieje

Brak wyników oznacza, że nie znaleziono żadnego wpisu w tabeli. Spróbujmy teraz usunąć indeks i założyć więzy integralności w sposób tradycyjny:

DROP INDEX UQ_TableCode
ON UniqueTable

ALTER TABLE UniqueTable
ADD CONSTRAINT UQ_TableCode
UNIQUE (TableCode)

SELECT 'TAK' [Klucz istnieje]
WHERE EXISTS
(
  SELECTFROM sys.key_constraints
  WHERE name LIKE 'UQ_TableCode'
)

Tym razem wynik zapytania będzie następujący:

Klucz istnieje
TAK

Tym razem indeks jest połączony z kluczem. Ta drobna różnica to kolejne konsekwencje.

Więzy unikalności a sprawa klucza obcego

Co ma piernik do wiatraka? Wyszliśmy od więzów unikalności a nagle przechodzimy do innej tabeli i relacji? Okazuje się, że nie jest to takie całkiem głupie. Po chwili zastanowienia się okazuje się nawet całkiem mądre! Popatrzmy na poniższy przykład:

CREATE TABLE UniqueTable
(
  ID int IDENTITY,
  TableCode varchar(20),
  CONSTRAINT UQ_TableCode
  UNIQUE (TableCode)
)

CREATE TABLE MyColumn
(
  Name varchar(30),
  TableCode varchar(20),
  CONSTRAINT FK_TableCode
  FOREIGN KEY (TableCode)
  REFERENCES UniqueTable(TableCode)
)

Klucze obce nie muszą być koniecznie powiązane z kluczem głównym innej tabeli. Doskonale nadają się do tego kolumny więzów unikalności. Pójdźmy jeszcze dalej i popatrzmy na kolejny przykład:

CREATE TABLE UniqueTable
(
  ID int IDENTITY,
  TableCode varchar(20)
)

CREATE UNIQUE INDEX UQ_TableCode
ON UniqueTable(TableCode)

CREATE TABLE MyColumn
(
  Name varchar(30),
  TableCode varchar(20),
  CONSTRAINT FK_TableCode
  FOREIGN KEY (TableCode)
  REFERENCES UniqueTable(TableCode)
)

Tutaj jest przykład czegoś jeszcze bardziej niezwykłego, bo okazuje się, że są to poprawne instrukcje. Przeglądając tabelę UniqueTable nie dostrzeżemy żadnego klucza! Do czego przypięty jest klucz obcy? Okazuje się, że do indeksu! To daje nam kolejne pole do popisu.

Można się spotkać z opinią, że zakładanie samego więzu unikalności nie pozwala na specyfikację wszystkich atrybutów indeksu. Jest to o tyle mylące, że taką informację można znaleźć na stronach dokumentacji SQL Server. Nie odsyłam do nich, bo są już mocno przestarzałe. Takie ograniczenie rzeczywiście było, ale począwszy od SQL Server 2005 można wzbogacić definicję więzu unikalności o wszystkie atrybuty, które związane są z indeksem. Poprawna zatem będzie taka oto definicja więzu:

ALTER TABLE UniqueTable
ADD CONSTRAINT UQ_TableCode
UNIQUE (TableCode)
WITH
(
  PAD_INDEX=ON,
  FILLFACTOR=90,
  DATA_COMPRESSION=PAGE
)

Tworzony w tle indeks otrzyma wszystkie pożądane atrybuty.

Fizyczne położenie wierszy

Więzy unikalności tworzą indeks. Jest on stosowany przede wszystkim do sprawnego badania, czy dana wartość nie istnieje jeszcze w tabeli (pisząc wartość mam na myśli jedną kolumnę lub zestaw kolumn, jeżeli więzy unikalności zbudowane są na kilku kolumnach). Nie jest to jednak jedyne zastosowanie tego indeksu - działa on dokładnie tak samo jak wszystkie inne, przez co zyskujemy możliwość wykorzystywania go w zapytaniach. W niektórych przypadkach rozsądne wydaje się takie poukładanie danych, aby ich porządek nie był zgodny z kluczem głównym. Najczęściej klucz główny tworzy indeks klastrowany (CLUSTERED) blokując w ten sposób możliwość innego poukładania wierszy - tylko jeden indeks może mieć atrybut CLUSTERED, bo tylko jeden może być porządek rekordów. Często też stosuje się klucz sztuczny, IDENTITY, a wyszukuje po wartościach klucza naturalnego. Klucz naturalny też jest unikalny.

W związku z powyższym warto rozważyć ustanowienie porządku względem klucza unikalnego, tak jak w poniższym przykładzie:

CREATE TABLE UniqueTable
(
  ID int IDENTITY,
  TableCode varchar(20),
  CONSTRAINT UQ_TableCode
  UNIQUE CLUSTERED (TableCode)
)

To, który indeks opatrzyć atrybutem CLUSTERED zależy od zapytań i tego, które kolumny są częściej wykorzystywane w tych zapytaniach (głównie w sekcji WHERE i predykatach złączeń). Dokładna analiza przypadków wykracza poza ten skromny wpis.

Indeksy filtrowane

W wersji SQL Server 2008 pojawiło się takie coś jak indeksy filtrowane. Pozwala to na stworzenie indeksu, który obejmuje tylko część wierszy, przefiltrowanych przez warunki sekcji WHERE. Można założyć taki oto indeks unikatowy:

CREATE UNIQUE INDEX UQ_TableCode
ON UniqueTable(TableCode)
WHERE TableCode IS NOT NULL

Do indeksu trafią tylko te kolumny, które mają wartość różną od NULL. Są i wady, i zalety tego typu rozwiązania. Z jednej strony indeks jest mniejszy, bo nie zawiera wszystkich kolumn, samo wyszukiwanie może być szybsze, bo mniej stron z danymi trzeba przeszukać. Poza tym otrzymujemy możliwość przechowywania wielu kopii rekordów, które mają w danej kolumnie wartość NULL. Takie nieco ciekawsze więzy: albo NULL (wiele razy), albo wartość jest unikatowa. Wada jest w zasadzie jedna - nie da się założyć na takim indeksie klucza obcego. Próba wykonania poniższego skryptu zakończy się błędem:

CREATE TABLE MyColumn
(
  Name varchar(30),
  TableCode varchar(20),
  CONSTRAINT FK_TableCode
  FOREIGN KEY (TableCode)
  REFERENCES UniqueTable(TableCode)
)
--Msg 1776, Level 16, State 0, Line 1
--There are no primary or candidate keys in the referenced table 'UniqueTable' that match the referencing column list in the foreign key 'FK_TableCode'.

Naturalnym ograniczeniem jest też brak możliwości użycia atrybutu CLUSTERED. To oczywiste, bo indeks CLUSTERED definiuje porządek wszystkich rekordów - nie tylko tych filtrowanych.

Podsumowanie i wnioski

Po przeanalizowaniu tekstu można sobie zadać pytanie: co stosować? Tradycyjne więzy unikalności? Może zwykły indeks unikalny? Różnice są i zostały, mam nadzieję, dobrze opisane. Nie lubię występować w charakterze wyroczni i wskazywać jedynie słuszne ścieżki postępowania. Osobiście, jeżeli nie mam zamiaru stosować indeksu filtrowanego, stosuję tradycyjną postać więzów unikalności. To, że w strukturach systemowych pojawi się jeden wpis więcej to nawet dobrze - wiadomo, że jest to klucz. Wpis ten nie ma jednak wpływu na wydajność. Sam indeks też zadziała, ale może wprowadzać w błąd. Ktoś, kto otwiera SQL Server Managemet Studio i przechodzi do sekcji kluczy niczego nie zobaczy. Może odczuć, jeżeli zostanie zaskoczony informacją o kluczach obcych wskazujących na tabelę... bez klucza? To takie drobiazgi mają wpływ na moje zdanie. Technicznie nie ma różnicy. Integralność będzie pilnowana w każdym przypadku.

Myślę, że temat więzów unikalności został wystarczająco opisany i nie będzie problemu ze świadomym ich użyciem. Przykłady mogą się wydać nierzeczywiste, ale chodziło głównie o prostotę. To więzy unikalności miały być na pierwszym miejscu. Celowo pominąłem między innymi definicje kluczy głównych, żeby nie wprowadzać niepotrzebnych konstrukcji. Gdyby coś było niejasne lub gdyby w tekście pojawił się jakiś błąd, zwłaszcza merytoryczny, zachęcam do skorzystania z komentarzy.

Kategoria:IndeksyOptymalizacja SQLSQL Server

, 2013-12-20

Komentarze:

Bogdan (2014-08-28 10:34:10)
Super artykuł, dzięki.
Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za rozjasnienie zagadnienia upsert. wlasnie sie ucze programowania :).
Co się stanie gdy spróbuję wyszukać:
SELECT * FROM NV_Airport WHERE Code='SVO'
SELECT * FROM V_Airport WHERE Code=N'SVO'
(odwrotnie są te N-ki)
Będzie konwersja czy nie znajdzie żadnego rekordu?