Podsumowanie: W tym poradniku dowiesz się jak używać SQL Server UNIQUE constraint do zapewnienia unikalności danych zawartych w kolumnie lub grupie kolumn.
Wprowadzenie do SQL Servera Ograniczenie UNIQUE
Serwer SQL UNIQUE Ograniczenia pozwalają zapewnić, że dane przechowywane w kolumnie lub grupie kolumn są unikalne wśród wierszy w tabeli.
Poniższe wyrażenie tworzy tabelę, której dane w kolumnie email są unikalne wśród wierszy w tabeli hr.persons:
Code language: SQL (Structured Query Language) (sql)CREATE SCHEMA hr;GOCREATE TABLE hr.persons( person_id INT IDENTITY PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE);
W tej składni definiujemy ograniczenie UNIQUE jako ograniczenie kolumny. Można również zdefiniować ograniczenie UNIQUE jako ograniczenie tabeli, w ten sposób:
Code language: SQL (Structured Query Language) (sql)CREATE TABLE hr.persons( person_id INT IDENTITY PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255), UNIQUE(email));
Za kulisami, SQL Server automatycznie tworzy indeks UNIQUE w celu wymuszenia unikalności danych przechowywanych w kolumnach, które uczestniczą w ograniczeniu UNIQUE. Dlatego też, jeśli spróbujesz wstawić zduplikowany wiersz, SQL Server odrzuci zmianę i zwróci komunikat o błędzie informujący, że ograniczenie UNIQUE zostało naruszone.
Poniższe wyrażenie wstawia nowy wiersz do tabeli hr.persons:
Code language: SQL (Structured Query Language) (sql)INSERT INTO hr.persons(first_name, last_name, email)VALUES('John','Doe','[email protected]');
Określenie działa zgodnie z oczekiwaniami. Jednak poniższe wyrażenie nie powiedzie się z powodu duplikatu wiadomości e-mail:
Code language: SQL (Structured Query Language) (sql)INSERT INTO hr.persons(first_name, last_name, email)VALUES('Jane','Doe','[email protected]');
SQL Server wyświetlił następujący komunikat o błędzie:
Code language: Shell Session (shell)Violation of UNIQUE KEY constraint 'UQ__persons__AB6E616417240E4E'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is ([email protected]).
Jeśli nie określisz osobnej nazwy dla ograniczenia UNIQUE, SQL Server automatycznie wygeneruje dla niego nazwę. W tym przykładzie, nazwa ograniczenia to UQ__persons__AB6E616417240E4E, co nie jest do końca czytelne.
Aby przypisać konkretną nazwę do ograniczenia UNIQUE, należy użyć słowa kluczowego CONSTRAINT w następujący sposób:
Code language: SQL (Structured Query Language) (sql)CREATE TABLE hr.persons ( person_id INT IDENTITY PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255), CONSTRAINT unique_email UNIQUE(email));
Istnieją następujące korzyści z przypisania UNIQUE constraint określonej nazwy:
- Łatwiej sklasyfikować komunikat o błędzie.
- Możesz odwołać się do nazwy ograniczenia, gdy chcesz je zmodyfikować.
Ograniczenie UNIQUE vs. PRIMARY KEY constraint
Mimo, że oba ograniczenia UNIQUE i PRIMARY KEY wymuszają unikalność danych, powinieneś użyć ograniczenia UNIQUE zamiast PRIMARY KEY gdy chcesz wymusić unikalność kolumny lub grupy kolumn, które nie są kolumnami klucza głównego.
Odmiennie od PRIMARY KEY ograniczeń, UNIQUE ograniczenia pozwalają NULL. Co więcej, UNIQUE constraints traktuje NULL jako zwykłą wartość, dlatego pozwala tylko na jeden NULL na kolumnę.
Poniższa instrukcja wstawia wiersz, którego wartością w kolumnie email jest NULL:
Code language: SQL (Structured Query Language) (sql)INSERT INTO hr.persons(first_name, last_name)VALUES('John','Smith');
Teraz, jeśli spróbujesz wstawić jeszcze jeden NULL do kolumny email, otrzymasz błąd:
Code language: SQL (Structured Query Language) (sql)INSERT INTO hr.persons(first_name, last_name)VALUES('Lily','Bush');
Tutaj jest wyjście:
Code language: SQL (Structured Query Language) (sql)Violation of UNIQUE KEY constraint 'UQ__persons__AB6E616417240E4E'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is (<NULL>).
Ograniczenia UNIQUE dla grupy kolumn
Aby zdefiniować ograniczenie UNIQUE dla grupy kolumn, zapisujemy je jako ograniczenie tabeli z nazwami kolumn oddzielonymi przecinkami w następujący sposób:
Code language: SQL (Structured Query Language) (sql)CREATE TABLE table_name ( key_column data_type PRIMARY KEY, column1 data_type, column2 data_type, column3 data_type, ..., UNIQUE (column1,column2));
Następujący przykład tworzy ograniczenie UNIQUE, które składa się z dwóch kolumn person_id i skill_id:
Code language: SQL (Structured Query Language) (sql)CREATE TABLE hr.person_skills ( id INT IDENTITY PRIMARY KEY, person_id int, skill_id int, updated_at DATETIME, UNIQUE (person_id, skill_id));
Dodaj ograniczenia UNIQUE do istniejących kolumn
Gdy dodajemy ograniczenie UNIQUE do istniejącej kolumny lub grupy kolumn w tabeli, SQL Server najpierw bada istniejące dane w tych kolumnach, aby upewnić się, że wszystkie wartości są unikalne. Jeśli SQL Server znajdzie zduplikowane wartości, wówczas zwraca błąd i nie dodaje ograniczenia UNIQUE.
Poniżej przedstawiono składnię dodawania ograniczenia UNIQUE do tabeli:
Code language: SQL (Structured Query Language) (sql)ALTER TABLE table_nameADD CONSTRAINT constraint_name UNIQUE(column1, column2,...);
Załóżmy, że masz następującą tabelę hr.persons:
Code language: SQL (Structured Query Language) (sql)CREATE TABLE hr.persons ( person_id INT IDENTITY PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255), phone VARCHAR(20),);
Następująca instrukcja dodaje ograniczenie UNIQUE do kolumny email:
Code language: SQL (Structured Query Language) (sql)ALTER TABLE hr.personsADD CONSTRAINT unique_email UNIQUE(email);
Podobnie, poniższa instrukcja dodaje ograniczenie UNIQUE do kolumny phone:
Code language: SQL (Structured Query Language) (sql)ALTER TABLE hr.personsADD CONSTRAINT unique_phone UNIQUE(phone);
Delete UNIQUE constraints
Aby zdefiniować UNIQUE constraint, używamy instrukcji ALTER TABLE DROP CONSTRAINT w następujący sposób:
Code language: SQL (Structured Query Language) (sql)ALTER TABLE table_nameDROP CONSTRAINT constraint_name;
Następująca instrukcja usuwa ograniczenie unique_phone z tabeli hr.person:
Code language: SQL (Structured Query Language) (sql)ALTER TABLE hr.personsDROP CONSTRAINT unique_phone;
Modyfikacja ograniczeń UNIQUE
SerwerSQL nie posiada bezpośrednich instrukcji do modyfikacji ograniczeń UNIQUE , dlatego należy najpierw usunąć ograniczenie i odtworzyć je, jeśli chcemy zmienić ograniczenie.
W tym poradniku dowiedziałeś się, jak używać ograniczenia SQL Server UNIQUE do upewnienia się, że dane zawarte w kolumnie lub grupie kolumn są unikalne.