1.2.4. Ограничения значений полей в SQL

Имена ограничений внутри базы данных должны быть уникальными. Это значит, что вы не можете создать два ограничения с одним и тем же именем не только для одной и той же таблицы, но и для разных таблиц одной базы данных.

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

CONSTRAINT Имя ограничения CHECK (Ограничение)

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

Давайте удалим старую таблицу, а создадим новую с еще одним полем – dDate типа datetime. Для этого поля поставим ограничение, которое позволит вводить только даты, которые меньше текущей:

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcName varchar(50) NOT NULL,
 iApartment int,
 dDate datetime,
 CONSTRAINT check_iApartment CHECK 
    (iApartment>0 and iApartment<1000),
 CONSTRAINT check_dDate CHECK 
    (dDate<getdate())
)

Как видите, в этом примере ограничения описываются через запятую, так же, как и поля. После ключевого слова CONSTRAINT указывается имя ограничения. Из своей практики хочу порекомендовать называть их в виде check_имя, где имя – имя поля, которое проверяется ограничением. После этого указывается само ограничение точно также как и в предыдущем примере.

Обратите внимание, что для ограничения ввода в поле "dDate" мы использовали функцию (getdate). Как и при описании значений по умолчанию, в ограничениях также могут использоваться функции.

При создании ограничения, можно использовать многие операторы сравнения языка SQL. Например, в SQL есть очень удобный оператор IN. С его помощью можно задать возможные значения для поля, которые оно может принимать. Например, вам нужно в таблице ограничить ввода данных в поле содержащую такую информацию как пол человека. В этом случае, можно разрешить ввод букв "М" и "Ж" следующим образом:

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcName varchar(50) NOT NULL,
 cPol char(1),
 CONSTRAINT check_cPol CHECK 
    (cPol IN ('М', 'Ж'))
)

В данном случае, ограничение выглядит следующим образом: cPol IN ('М', 'Ж'). Оператор IN означает, что поле может принимать любые значения, перечисленные в круглых скобках. В нашем случае указано две строки 'М' и 'Ж'. Другие буквы вносить в поле нельзя. Конечно же, то же самое можно было бы написать и следующим образом:

(cPol = 'М' or cPol = 'Ж')

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

Очень мощных возможностей можно добиться, используя в ограничении оператора LIKE. Например, вы хотите, чтобы поле для хранения телефонного номера содержало номер в формате (ХХХ) ХХХ-ХХ-ХХ, где Х – это любая цифра. Для реализации примера такого ограничения создадим новую таблицу с полем "vcPhonenumber":

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcPhonenumber varchar(50) NOT NULL
 CONSTRAINT check_vcPhonenumber CHECK
 (vcPhonenumber LIKE 
   '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]')
)

В данном случае, ограничение с именем check_vcPhonenumber при проверки использует оператор LIKE. С этим оператором мы познакомимся ближе в следующей главе, когда будем изучать работу с оператором SELECT, но я решил вставить этот пример, чтобы вы заранее знали о его мощи. Если классический знак равенства производит жесткое сравнение, то LIKE позволяет сравнивать строки с определенным шаблоном. В данном случае шаблоном является:

([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]

В квадратных скобках мы указываем возможный диапазон символа. В данном случае, диапазон от 0 до 9. Если заменить все [0-9] на Х, то мы получим искомый шаблон (ХХХ) ХХХ-ХХ-ХХ. Следующий пример запроса добавляет в таблицу номер телефона (085) 880-08-00:

INSERT INTO TestTable(vcPhonenumber)
VALUES('(085) 880-08-00')

Операция пройдет успешно. Но если убрать пробел, или любой из символов ( или -, или заменить какую-либо цифру буквой, то операция завершиться неудачей:

INSERT INTO TestTable(vcPhonenumber)
VALUES('(095)8800800')

В ответ на это мы увидим сообщение:

INSERT statement conflicted with COLUMN CHECK constraint 'check_vcPhonenumber'. The conflict occurred in database 'TestDatabase', table 'TestTable', column 'vcPhonenumber'.

The statement has been terminated.

Смысл сообщения в следующем: «Оператор INSERT конфликтует с ограничением COLUMN CHECK с именем check_vcPhonenumber. Конфликт появился в базе данных TestDatabase, таблице TestTable, колонке vcPhoneNumber. Выполнение оператора прервано».

Следующий пример задает шаблон для ввода даты:

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcDate varchar(50) NOT NULL
 CONSTRAINT check_vcDate CHECK
 (vcDate LIKE 
    '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]')
)

Такой шаблон бывает удобным, если дату нужно хранить в текстовом виде. Да, есть специализированный тип datetime, но все же, бывают разные задачи и может пригодиться текстовый хранение. Шаблон, позволяет ограничить действия пользователя, дабы уменьшить вероятность ошибки, но не решает задачу полностью. Давайте посмотрим на него по частям.

Первая часть (до точки) определяет число месяца. Первая цифра числа может принимать значения от 0 до 3, а вторая цифра от 0 до 9. Это значит, что пользователь может ввести числа от 01 до 39. Числа от 32 до 39 заранее являются не корректными. При указании месяца пользователь также может указать неверное значение, потому что это значение мы ограничили значениями от 01 до 19.

Бывает необходимость, чтобы определенное поле или сочетание полей были в базе данных уникальными. Это можно проверять с помощью запросов самостоятельно, а можно доверить проверку серверу. Для создания ограничения уникальности используется ограничение UNIQUE, которое выглядит следующим образом:

CONSTRAINT Имя ограничения UNIQUE (Поле или список полей)

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

CREATE TABLE Names
(
 idName int, 
 vcName varchar(50),
 vcLastName varchar(50),
 vcSurName varchar(50),
 dBirthDay datetime, 
 CONSTRAINT cn_unique UNIQUE (idName)
)

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

Самая простая проверка на двойников проходила выявлением записей, где полностью совпадали ФИО и дата рождение клиента. Вероятность совпадения всех этих параметров внутри одного города стремиться к нулю. Таким образом, необходимо было всего лишь добавить ограничение, при котором сочетание из этих полей было уникальным. Это можно сделать следующим образом:

CREATE TABLE Names
(
 idName int , 
 vcName varchar(50),
 vcLastName varchar(50),
 vcSurName varchar(50),
 dBirthDay datetime, 
 CONSTRAINT cn_unique UNIQUE (vcName, vcLastName, 
    vcSurName, dBirthDay)
)

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