检查(CHECK)约束用来指定某列可取值的范围。它通过限制输入列中的值来强制域的完整性。可以在单列上定义多个CHECK约束,以它们定义的顺序来求值。
1.使用Transact-SQL语句创建检查约束
创建检查约束的语法格式为:
[ CONSTRAINT constraint_name ] CHECK (expression)
其中,expression定义要对列进行检查的条件,可以是任何表达式,包括算术表达式、关系表达式、逻辑表达式或如IN、LIKE和BETWEEN之类的关键字。
(1)在创建表时添加CHECK约束
【例3.35】 创建学生表Student,包含sid(学号)、sname(姓名)、sage(年龄)和scity(城市)4个字段,并在sage字段创建一个CHECK约束,使sage的值在18~30岁之间。
CREATE TABLE Student ( sid int PRIMARY KEY, sname char(20), sage int CONSTRAINT check_age CHECK (sage>=18 AND sage<=30), scity char(10) )
该语句还可写成如下形式:
CREATE TABLE Student ( sid int PRIMARY KEY, sname char(20), sage int CONSTRAINT check_age CHECK (sage BETWEEN 18 AND 30), scity char(10) )
当向该表执行插入或更新操作时,SQL Server会检查插入的新列值是否满足CHECK约束的条件,若不满足,系统会报错,并拒绝执行插入或更新操作。
(2)在已存在的表中添加CHECK约束
【例3.36】 修改学生表Student,在scity字段创建一个CHECK约束,以限制只能输入有效的城市。
ALTER TABLE Student
WITH NOCHECK
ADD CONSTRAINT check_city CHECK (scity IN('北京','上海','天津','重庆'))
选项WITH NOCHECK表示在创建约束时不检查现有数据,若缺省该选项,系统在创建约束之前检查表中已有数据,如果已有数据不满足CHECK约束的条件,则系统会报错,并拒绝执行添加CHECK约束的操作。
【例3.37】 修改Seller表,在Telephone字段创建一个CHECK约束,使该字段值的格式为([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]。
ALTER TABLE Seller ADD CONSTRAINT check_telephone CHECK (Telephone LIKE '([0-9][0-9][0-9])[0-9][0-9] [0-9][0-9][0-9][0-9][0-9][0-9]')
Seller表中已有的数据如图3-47所示。
执行上述ALTER TABLE语句,系统报错,因为系统在创建CHECK约束前,对已有数据进行检测,发现3条记录Telephone字段的值均不满足CHECK约束,如图3-48所示。
注意 不能在具有IDENTITY属性的列上设置CHECK约束。
2.使用SQL Server Management Studio创建检查约束
【例3.38】 修改Seller表,创建CHECK约束,使雇佣日期(HireDate)字段的值应大于出生日期(Birthday)。
在SQL Server Management Studio下创建CHECK约束的操作步骤为:
1)在SQL Server Management Studio的“对象资源管理器”中,选中需要添加CHECK约束的表Seller,单击鼠标右键,在弹出的快捷菜单中选择“设计”命令,弹出“表设计器”窗口。
2)在该窗口中,右键单击上方窗格,在弹出的快捷菜单中选择“CHECK约束”命令,弹出“CHECK约束”对话框,如图3-49所示。
3)单击“添加”按钮,系统给出默认的约束名CK_Seller,在“(常规)”的“表达式”文本框中输入约束条件:HireDate>Birthday。若要修改已有的CHECK约束,可以在“选定的CHECK约束”列表框中选择要修改的CHECK约束,修改约束表达式即可。
4)“CHECK_约束”中的“在创建或重新启动时检查现有数据”选项决定在创建CHECK约束时是否检测现存数据,如图3-50所示。
5)单击“关闭”按钮,完成CHECK约束的创建或修改。
6)如要删除CHECK约束,则在该对话框的“选定的CHECK约束”列表框中选定要删除的CHECK约束,单击“删除”按钮即可。