Oracle 11g-使用RegEx检查约束 [英] Oracle 11g - Check constraint with RegEx

问题描述

我正在使用Oracle 11g，并试图创建一个表来定义对创建的约束.

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

我试图添加检查约束以验证某些信息(例如电子邮件地址，电话号码等)

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

Oracle 11g中有什么可以让我做这样的事情吗?

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

constraint CK_CONSTRAINT_NAME check (EMAIL like 'REGEX')

我想使用的regEx(取自regexLib)是:

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

^[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})$

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

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

我见过使用REGEX_LIKE的方法，但它似乎仅在WHERE子句中起作用.

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

我想将其保留为检查约束，而不是触发器或外部函数/脚本.

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

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

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!

推荐答案

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

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

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

手册中的更多详细信息:

More details in the manual:

• 对于Oracle 11- http://docs. oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF52141
• 对于Oracle 12- https://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF52141

• for Oracle 11 - http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF52141
• for Oracle 12 - https://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF52141

但是，对于可以在检查约束中实际使用的内容有一些限制:

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

• Oracle 11- http://docs.oracle .com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52205
• Oracle 12- https://docs.oracle.com/database /121/SQLRF/clauses002.htm#SQLRF52205

• Oracle 11 - http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52205
• Oracle 12 - https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52205

这篇关于Oracle 11g-使用RegEx检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！