Автор: Konstantin Kosinsky
Одной из рекомендуемых практик при построении базы данных, является задание максимального числа ограничений на данные средствами самой СУБД, т.к. не известно, что и как будет реализовано на уровне приложения и сколько этих приложений может быть. Важным ограничением, которое позволит отсечь много мусора является ограничение уникальности. Например, если у меня есть таблица проектов:
|
1 2 3 4 5 |
CREATE TABLE Projects ( ProjectID int identity not null primary key, Name nvarchar(50) ) |
и я хочу обеспечить уникальность названия проекта, я сделаю так:
|
1 |
ALTER TABLE Projects ADD CONSTRAINT UQ_Project_Name UNIQUE (Name) |
И тогда, для следующего запроса:
|
1 2 |
insert into Projects VALUES('Project #1') insert into Projects VALUES('Project #1') |
Первая строчка будет выполнена, а вторая вернет ошибку:
|
1 2 |
Msg 2627, Level 14, State 1, Line 2 Violation of UNIQUE KEY constraint 'UQ_Project_Name'. Cannot insert duplicate key in object 'dbo.Projects'. |
В результате, даже если будут баги в приложениях, то в таблицу 100% не попадут не уникальные проекты.
Но что делать если нужна только частичная уникальность. Например, таблица создается следующим образом:
|
1 2 3 4 5 6 |
CREATE TABLE Projects ( ProjectID int identity not null primary key, Name nvarchar(50), IsDeleted bit not null default 0 ) |
и нужно обеспечить только уникальность актуальных проектов (то есть только тех для которых IsDeleted == 0). Ограничение уникальности здесь не поможет.
Вторым способом обеспечить уникальность столбца является создание уникального индекса. Точнее когда мы создаем UNIQUE CONSTRAINT индекс создается автоматом, при чем уникальный индекс.
Если выполнить следующий запрос, который вернет все индексы в таблице Projects:
|
1 |
select * from sys.indexes where object_id=OBJECT_ID('Projects') |
То получим:
Хотя уникальный индекс можно создать и без создания UNIQUE CONSTRAINT.
Если ко всему этому добавит появившиеся в SQL Server 2008 фильтрованные индексы, то мы можем обеспечить частичную уникальность для имени проекта, следующим образом:
|
1 2 |
CREATE UNIQUE INDEX UNX_Project_Name On Projects(Name) WHERE IsDeleted=0 |
И теперь, если выполнить скрипт:
|
1 2 3 4 |
insert into Projects VALUES('Project #1',0) insert into Projects VALUES('Project #1',1) insert into Projects VALUES('Project #1',1) insert into Projects VALUES('Project #1',0) |
То первые три выражения пройдут, а последнее выдаст ошибку:
|
1 2 |
Msg 2601, Level 14, State 1, Line 4 Cannot insert duplicate key row in object 'dbo.Projects' with unique index 'UNX_Project_Name'. |
В результате названия актуальных проектов не могут дублироватся, а удаленные могут быть любыми.
