SQL Server поддерживает регулярные выражения при проверке ограничений, не всегда нужны триггеры

Кто-то задал такой вопрос:

Мне нужно создать таблицу group со столбцом code. Можно ли добавить ограничение на столбец, которое допускало бы только буквенные символы (D, M, O, P или T) с последующими двумя цифровыми символами?

Ему ответили:

Вы не можете этого сделать обычным способом — MS SQL Server поддерживает проверку ограничений, но лишь в виде мин/макс значения INT, длины строки и т.п. То, что вам нужно, — проверка на основе регулярного выражения, которую SQL Server не поддерживает. Теоретически можно сделать .NET вставку, развернуть на SQL Server и заставить его использовать такую проверку — нетривиальная задача.

Попробуем разобраться

Не смотря на то, что SQL server не поддерживает регулярные выражения в полной мере, но поставленную выше задачу можно без проблем решить и на T-SQL. Вот так будет выглядеть это регулярное выражение

[DMOPT][0-9][0-9]

Оно допускает только буквенные символы (D, M, O, P или T) с последующими двумя цифровыми символами. Ладно, хватит разговоров, давайте перейдем к коду

Создадим таблицу

CREATE TABLE blatest(code char(3))

Добавим проверку ограничения

ALTER TABLE blatest ADD  CONSTRAINT ck_bla 
CHECK (code like '[DMOPT][0-9][0-9]' )
GO

Теперь несколько инструкций для вставки данных

INSERT blatest VALUES('a12') --недопустимое значение
INSERT blatest VALUES('M12')  --допустимое значение
INSERT blatest VALUES('D12') --допустимое значение
INSERT blatest VALUES('DA1') --недопустимое значение

Как видите, мы дважды получили сообщение об ошибке
Сообщение 547, уровень 16, состояние 0, строка 1
Конфликт инструкции INSERT с ограничением CHECK "ck_bla". Конфликт произошел в базе данных "Test", таблица "dbo.blatest", столбец 'code'.
Выполнение данной инструкции было прервано.

Если вы хотите выполнить вставку D12, а не d12, т.е. нужна чувствительность к регистру, тогда нужно создать такое ограничение

(code like '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS )

Все, что потребовалось изменить — добавить параметр сортировки SQL_Latin1_General_CP1_CS_AS.

Чтобы узнать, что дает этот параметр, выполните следующий запрос

SELECT * FROM ::fn_helpcollations()
WHERE name = 'SQL_Latin1_General_CP1_CS_AS'

Вот, что вернул запрос в описании

Latin1-General, case-sensitive, accent-sensitive, kanatype-
insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data

Давайте создадим новое ограничение, но прежде нам нужно удалить старое

ALTER TABLE blatest DROP CONSTRAINt ck_bla
GO

Теперь создаем новое и проверяем

ALTER TABLE blatest ADD CONSTRAINT ck_bla 
CHECK (code LIKE '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS )
GO

INSERT blatest VALUES('D12') --допустимое значение
INSERT blatest VALUES('d12') --недопустимое значение

Вставка значения D12 будет успешной, а d12 — нет.
Как видите, использовать регулярные выражения в ограничениях можно и никакой триггер в данном случае не нужен.

Автор: rt001

Источник