Oracle 11g - 使用RegEx检查约束

时间：2021-05-08 16:54:00

I'm using Oracle 11g, and trying to create a table define constraints on the creation.

我正在使用Oracle 11g,并尝试创建一个表定义创建约束。

I was trying to add check constraint to validate some information (like e-mail address, phone number, etc...)

我试图添加检查约束来验证一些信息(如电子邮件地址,电话号码等...)

Is there something in Oracle 11g that would allow me to do something like this?

Oracle 11g中有什么东西可以让我这样做吗?

constraint CK_CONSTRAINT_NAME check (EMAIL like 'REGEX')

The regEx I wanted to use (grabbed from regexLib) is:

我想使用的regEx(从regexLib中获取)是:

^[a-zA-Z][a-zA-Z0-9_\.\-]+@([a-zA-Z0-9-]{2,}\.)+([a-zA-Z]{2,4}|[a-zA-Z]{2}\.[a-zA-Z]{2})$

I think Oracle 11g (correct me if I'm wrong) doesn't support this format for RegEx...

我认为Oracle 11g(纠正我,如果我错了)不支持RegEx的这种格式...

I've seen methods using REGEX_LIKE, but it seems to only work in WHERE clauses.

我见过使用REGEX_LIKE的方法,但它似乎只适用于WHERE子句。

I'd like to keep it as a check constraint and not a trigger or an external function/script.

我想将它作为检查约束而不是触发器或外部函数/脚本。

Also, I've read in other threads here, someone saying RegEx' are not a good way of verifying e-mail address format and such information. No reason was given in the comment, and I'd like to know why, if a reason there is!

此外,我在这里读过其他帖子,有人说RegEx'不是验证电子邮件地址格式和此类信息的好方法。评论中没有给出任何理由,我想知道为什么,如果有原因!

1 个解决方案

#1

13  

A check constraint follows the same syntax rules as conditions for a WHERE clause:

检查约束遵循与WHERE子句的条件相同的语法规则:

alter table foo
  add constraint check_email 
  check (REGEXP_LIKE(email,'your_regex_goes_here','I'));

More details in the manual:

手册中的更多细节:

Edit:

There are however some restrictions on what you can actually use in a check constraint:

但是,在检查约束中实际使用的内容有一些限制:

#1

13  

A check constraint follows the same syntax rules as conditions for a WHERE clause:

检查约束遵循与WHERE子句的条件相同的语法规则:

alter table foo
  add constraint check_email 
  check (REGEXP_LIKE(email,'your_regex_goes_here','I'));

More details in the manual:

手册中的更多细节:

Edit:

There are however some restrictions on what you can actually use in a check constraint:

但是,在检查约束中实际使用的内容有一些限制:

标签：

相关文章