Zgodność danych w SQL Server – Constraint, Default, Null

Integralność danych w SQL Server

Czym są Constraint’y w tabelach? Są to obostrzenia założone na poszczególne kolumny tabel. Są szczególnie ważne podczas tworzenia zaawansowanych aplikacji opartych na wszystkich relacyjnych bazach danych. Za ich pomocą określamy na poszczególnych kolumnach tabel, jakie dane mogą być składowane w kolumnie, a jakie nie mogą. Dodatkowo za ich pomocą przypisujemy klucze główne i obce w poszczególnych kolumnach tabel. W SQL Server wyróżniamy następujące Constraint’y i obostrzenia, które możemy założyć na tabelę:

• PRIMARY KEY – Klucz główny tabeli. W jednej tabeli może występować tylko jeden klucz główny. Klucz główny musi być wartością unikatową. Klucz główny nie może zawierać wartości NULL. Podczas zakładania klucza głównego automatycznie tworzony jest indeks klastrowy dla danej kolumny, a dane sortowane są rosnąco.
• UNIQUE – Unikatowa wartość w kolumnie. W tabeli możemy wstawić ustawić więcej niż jedną kolumnę z takim Constraint’em. Kolumna z UNIQUE może zawierać jeden rekord z wartością NULL. Jeśli chcemy tego uniknąć, można zastosować dodatkowo NOT NULL dla kolumny.
• FOREIGN KEY – Klucz obcy. Wartość odnosi się do id w innej tabeli. Stosujemy go do tworzenia relacji pomiędzy tabelami. Klucz obcy musi mieć odniesienie do klucza tabeli obcej.
• CHECK – Walidacja wartości wprowadzanej do tabeli. Check służy do walidacji danych wprowadzanych do kolumny Wprowadzamy w nim wyrażenie logiczne określające kryteria danych, które będą wprowadzane do kolumn. Możemy np określić, że kolumna ma przechowywać wartości od 1 do 1000 itp.
• DEFAULT – Wartość domyślna wstawiana w kolumnie tabeli. Wprowadza się, gdy wstawiamy nowy rekord i nie określimy, jaką konkretną wartość ma uzyskać dana kolumna. W tym wypadku automatycznie wstawia się wartość Default. Może to być np bieżąca data.
• NULL / NOT NULL – Warto znać te obostrzenie. Uniemożliwia lub dopuszcza wartość NULL w danej kolumnie tabeli.

2. Tworzenie tabeli – wstawianie Constraintów

W jaki sposób tworzyć tabele w SQL Server, by kolumny zawierały wybrane constraint’y? Poniżej skrócony schemat tworzenia tabeli w SQL Server. Jak widzisz, po nazwie kolumny i typie danych wstawiamy wyrażenie CONSTRAINT oraz naszą nazwę własną. Następnie wprowadzamy typ, a więc PRIMARY KEY, UNIQUE, CHECK itp. Na koniec wprowadzamy właściwości, które możemy ustalić dla wybranych kolumn. Ważne jest, że wcale nie musisz wprowadzać wyrażenia CONSTRAINT, ani jego nazwy. W tym wypadku SQL Server sam wybierze taką nazwę. Nie jest to jednak zalecane. Nazwa utworzona w ten sposób może być ciężka do zapamiętania jak np PK__TBL_USER__3214EC27E2BD78DE.

--tomaszkenig.pl
CREATE TABLE NAZWA_TABELI
(NAZWA_KOLUMNY TYP_DANYCH CONSTRAINT TWOJA_NAZWA_CONSTRAINTU WYBRANY_CONSTRAINT WŁAŚCIWOŚCI/OPCJE CONSTRAINTU
);
-- PRZYKŁAD
CREATE TABLE NAZWA_TABELI
(ID BIGINT CONSTRAINT cst_prm_key PRIMARY KEY,
ROK INT CONSTRAINT cst_rok CHECK (ROK BETWEEN 1900 AND 2020)
);

Przykład tworzenia tabeli z Constraintami

Stwórzmy krótki przykład. Utwórz dwie tabele TBL_USERS I TBL_USERS_DATA z wykorzystaniem poznanych obostrzeń tabel.

--tomaszkenig.pl
CREATE TABLE TBL_USERS
(
ID_TB_1 BIGINT CONSTRAINT cst_tbl_users_prmkey PRIMARY KEY,
LOGIN_NAME NVARCHAR(50) CONSTRAINT cst_tbl_users_unique_login UNIQUE NOT NULL,
FULL_NAME NVARCHAR(100) NOT NULL,
INSERT_DATE DATETIME2 CONSTRAINT cst_tbl_users_insdt_default DEFAULT GETDATE()
);
GO

CREATE TABLE TBL_USERS_DATA
(ID_TB_2 BIGINT CONSTRAINT cst_tbl_users_data_prmkey PRIMARY KEY,
ID_TBL_USERS BIGINT CONSTRAINT cst_tbl_users_data_forkey FOREIGN KEY REFERENCES TBL_USERS(ID_TB_1),
AGE INT CONSTRAINT cst_tbl_users_data_age_check CHECK (AGE BETWEEN 0 AND 100),
GENDER NVARCHAR(10) CONSTRAINT cst_tbl_users_gender_check CHECK ( GENDER IN ('man','women')),
INSERT_DATE DATETIME2 NOT NULL DEFAULT GETDATE()
);
GO

