Jak sama nazwa wskazuje, constraint-y są ograniczeniami. Są regułami, które wymuszają pewne reguły na danych w kolumnach.
W SQL-u mamy następujące typy constraint-ów:
- NOT NULL - wymusza aby w kolumnie zawsze występowała jakaś wartość
- UNIQUE - wymaga, aby w kolumnie występowały tylko i wyłącznie wartości unikalne
- PRIMARY KEY - wymaga spełnienia następujących reguł:
- unikalność wartości w kolumnie (UNIQUE)
- w tabeli może być tylko jedna kolumna tego typu
- nie może zawierać wartości NULL
- aby klucz główny automatycznie posiadał wartość domyślną, trzeba przy jego definicji podać opcje auto increment (W PostgreSQL-u za ten mechanizm odpowiada seqwencja)
- FOREIGN KEY - Kolumna z tą opcją, wskazuje na klucz główny (PRIMARY KEY) w innej tabeli. Jeśli nie ma nałożonych innych ograniczeń, kolumna może zawierać wartości tylko, które będą odpowiadać wartością klucza głównego w innej tabeli, do której się odwołuje. Constraint może dotyczyć także grupy kolumn i odwoływać się do tej liczby i typów kolumn innej tabeli.
- CHECK - wymusza limitację wartości danych w kolumnie. Np. CHECK (c1 > 10) - tylko wartości większe od 10. Niestety tego typu ograniczenie nie jest wspierane przez bazę MySQL
- DEFAULT - jest używane do wstawianie wartości domyślnych. W poniższych przykładach zobaczycie, że wartość domyślna dla stringów jest wstawiana tylko wtedy, gdy kolumna jest pomijana.
- EXCLUDE (PostgreSQL) - gwarantuje, że 2 wiersze są porównywane na specjalnej kolumnie lub wyrażeniu, używające specjalnego operatora.
Przykład poniżej przestawia definicję i zawartość tabeli z kołami, gdzie można wstawić tylko i wyłącznie koła, które nie nachodzą na siebie:
postgres@test(127.0.0.1) # CREATE TABLE circles (
c circle,
EXCLUDE USING gist (c WITH &&)
);
postgres@test(127.0.0.1) # select * from circles;
c
---------------
<(0,2),1>
<(0,0),0.5>
<(0,-10),0.5>
<(-1,-1),0.5>
(4 rows)
postgres@test(127.0.0.1) # insert into circles (c) values (circle(point '(-2,-2)', 1));
ERROR: conflicting key value violates exclusion constraint "circles_c_excl"
DETAIL: Key (c)=(<(-2,-2),1>) conflicts with existing key (c)=(<(-1,-1),0.5>).
Przykłady
Przejdźmy do konkretnych przykładów, omówionych powyżej constraint-ów. Definicja tabela test3 zawierająca wszystkie typy constraint-ów (MySQL):
mysql (root@test)> create table test3 (c1 integer primary key, c2 varchar(255) default 'default text', c3 varchar(10) UNIQUE, c4 timestamp default CURRENT_TIMEstamp, c5 integer not null, c6 integer, check (c5 > 10), foreign key (c6) references test(c1));
Query OK, 0 rows affected (0.31 sec)
mysql (root@test)> show create table test3 \G
*************************** 1. row ***************************
Table: test3
Create Table: CREATE TABLE `test3` (
`c1` int(11) NOT NULL,
`c2` varchar(255) DEFAULT 'default text',
`c3` varchar(10) DEFAULT NULL,
`c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`c5` int(11) NOT NULL,
`c6` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `c3` (`c3`),
KEY `c6` (`c6`),
CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`c6`) REFERENCES `test` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
W tabeli test, używanej już innych postach, mamy 4 wiersze:
mysql (root@test)> select * from test;
+----+-------+---------------------+
| c1 | c2 | c3 |
+----+-------+---------------------+
| 2 | test | 2013-12-23 14:37:27 |
| 1 | test | 2013-12-23 14:37:21 |
| 3 | test | 2013-12-23 14:37:33 |
| 4 | TEST | 2013-12-23 14:37:39 |
+----+-------+---------------------+
4 rows in set (0.00 sec)
Wprowadźmy teraz kilka danych do nowej tabeli i zobaczmy co się stanie.
MySQL:
Próba wstawienie null pod klucz główny lub do kolumny z NOT NULL:
mysql (root@test)> insert into test3 (c1,c2,c3,c4,c5,c6) values (null,null, null, null, null, null);
ERROR 1048 (23000): Column 'c1' cannot be null
Error (Code 1048): Column 'c1' cannot be null
Error (Code 1105): Unknown error
mysql (root@test)> insert into test3 (c1,c2,c3,c4,c5,c6) values (1,null, null, null, null, null);
ERROR 1048 (23000): Column 'c5' cannot be null
Error (Code 1048): Column 'c5' cannot be null
Error (Code 1105): Unknown error
mysql (root@test)> insert into test3 (c1,c2,c3,c4,c5,c6) values (1,null, null, null, 1, null);
Query OK, 1 row affected (0.03 sec)
mysql (root@test)> insert into test3 (c1,c2,c3,c4,c5,c6) values (2,null, null, null, 1, 2);
Query OK, 1 row affected (0.03 sec)
Klucz główny wymaga unikalności:
mysql (root@test)> insert into test3 (c1,c2,c3,c4,c5,c6) values (2,null, null, null, 1, 2);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql (root@test)> insert into test3 (c1,c2,c3,c4,c5,c6) values (3,null, 3, null, 1, 2);
Query OK, 1 row affected (0.04 sec)
mysql (root@test)> insert into test3 (c1,c2,c3,c4,c5,c6) values (4,null, 3, null, 1, 2);
ERROR 1062 (23000): Duplicate entry '3' for key 'c3'
Dla klucza obcego (FOREIGN KEY), nie jest możliwe wstawienie wartości, które nie istnieje w kluczu głównym:
mysql (root@test)> insert into test3 (c1,c2,c3,c4,c5,c6) values (4,null, 4, null, 1, 6);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`test3`, CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`c6`) REFERENCES `test` (`c1`))
mysql (root@test)> insert into test3 (c1,c2,c3,c4,c5,c6) values (4,null, 4, null, 1, 4);
Query OK, 1 row affected (0.03 sec)
mysql (root@test)> insert into test3 (c1,c3,c4,c5,c6) values (5,5, null, 1, 4);
Query OK, 1 row affected (0.05 sec)
MySQL nie wspiera constarint-a typu CHECK dlatego w kolumnie c5 widzimy 'niedozwolone' wartości:
mysql (root@test)> select * from test3;
+----+--------------+------+---------------------+----+------+
| c1 | c2 | c3 | c4 | c5 | c6 |
+----+--------------+------+---------------------+----+------+
| 1 | NULL | NULL | 2014-01-13 12:03:57 | 1 | NULL |
| 2 | NULL | NULL | 2014-01-13 12:04:26 | 1 | 2 |
| 3 | NULL | 3 | 2014-01-13 12:05:08 | 1 | 2 |
| 4 | NULL | 4 | 2014-01-13 12:05:33 | 1 | 4 |
| 5 | default text | 5 | 2014-01-13 12:11:31 | 1 | 4 |
+----+--------------+------+---------------------+----+------+
5 rows in set (0.00 sec)
PostgreSQL v9.1:
postgres@test(localhost) # CREATE TABLE test3 (
c1 integer NOT NULL,
c2 varchar(255) DEFAULT 'default text',
c3 varchar(10) DEFAULT NULL,
c4 timestamp NOT NULL DEFAULT now(),
c5 integer NOT NULL,
c6 integer DEFAULT NULL,
PRIMARY KEY (c1),
UNIQUE (c3), CHECK (c5 > 10),
FOREIGN KEY (c6) REFERENCES test (c1)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test3_pkey" for table "test3"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "test3_c3_key" for table "test3"
CREATE TABLE
Próba wstawienia NULL dla klucza głównego lub kolumny z constraint-em NOT NULL:
postgres@test(localhost) # insert into test3 (c1,c2,c3,c4,c5,c6) values (null,null, null, null, null, null);
ERROR: null value in column "c1" violates not-null constraint
postgres@test(localhost) # insert into test3 (c1,c2,c3,c4,c5,c6) values (1,null, null, null, null, null);
ERROR: null value in column "c4" violates not-null constraint
postgres@test(localhost) # insert into test3 (c1,c2,c3,c4,c5,c6) values (1,null, null, null, 1, null);
ERROR: null value in column "c4" violates not-null constraint
PostgreSQL wspiera constraint CHECK. Dla kolumny c5 tylko wartości powyżej 10 są przymowane:
postgres@test(localhost) # insert into test3 (c1,c2,c3,c5,c6) values (1,null, null, 1, null);
ERROR: new row for relation "test3" violates check constraint "test3_c5_check"
postgres@test(localhost) # insert into test3 (c1,c2,c3,c5,c6) values (1,null, null, 10, null);
ERROR: new row for relation "test3" violates check constraint "test3_c5_check"
postgres@test(localhost) # insert into test3 (c1,c2,c3,c5,c6) values (1,null, null, 11, null);
INSERT 0 1
postgres@test(localhost) # insert into test3 (c1,c2,c3,c5,c6) values (2,null, null, 11, 2);
INSERT 0 1
Dla klucza głównego wartość 2 już istaniała:
postgres@test(localhost) # insert into test3 (c1,c2,c3,c5,c6) values (2,null, null, 11, 2);
ERROR: duplicate key value violates unique constraint "test3_pkey"
DETAIL: Key (c1)=(2) already exists.
postgres@test(localhost) # insert into test3 (c1,c2,c3,c5,c6) values (3,null, 3, 11, 2);
INSERT 0 1
Próba wstawienia istniejącej wartości do kolumny, na której nałożony został UNIQUE constraint:
postgres@test(localhost) # insert into test3 (c1,c2,c3,c5,c6) values (4,null, 3, 11, 2);
ERROR: duplicate key value violates unique constraint "test3_c3_key"
DETAIL: Key (c3)=(3) already exists.
Próba wstawienia wartości dla klucza obcego, która nie istnieje w kluczu głównym:
postgres@test(localhost) # insert into test3 (c1,c3,c5,c6) values (6, 5, 11, 6);
ERROR: insert or update on table "test3" violates foreign key constraint "test3_c6_fkey"
DETAIL: Key (c6)=(6) is not present in table "test".
postgres@test(localhost) # insert into test3 (c1,c3,c5,c6) values (6, 5, 11, 3);
INSERT 0 1
Wynik naszych insertów:
postgres@test(localhost) # select * from test3;
c1 | c2 | c3 | c4 | c5 | c6
----+--------------+----+----------------------------+----+----
1 | | | 2014-01-13 12:27:04.033428 | 11 |
2 | | | 2014-01-13 12:27:30.215729 | 11 | 2
3 | | 3 | 2014-01-13 12:28:14.591692 | 11 | 2
6 | default text | 5 | 2014-01-13 12:29:27.55217 | 11 | 3
(4 rows)
FOREIGN KEY
Co się stanie gdy będziemy chcieli usunąć z tabeli test wiersz? Przecież w tabeli test3 mamy klucze obce to tych wartości. Odpowiedź brzmi: NIC. Baza danych odmówi wykonania operacji ze względu na te połączenia (akurat w naszym przypadku):
mysql (root@test)> delete from test where c1 = 4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`test3`, CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`c6`) REFERENCES `test` (`c1`))
postgres@test(localhost) # delete from test where c1 = 4;
ERROR: update or delete on table "test" violates foreign key constraint "test3_c6_fkey" on table "test3"
DETAIL: Key (c1)=(4) is still referenced from table "test3".
Aby ułatwić nam pracę, constraint FOREIGN KEY ma kilka opcji, które teraz użyjemy. Dla zobrazowania problemu, stworzyłam 3 tabele i wprowadziłam do nich dane.
mysql (root@test)> create table t1 (c1 integer primary key, c2 varchar(10));
Query OK, 0 rows affected (0.26 sec)
mysql (root@test)> create table t2 (c1 integer primary key, c2 varchar(10));
Query OK, 0 rows affected (0.23 sec)
mysql (root@test)> create table t3 (c1 integer, c2 integer, foreign key (c1) references t1(c1) ON DELETE RESTRICT, foreign key (c2) references t2(c1) ON DELETE CASCADE);
Query OK, 0 rows affected (0.37 sec)
mysql (root@test)> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | |
| 2 | |
+----+------+
2 rows in set (0.00 sec)
mysql (root@test)> select * from t2;
+----+------+
| c1 | c2 |
+----+------+
| 1 | we |
| 2 | zxcv |
| 3 | zx |
| 4 | eee |
+----+------+
4 rows in set (0.00 sec)
mysql (root@test)> select * from t3;
+------+------+
| c1 | c2 |
+------+------+
| 2 | 1 |
| 1 | 1 |
| 1 | 4 |
| 2 | 3 |
+------+------+
4 rows in set (0.00 sec)
To tabela t3 najbardziej nas interesuje. Posiada dwie kolumny, które są kluczami obcymy:
- c1 do tabeli t1 (c1), z opcją ON DELETE RESTRICT
- c2 do tabeli t2 (c1), z opcją ON DELETE CASCADE
Teraz usunę jeden wiersz z tabeli t2. Oprócz tego, że operacja na tabeli t2 została wykonana poprawnie, w tabeli t3, także zostały usunięte wiersze, gdzie klucz obcy odpowiadał usuniętemu wierszowi.
mysql (root@test)> delete from t2 where c1 = 1;
Query OK, 1 row affected (0.04 sec)
mysql (root@test)> select * from t3;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 4 |
| 2 | 3 |
+------+------+
2 rows in set (0.00 sec)
Jeśli jednak tę samą operacje chce wykonać na tabeli t1, otrzymuję błąd:
mysql (root@test)> delete from t1 where c1 = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`))
Musimy zmienić nasz constraint tak aby można było usunąć wiersz z tabeli t1 i jednocześnie updatować wartość w kluczu głównym. Musimy jednak usunąć i przywrócić go z nowymi opcjami:
mysql (root@test)> alter table t3 drop FOREIGN KEY t3_ibfk_1;
Query OK, 2 rows affected (0.33 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql (root@test)> alter table t3 add CONSTRAINT t3_ibfk_1 FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`) ON DELETE SET NULL;
Query OK, 2 rows affected (0.34 sec)
Records: 2 Duplicates: 0 Warnings: 0
Usuwając wiersz z tabeli t1, w t3 klucz główny, który odwoływał się do usunietego wiersza, został updatowany i teraz ma wartość NULL:
mysql (root@test)> delete from t1 where c1 = 1;
Query OK, 1 row affected (0.04 sec)
mysql (root@test)> select * from t3;
+------+------+
| c1 | c2 |
+------+------+
| NULL | 4 |
| 2 | 3 |
+------+------+
2 rows in set (0.00 sec)
Podsumowując (
dokumentacja MySQL'a,
dokumentacja PostgreSQL'a):
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
gdzie:
- ON DELETE / ON UPDATE - jest możliwe aby dla każdej akcji wykonać różne operacje (np. ON DELETE CASCADE, ON UPDATE SET NULL) dla tej samej kolumny. Jeśli zdefiniowana jest tylko akacja ON DELETE, to tak naprawdę akcja ON UPDATE też jest zdefiniowana, ale z opcjami domyślnymi (RESTRICT). Aby uściślić wszystkie informację, wspomnę tylko, że operacja UPDATE w tym przypadku dotyczy samego klucza głównego, a nie innych kolumn tabeli do której się odwołuje nasz klucz obcy.
- RESTRICT - jest domyślną opcją. Nie pozwala wykonać operacji (w tym przypadku) usunięcia, puki istnieje wiersz do którego się odnosi.
- CASCADE - jeśli wiersz, do którego się odnosi, jest usuwany, to i ten wiersz musi być usunięty.
- SET NULL - po usunięciu wiersza z kluczem głównym, klucz obcy jest updatowany do wartości NULL. Ważne jest aby pamiętać, że kolumna z kluczem obcym może przyjmować NULL-e
- NO ACTION - W przeciwieństwie do opcji RESTRICT, pozwala na usunięcie wiersza z tabeli do której odnosi się klucz obcy.
- SET DEFAULT (PostgreSQL)- to samo co SET NULL, ale wstawia wartość domyślną.
Warto jeszcze dodać, że constraint typu FOREIGN KEY ustawiamy 'łącząc' ze sobą kolumny o tych samych typach (np int(11) unsigned) gdzie ENGINE także jest ten sam (MySQL).
Constraint-y zapobiegają pojawieniu się błędnych danych dlatego warto o nich pomyśleć w czasie projektowania naszej bazy danych.
Powodzenia
