SQLite创建CONTACT字段格式CHECK约束插入合规数据失败

你之前两次约束写法不生效，都是规则本身存在错误：

• 第一次建表时的LIKE规则逻辑完全错误：'%([0-9]*3)[0-9]*10%' 实际匹配的是「任意位置出现左括号、任意长度数字、字面量3、右括号、任意长度数字、字面量10」的字符串，和你要求的(3位数字)10位数字格式没有关联，自然符合格式的正常值也会被拦截。
• 第二次ALTER新增的约束有两个硬伤：
  • SUBSTRING截取长度和LIKE匹配规则不匹配：你截取了2-4位共3个字符、6位开始共10个字符，却用只能匹配单个数字的'[0-9]'做匹配，永远返回false
  • 没有校验字符串总长度，就算单字符位置正确，长度不对的非法值也可能漏过

你需要的格式固定为14位字符：第1位是(，第2-4位是3位数字，第5位是)，第6到14位是10位数字。两种写法都可实现校验，第一种更简洁：

写法1：直接用LIKE做完全匹配 #

SQL Server的LIKE在不加前后%通配符时会做全等匹配，不需要额外判断长度：

-- 新建表时直接使用该语句
create table patient
(
USERNUM INT PRIMARY KEY,
CONTACT varchar(15) NOT NULL constraint check_contact check (
  CONTACT 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][0-9]'
),
age int constraint check_age check (age >0 and age <=100),
location varchar(50)
);

写法2：用SUBSTRING逐段校验 #

如果是已经建完表，需要替换旧的错误约束，先删除错误约束再加新的即可：

-- 先删除之前创建的错误约束
ALTER TABLE patient DROP CONSTRAINT IF EXISTS check_contact;
ALTER TABLE patient DROP CONSTRAINT IF EXISTS ch_contact;

-- 新增正确约束
ALTER TABLE patient ADD CONSTRAINT check_contact CHECK (
  LEN(CONTACT) = 14
  AND SUBSTRING(CONTACT,1,1) = '('
  AND SUBSTRING(CONTACT,5,1) = ')'
  AND SUBSTRING(CONTACT,2,3) NOT LIKE '%[^0-9]%'
  AND SUBSTRING(CONTACT,6,10) NOT LIKE '%[^0-9]%'
);

补充：如果CONTACT字段允许为NULL，在约束条件最前面加OR CONTACT IS NULL即可，否则NULL值会被判定为不符合规则无法插入。

写完约束后再执行你的测试插入语句就能正常写入了：

insert into patient (USERNUM, CONTACT, AGE, LOCATION)
VALUES (5, '(000)0234567890', 34, 'VIZAG');

所有不符合格式的值，比如括号内数字不足3位、后缀数字不足10位、含非数字字符、缺括号的情况，都会被CHECK约束正常拦截。

内容的提问来源于stack exchange，提问作者Sanjeev Kallepalli