Jak widzisz obie tabele zawierają klucze główne. Tabela TBL_USERS_DATA ma też klucz obcy odnoszący się do TBL_USERS. W tabeli TBL_USERS dwie z kolumn posiadają walidację wartości utworzoną za pomocą CHECK. Wstawmy teraz do tabel przykładowe dane:

--tomaszkenig.pl
INSERT INTO TBL_USERS (ID_TB_1, LOGIN_NAME, FULL_NAME) VALUES (1,'sjobs','Steave Jobs');
INSERT INTO TBL_USERS (ID_TB_1, LOGIN_NAME, FULL_NAME) VALUES (2,'bgates','Bill Gates');
INSERT INTO TBL_USERS (ID_TB_1, LOGIN_NAME, FULL_NAME) VALUES (3,'mzuckerberg','Mark Zuckerberg');
INSERT INTO TBL_USERS (ID_TB_1, LOGIN_NAME, FULL_NAME) VALUES (4,'panderson','Pamela Anderson');

INSERT INTO TBL_USERS_DATA (ID_TB_2, ID_TBL_USERS, AGE, GENDER) VALUES (1, 1, 40, 'man');
INSERT INTO TBL_USERS_DATA (ID_TB_2, ID_TBL_USERS, AGE, GENDER) VALUES (2, 2, 40, 'man');
INSERT INTO TBL_USERS_DATA (ID_TB_2, ID_TBL_USERS, AGE, GENDER) VALUES (3, 3, 40, 'man');
INSERT INTO TBL_USERS_DATA (ID_TB_2, ID_TBL_USERS, AGE, GENDER) VALUES (4, 4, 40, 'women');
INSERT INTO TBL_USERS_DATA (ID_TB_2, ID_TBL_USERS, AGE, GENDER) VALUES (5, 4, 40, 'women');

Klucz obcy założony w tabeli TBL_USERS_DATA  uniemożliwia skasowanie każdego rekordu w tabeli TBL_USERS, jeśli powiązanie pomiędzy wstawionymi danymi istnieje. Nie możesz też skasować tabeli 1 (TBL_USERS) za pomocą polecenia DROP, zanim nie skasujesz tabeli TBL_USERS_DATA lub nie skasujesz z niej danych.

Dodawanie Constraintów do istniejących tabel

W jaki sposób założyć nowe constrainty na istniejących tabelach? Możesz to zrobić za pomocą polecenia ALTER TABLE. Wyjątek stanowi ustawianie dopuszczenia lub zakazu wartości NULL w tabeli. Stosujemy w tym przypadku składnię ALTER TABLE nazwa_tabeli ALTER COLUMN nazwa_kolumny typ_danych NULL/NOT NULL. Jeśli dodajemy do tabeli klucz główny PRIMARY KEY, kolumna musi być wcześniej ustawiona jako NOT NULL.  Poniżej przykład składni dla wszystkich obostrzeń.

--tomaszkenig.pl
CREATE TABLE TBL_USER_CITY
(ID_TB_3 BIGINT,
ID_TBL_USERS BIGINT,
CITY NVARCHAR(100), 
STREET_AND_HOUSE NVARCHAR(100) UNIQUE,
INSERT_DATE DATETIME2
);
GO

ALTER TABLE TBL_USER_CITY ADD CONSTRAINT cst_check1 CHECK (CITY IN ('New York', 'Los Angeles', 'Warsaw'));
GO
ALTER TABLE TBL_USER_CITY ADD CONSTRAINT cst_def1 DEFAULT GETDATE() FOR INSERT_DATE;
GO
ALTER TABLE TBL_USER_CITY ALTER COLUMN  ID_TB_3 BIGINT NOT NULL ;
GO
ALTER TABLE TBL_USER_CITY ADD CONSTRAINT cst_prm_key_id PRIMARY KEY (ID_TB_3) ;
GO
ALTER TABLE TBL_USER_CITY ADD CONSTRAINT cst_for_key_id FOREIGN KEY (ID_TBL_USERS) REFERENCES TBL_USERS(ID_TB_1); 
GO
ALTER TABLE TBL_USER_CITY ADD CONSTRAINT cst_unique1 UNIQUE (STREET_AND_HOUSE);
GO

Modyfikacja i usuwanie Constraintów

Jak modyfikować obostrzenia założone na kolumnach tabel? W SQL Server nie ma właściwej składni, za pomocą której mógłbyś zmodyfikować obostrzenia. Jeśli chcemy zmienić właściwości naszych kolumn powinniśmy usunąć obostrzenie za pomocą polecenia DROP. Następnie możemy założyć nowe o tej samej nazwie.

ALTER TABLE TBL_USER_CITY DROP CONSTRAINT cst_check1;
GO
ALTER TABLE TBL_USER_CITY ADD CONSTRAINT cst_check1 CHECK (CITY IN ('New York', 'Los Angeles', 'Warsaw', 'Madrid'));
GO

Bardziej szczegółowe opcje wszystkich poznanych obostrzeń poznasz w kolejnych rozdziałach kursu SQL Server. Jeśli masz pytania co do omawianych przykładów, zachęcam kontaktu ze mną.