如何正确设置具有多个 LIKE 语句的 CHECK 约束?

Question

我正在尝试强制执行一个简单的规则来插入bank_account列的值：

- bank account can consist of only digits
- bank account can have one hyphen '-' or zero hyphens
- bank account can have one slash '/' or zero slashes

我有这个检查约束：

alter table someTable
add constraint chk_bank check
(
(bank_account not like '%-%-%')
and
(bank_account not like '%/%/%')
and
(bank_account not like '%[^0123456789-/]%')
)

我有这些bank_account数字(它们是虚构的(：

12-4414424434/0987
987654321/9999
NULL
41-101010101010/0011
500501502503/7410
NULL
60-6000070000/1234
7987-42516/7845
NULL
12-12121212/2121

启用约束时，出现此错误：

The ALTER TABLE statement conflicted with the CHECK constraint "chk_bank".
The conflict occurred in database "x", table "someTable", column 'bank_account'.

我尝试了一些选择查询，但找不到错误的数字。

我的检查约束写错了吗？如果是这样，我应该如何更改它以满足我的要求？ 检查约束是否忽略 NULL 值或这些是问题？

Answer 1

根据文档，默认情况下没有转义字符。 必须使用escape子句来表示反斜杠是转义字符：

...and bank_account not like %[^0123456789-/]%' escape ''...

Answer 2

检查逻辑的简单方法是单独选择条件，例如：

select bank_account,
case when bank_account not like '%-%-%' then 1 else 0 end as CheckHyphens,
case when bank_account not like '%/%/%' then 1 else 0 end as CheckSlashes,
case when bank_account not like '%[^0123456789-/]%' then 1 else 0 end as CheckIllegalCharacters,
case when bank_account not like '%[^0123456789-/]%' escape '' then 1 else 0 end as CheckIllegalCharactersWithEscape
from YourTable;

很明显，你的最后一个条件失败了。添加escape子句可更正模式。