SQL 身份证约束.doc

因在数据库中设计一个字段，存储身份信息，网上找不到好的方法来不允许不合法的身份号码存储到记录中，编者只有自己写了一个只允许在数据库中输入合法身份证的约束，供大家参考在设计身份证约束前，我们先设计出生日期的约束首先，在表中建立一个字段，下面以IDCardNumber_Birthday为例，字段类型为 nchar(8)打开对象资源管理器，选择要设置约束的表展开，右键点击约束，选择新建约束在CHECK约束界面，点击表达式右边的编辑按钮将以下表达式复制到弹出编辑界面（记得将下列代码中IDCardNumber_Birthday替换为自己的字段名）：(IDCardNumber_Birthday like 1-20,90-90-90-10-90-30-9 AND (substring(IDCardNumber_Birthday,(1),(1)=1 AND substring(IDCardNumber_Birthday,(2),(1)=9 OR substring(IDCardNumber_Birthday,(1),(1)=2 AND substring(IDCardNumber_Birthday,(2),(1)=0) AND (substring(IDCardNumber_Birthday,(5),(1)=1 AND substring(IDCardNumber_Birthday,(6),(1)=1 AND substring(IDCardNumber_Birthday,(6),(1)2 OR substring(IDCardNumber_Birthday,(6),(1)=2 AND (substring(IDCardNumber_Birthday,(7),(1)2 OR substring(IDCardNumber_Birthday,(7),(1)=2 AND (substring(IDCardNumber_Birthday,(3),(1)*(10)+substring(IDCardNumber_Birthday,(4),(1)%(4)=(0) OR (substring(IDCardNumber_Birthday,(3),(1)*(10)+substring(IDCardNumber_Birthday,(4),(1)%(4)(0) AND substring(IDCardNumber_Birthday,(8),(1)9) AND (substring(IDCardNumber_Birthday,(7),(1)=3 AND (substring(IDCardNumber_Birthday,(6),(1)=4 OR substring(IDCardNumber_Birthday,(6),(1)=6 OR substring(IDCardNumber_Birthday,(6),(1)=9 OR substring(IDCardNumber_Birthday,(5),(1)=1 AND substring(IDCardNumber_Birthday,(6),(1)=1) AND substring(IDCardNumber_Birthday,(8),(1)=0 OR (substring(IDCardNumber_Birthday,(6),(1)=3 OR substring(IDCardNumber_Birthday,(6),(1)=5 OR substring(IDCardNumber_Birthday,(6),(1)=7 OR substring(IDCardNumber_Birthday,(6),(1)=8 OR substring(IDCardNumber_Birthday,(5),(1)=0 AND substring(IDCardNumber_Birthday,(6),(1)=1 OR substring(IDCardNumber_Birthday,(5),(1)=1 AND substring(IDCardNumber_Birthday,(6),(1)=0 OR substring(IDCardNumber_Birthday,(5),(1)=1 AND substring(IDCardNumber_Birthday,(6),(1)=2) AND substring(IDCardNumber_Birthday,(8),(1)=1 OR substring(IDCardNumber_Birthday,(7),(1)=2 OR substring(IDCardNumber_Birthday,(7),(1)=1)以上表达式将年约束在1900-2099之间，如不需此约束，请删去红色部分下面身份证字段的约束设计，我们都知道，身份证的组成结构是6位地址码，接下来是8位出生日期，然后是3位随机序号（第3位单数为男，双数为女），最后1位校检位，我们先设计一个IDCradNumber字段，类型nchar(18)为避免出现不合法的出生日期，必须使用上面的出生日期约束，只需要将上面代码的substring()函数第二个参数的值加6（出生日期在6位地址码后面），再和校检表达式做AND运算即可表达式如下：(IDCardNumber like 1-61-70-90-90-90-91-20,90-90-90-10-90-30-90-90-90-90-9,X,x AND (substring(IDCardNumber,(7),(1)=1 AND substring(IDCardNumber,(8),(1)=9 OR substring(IDCardNumber,(7),(1)=2 AND substring(IDCardNumber,(8),(1)=0) AND (substring(IDCardNumber,(11),(1)=1 AND substring(IDCardNumber_Birthday,(12),(1)=1 AND substring(IDCardNumber,(12),(1)2 OR substring(IDCardNumber,(12),(1)=2 AND (substring(IDCardNumber,(13),(1)2 OR substring(IDCardNumber,(13),(1)=2 AND (substring(IDCardNumber,(9),(1)*(10)+substring(IDCardNumber,(10),(1)%(4)=(0) OR (substring(IDCardNumber,(9),(1)*(10)+substring(IDCardNumber,(10),(1)%(4)(0) AND substring(IDCardNumber,(14),(1)9) AND (substring(IDCardNumber,(13),(1)=3 AND (substring(IDCardNumber,(12),(1)=4 OR substring(IDCardNumber,(12),(1)=6 OR substring(IDCardNumber,(12),(1)=9 OR substring(IDCardNumber,(11),(1)=1 AND substring(IDCardNumber,(12),(1)=1) AND substring(IDCardNumber,(14),(1)=0 OR (substring(IDCardNumber,(12),(1)=3 OR substring(IDCardNumber,(12),(1)=5 OR substring(IDCardNumber,(12),(1)=7 OR substring(IDCardNumber,(12),(1)=8 OR substring(IDCardNumber,(11),(1)=0 AND substring(IDCardNumber,(12),(1)=1 OR substring(IDCardNumber,(11),(1)=1 AND substring(IDCardNumber,(12),(1)=0 OR substring(IDCardNumber,(11),(1)=1 AND substring(IDCardNumber,(12),(1)=2) AND substring(IDCardNumber,(14),(1)=1 OR substring(IDCardNumber,(13),(1)=2 OR substring(IDCardNumber,(13),(1)=1)AND (substring(IDCardNumber,(18),(1)x or substring(IDCardNumber,(18),(1)X) and (substring(IDCardNumber,(1),(1)*(7)+ substring(IDCardNumber,(2),(1)*(9) + substring(IDCardNumber,(3),(1)*(10) + substring(IDCardNumber,(4),(1)*(5) + substring(IDCardNumber,(5),(1)*(8) + substring(IDCardNumber,(6),(1)*(4) + substring(IDCardNumber,(7),(1)*(2) + substring(IDCardNumber,(8),(1)*(1) + substring(IDCardNumber,(9),(1)*(6) + substring(IDCardNumber,(10),(1)*(3) + substring(IDCardNumber,(11),(1)*(7) + substring(IDCardNumber,(12),(1)*(9) + substring(IDCardNumber,(13),(1)*(10) + substring(IDCardNumber,(14),(1)*(5) + substring(IDCardNumber,(15),(1)*(8) + substring(IDCardNumber,(16),(1)*(4) + substring(IDCardNumber,(17),(1)*(2)%11= (12-substring(IDCardNumber,(18),(1)%11) or (substring(IDCardNumber,(18),(1)=x or substring(IDCardNumber,(18),(1)=X) and (substring(IDCardNumber,(1),(1)*(7)+ substring(IDCardNumber,(2),(1)*(9) + substring(IDCardNumber,(3),(1)*(10) + substring(IDCardNumber,(4),(1)*(5) + substring(IDCardNumber,(5),(1)*(8) + substring(IDCardNumber,(6),(1)*(4) + substring(IDCardNumber,(7),(1)*(2) + substring(IDCardNumber,(8),(1)*(1) + substring(IDCardNumber,(9),(1)*(6) + substring(IDCardNumber,(10),(1)*(3) + substring(IDCardNumber,(11),(1)*(7) + substring(IDCardNumber,(12),(1)*(9) + substring(IDCardNumber,(13),(1)*(10) + substring(IDCardNumber,(14),(1)*(5) + substring(IDCardNumber,(15),(1)*(8) + sub