Прочие ограничения – unique, default, check

При помощи ограничения UNIQUE можно сказать что значения для каждой строки в данном поле или в наборе полей должно быть уникальным. В случае таблицы Employees, такое ограничение мы можем наложить на поле Email. Только предварительно заполним Email значениями, если они еще не определены:

UPDATE Employees SET Email='[email protected]' WHERE ID=1000

UPDATE Employees SET Email='[email protected]' WHERE ID=1001

UPDATE Employees SET Email='[email protected]' WHERE ID=1002

UPDATE Employees SET Email='[email protected]' WHERE ID=1003

А теперь можно наложить на это поле ограничение-уникальности:

ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE(Email)

Теперь пользователь не сможет внести один и тот же E-Mail у нескольких сотрудников.

Ограничение уникальности обычно именуется следующим образом – сначала идет префикс «UQ_», далее название таблицы и после знака подчеркивания идет имя поля, на которое накладывается данное ограничение.

Соответственно если уникальной в разрезе строк таблицы должна быть комбинация полей, то перечисляем их через запятую:

ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения UNIQUE(поле1,поле2,…)

При помощи добавления к полю ограничения DEFAULT мы можем задать значение по умолчанию, которое будет подставляться в случае, если при вставке новой записи данное поле не будет перечислено в списке полей команды INSERT. Данное ограничение можно задать непосредственно при создании таблицы.

Давайте добавим в таблицу Employees новое поле «Дата приема» и назовем его HireDate и скажем что значение по умолчанию у данного поля будет текущая дата:

ALTER TABLE Employees ADD HireDate date NOT NULL DEFAULT SYSDATETIME()

Или если столбец HireDate уже существует, то можно использовать следующий синтаксис:

ALTER TABLE Employees ADD DEFAULT SYSDATETIME() FOR HireDate

Здесь я не указал имя ограничения, т.к. в случае DEFAULT у меня сложилось мнение, что это не столь критично. Но если делать по-хорошему, то, думаю, не нужно лениться и стоит задать нормальное имя. Делается это следующим образом:

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate

Та как данного столбца раньше не было, то при его добавлении в каждую запись в поле HireDate будет вставлено текущее значение даты.

При добавлении новой записи, текущая дата так же будет вставлена автоматом, конечно если мы ее явно не зададим, т.е. не укажем в списке столбцов. Покажем это на примере, не указав поле HireDate в перечне добавляемых значений:

INSERT Employees(ID,Name,Email)VALUES(1004,N'Сергеев С.С.','[email protected]')

Посмотрим, что получилось:

SELECT * FROM Employees

 

ID	Name	Birthday	Email	PositionID	DepartmentID	ManagerID	HireDate
1000	Иванов И.И.	1955-02-19	[email protected]	2	1	NULL	2015-04-08
1001	Петров П.П.	1983-12-03	[email protected]	3	4	1003	2015-04-08
1002	Сидоров С.С.	1976-06-07	[email protected]	1	2	1000	2015-04-08
1003	Андреев А.А.	1982-04-17	[email protected]	4	3	1000	2015-04-08
1004	Сергеев С.С.	NULL	[email protected]	NULL	NULL	NULL	2015-04-08

Проверочное ограничение CHECK используется в том случае, когда необходимо осуществить проверку вставляемых в поле значений. Например, наложим данное ограничение на поле табельный номер, которое у нас является идентификатором сотрудника (ID). При помощи данного ограничения скажем, что табельные номера должны иметь значение от 1000 до 1999:

ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999)

Ограничение обычно именуется так же, сначала идет префикс «CK_», затем имя таблицы и имя поля, на которое наложено это ограничение.

Попробуем вставить недопустимую запись для проверки, что ограничение работает (мы должны получить соответствующую ошибку):

INSERT Employees(ID,Email) VALUES(2000,'[email protected]')

А теперь изменим вставляемое значение на 1500 и убедимся, что запись вставится:

INSERT Employees(ID,Email) VALUES(1500,'[email protected]')

Можно так же создать ограничения UNIQUE и CHECK без указания имени:

ALTER TABLE Employees ADD UNIQUE(Email)

ALTER TABLE Employees ADD CHECK(ID BETWEEN 1000 AND 1999)

Но это не очень хорошая практика и лучше задавать имя ограничения в явном виде, т.к. чтобы разобраться потом, что будет сложнее, нужно будет открывать объект и смотреть, за что он отвечает.

При хорошем наименовании много информации об ограничении можно узнать непосредственно по его имени.

И, соответственно, все эти ограничения можно создать сразу же при создании таблицы, если ее еще нет. Удалим таблицу:

DROP TABLE Employees

И пересоздадим ее со всеми созданными ограничениями одной командой CREATE TABLE:

CREATE TABLE Employees(

ID int NOT NULL,

Name nvarchar(30),

Birthday date,

Email nvarchar(30),

PositionID int,

DepartmentID int,

HireDate date NOT NULL DEFAULT SYSDATETIME(), -- для DEFAULT я сделаю исключение

CONSTRAINT PK_Employees PRIMARY KEY (ID),

CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID),

CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID),

CONSTRAINT UQ_Employees_Email UNIQUE (Email),

CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN 1000 AND 1999)

)

Напоследок вставим в таблицу наших сотрудников:

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID)VALUES

(1000,N'Иванов И.И.','19550219','[email protected]',2,1),

(1001,N'Петров П.П.','19831203','[email protected]',3,3),

(1002,N'Сидоров С.С.','19760607','[email protected]',1,2),

(1003,N'Андреев А.А.','19820417','[email protected]',4,3)