Ограничение уникальности SQL Server

В этом руководстве вы узнаете, как использовать ограничение SQL Server UNIQUE для обеспечения уникальности данных, содержащихся в столбце или группе столбцов.

Введение в ограничение UNIQUE SQL Server

Ограничения UNIQUE в SQL Server позволяют гарантировать, что данные, хранящиеся в столбце или группе столбцов, являются уникальными среди строк таблицы.

Следующий оператор создает таблицу, данные в столбце email которой уникальны среди строк в таблице hr.persons:

CREATE SCHEMA hr;
GO
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
);

В этом синтаксисе вы определяете ограничение UNIQUE как ограничение столбца. Вы также можете определить ограничение UNIQUE как ограничение таблицы, например:

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)
);

За кулисами SQL Server автоматически создает индекс UNIQUE для обеспечения уникальности данных, хранящихся в столбцах, которые участвуют в ограничении UNIQUE. Поэтому, если вы попытаетесь вставить дублирующую строку, SQL Server отклонит изменение и вернет сообщение об ошибке, в котором будет указано, что ограничение UNIQUE было нарушено.

Следующий оператор вставляет новую строку в таблицу hr.persons:

INSERT INTO hr.persons(first_name, last_name, email)
VALUES('John','Doe',' [email protected] ');

Оператор работает так, как и ожидалось. Однако следующий оператор не работает из-за дублирующего адреса электронной почты:

INSERT INTO hr.persons(first_name, last_name, email)
VALUES('Jane','Doe',' [email protected] ');

SQL Server выдал следующее сообщение об ошибке:

Violation of UNIQUE KEY constraint 'UQ__persons__AB6E616417240E4E'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is( [email protected] ).

Если вы не укажете отдельное имя для ограничения UNIQUE, SQL Server автоматически сгенерирует для него имя. В этом примере имя ограничения — UQ__persons__AB6E616417240E4E, что не совсем читабельно.

Чтобы присвоить определенное имя ограничению UNIQUE, используйте ключевое слово CONSTRAINT следующим образом:

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)
);

Ниже перечислены преимущества присвоения ограничению UNIQUE определенного имени:

• Легче классифицировать сообщение об ошибке.
• Вы можете ссылаться на имя ограничения, когда захотите его изменить.

Ограничение UNIQUE против ограничения PRIMARY KEY

Хотя ограничения UNIQUE и PRIMARY KEY обеспечивают уникальность данных, следует использовать ограничение UNIQUE вместо ограничения PRIMARY KEY, если требуется обеспечить уникальность столбца или группы столбцов, которые не являются столбцами первичного ключа.

В отличие от ограничений PRIMARY KEY, ограничения UNIQUE допускают NULL. Более того, ограничения UNIQUE рассматривают NULL как обычное значение, поэтому они допускают только одно значение NULL на столбец.

Следующий оператор вставляет строку, значение которой в столбце email равно NULL:

INSERT INTO hr.persons(first_name, last_name)
VALUES('John','Smith');

Теперь, если вы попытаетесь вставить еще один NULL в столбец электронной почты, вы получите ошибку:

INSERT INTO hr.persons(first_name, last_name)
VALUES('Lily','Bush');

Вот что получилось:

Violation of UNIQUE KEY constraint 'UQ__persons__AB6E616417240E4E'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is().

Ограничения UNIQUE для группы столбцов

Чтобы определить ограничение UNIQUE для группы столбцов, вы записываете его как ограничение таблицы с именами столбцов, разделенными запятыми, следующим образом:

CREATE TABLE table_name(
    key_column data_type PRIMARY KEY,
    column1 data_type,
    column2 data_type,
    column3 data_type,
    ...,
    UNIQUE(column1,column2)
);

В следующем примере создается ограничение UNIQUE, состоящее из двух столбцов person_id и skill_id:

CREATE TABLE hr.person_skills(
    id INT IDENTITY PRIMARY KEY,
    person_id int,
    skill_id int,
    updated_at DATETIME,
    UNIQUE(person_id, skill_id)
);

Добавить ограничения UNIQUE к существующим столбцам

Когда вы добавляете ограничение UNIQUE к существующему столбцу или группе столбцов в таблице, SQL Server сначала проверяет существующие данные в этих столбцах, чтобы убедиться, что все значения являются уникальными. Если SQL Server находит дублирующиеся значения, он возвращает ошибку и не добавляет ограничение UNIQUE.

Ниже показан синтаксис добавления ограничения UNIQUE к таблице:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE(column1, column2,...);

Предположим, у вас есть следующая таблица hr.persons:

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),
);  

Следующий оператор добавляет ограничение UNIQUE к столбцу электронной почты:

ALTER TABLE hr.persons
ADD CONSTRAINT unique_email UNIQUE(email);

Аналогично, следующий оператор добавляет ограничение UNIQUE к столбцу телефона:

ALTER TABLE hr.persons
ADD CONSTRAINT unique_phone UNIQUE(phone); 

Удалить ограничения UNIQUE

Чтобы определить ограничение UNIQUE, используйте оператор ALTER TABLE DROP CONSTRAINT следующим образом:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Следующий оператор удаляет ограничение unique_phone из таблицы hr.person:

ALTER TABLE hr.persons
DROP CONSTRAINT unique_phone;

Изменить ограничения UNIQUE

В SQL Server нет прямого оператора для изменения ограничения UNIQUE, поэтому, если вы хотите изменить ограничение, вам необходимо сначала удалить его, а затем создать заново.

В этом руководстве вы узнали, как использовать ограничение SQL Server UNIQUE, чтобы гарантировать уникальность данных, содержащихся в столбце или группе столбцов.