SQL:检查约束以强制任意长度的正则表达式?

Question

我是正则表达式的新手，我正在使用Postgres。我正在尝试在表中创建一个检查约束，以便某些列中的任何数据始终仅检查字母数字、数字、不带空格的字母数字等。

例如，给定一个表，例如：

CREATE TABLE client (
PRIMARY KEY (id),
id                  CHAR(18)    NOT NULL,
name                TEXT        NOT NULL,
promo_code          TEXT        NOT NULL
);

我想用 CHECK 做一个约束，它只允许 id 中没有空格内容的字母数字、名称中没有空格的字母数字、promo_code中没有空格内容的符号的字母数字。 id 的严格长度为 18，但名称和promo_code的长度是可变的。

像下面这样的东西会起作用吗？

ALTER TABLE client
ADD CONSTRAINT ck_client_one
CHECK (
id ~ '%w%' AND id !~ '%s%',
name ~ '%w%' AND name !~ '%d%',
promo_code ~ '%w%' OR promo_code ~ '%W%' AND promo_code !~ '%s%'
);
ALTER TABLE client
ADD CONSTRAINT ck_client_two
CHECK (
id ~ '%^[a-zA-Z0-9]*%' AND id !~ '%s%',
name ~ '%^[a-zA-Z]*%',
promo_code ~ '%^[a-zA-Z0-9]*%' OR promo_code !~ '%^[a-zA-Z0-9]*%' 
AND promo_code !~ '%s%'
);

rd_nielsen

ALTER TABLE client
ADD CONSTRAINT ck_client_one
CHECK (
id ~ '.+w+.+' AND id !~ '.+s+.+',
name ~ '.+w+.+' AND name !~ '.+d+.+',
promo_code ~ '.+w+.+' OR promo_code ~ '.+W+.+' 
AND promo_code !~ '.+s+.+'
);

谢谢！

Answer 1

类似表达式的内容将起作用，但表达式包含 SQL 多字符通配符 ("%"( 和正则表达式语法的混合。 您应该删除"%"字符并根据需要修改正则表达式。 例如

'%w%'

应该是

'.*w*.*'

如果列不可为空，则应改为w+w*。 其他表达式应进行类似的修改。

Answer 2

有几件事值得一提：

• %字符被LIKE表达式用作零个或多个通配符。正则表达式等效项是.*。
• 如果要匹配整个字符串，则需要将开头和结尾锚定为^和$。
  • 相反，未锚定的模式表现得好像两端都有通配符;例如，s与^.*s.*$相同。
• w匹配下划线，s匹配制表符和换行符，您可能不希望允许这样做。
• ~*运算符执行不区分大小写的匹配，使您可以简化[A-Za-z]到[a-z]。

考虑到所有这些：

id ~* '^[a-z0-9]{18}$'
name ~* '^[a-z ]*$'
promo_code !~ 's'

如果要确保name不是完全空白的，请改用^[a-z ]+$。

promo_code检查将允许的不仅仅是"符号";Unicode 包含许多控制代码和不可打印的字符，这些字符不算作"空格"。您可能希望改用显式白名单。