Oracle 11g - 使用 RegEx 检查约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7621568/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 11g - Check constraint with RegEx
提问by Pacane
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:
我想使用的正则表达式（从 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 WHEREclauses.
我见过使用 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 不是验证电子邮件地址格式和此类信息的好方法。评论中没有给出任何理由，如果有理由，我想知道为什么！
回答by a_horse_with_no_name
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:
手册中的更多详细信息：
- 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
- 对于 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
Edit:
编辑：
There are however some restrictions on what you can actually use in a check constraint:
但是，您可以在检查约束中实际使用的内容有一些限制：
回答by Dr. Parag C Shukla
CREATE TABLE MYTABLE(
EMAIL VARCHAR2(30) CHECK(REGEXP_LIKE (EMAIL,'^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'))
);
ALTER TABLE MYTABLE ADD(CONTACT NUMBER(10) CHECK(REGEXP_LIKE(CONTACT,'[0-9]{10}')));
Explanation of Regular Expression
^ #start of the line
[_A-Za-z0-9-]+ # must start with string in the bracket [ ], must contains one or more (+)
( # start of group #1
\.[_A-Za-z0-9-]+ # follow by a dot "." and string in the bracket [ ], must contains one or more (+)
)* # end of group #1, this group is optional (*)
@ # must contains a "@" symbol
[A-Za-z0-9]+ # follow by string in the bracket [ ], must contains one or more (+)
( # start of group #2 - first level TLD checking
\.[A-Za-z0-9]+ # follow by a dot "." and string in the bracket [ ], must contains one or more (+)
)* # end of group #2, this group is optional (*)
( # start of group #3 - second level TLD checking
\.[A-Za-z]{2,} # follow by a dot "." and string in the bracket [ ], with minimum length of 2
) # end of group #3
$ #end of the line
