SQL-04数据完整性

练习4 约束、默认和规则
4．1上机目的
1、练习使用企业管理器和T-SQL命令在查询分析器中创建表，管理表，熟悉并逐步掌握其中的步骤和对应语句。

2、在企业管理器和查询分析器中练习约束、规则、默认的设置，通过练习逐步掌握约束的种类，作用和创建方法
3、了解数据库关系图的作用，掌握数据库关系图的建立方法。

4．2 上机练习预备知识点
4．2．1 数据完整性
1、数据完整性是指数据库中数据的正确性和一致性，是衡量数据库质量的一个重要标准。

2、数据完整性包括：实体完整性、区域完整性、参照完整性、自定义完整性。

①实体完整性：确保数据库中所有实体的唯一性，也就是不应有完全相同的数据记录。

实现方法：设定主键、设定唯一键、设定唯一索引、设置标识
②区域完整性：要求数据表中的数据位于某一个特定的允许范围内。

实现方法：设定默认值、设定核查约束、设定外键约束、设定规则
③参照完整性：用来维护相关数据表之间数据一致性的手段，避免因一个数据表的记录改变而造成另一个数据表内的数据变成无效的值。

实现方法：设定外键约束、核查约束、触发器和存储过程
④用户自定义完整性：由用户根据实际应用中的需要自行定义。

实现方法：规则、触发器、存储过程等。

4．2．2 约束
1、实现数据完整性最重要的方法，主要目的是限制输入到表中的数值的范围。

字段级约束：是数据表中字段定义的一个部分，只能应用于数据表中的一个字段。

如：为性别设置核查约束，只能输入“男”或者“女”，不能输入其他内容。

数据表级约束：独立于数据表的字段定义之外，它可以应用于数据表中的多个字段。

如将表中的几个字段组合设置为主键。

2、约束的种类：
①主键（PRIMARY KEY）约束
唯一标识表中的每一行的列或者列的组合，可以强制表的实体完整性。

主键不允许为空。

②外键（FOREIGN KEY）约束
若列或者列的组合不是本表的关键字，而是另一个表的关键字，则称这些列或者列的组合是外键。

一般表与表之间通过主键和外键进行连接，通过它可以强制表与表之间实现参照完整性。

外键约束要求列中的每个值在被引用表对应的被引用列中都存在。

③唯一（UNIQUE）约束
用于确保非主键字段中数据的唯一性。

③核查（CHECK）约束
用于限制输入到一列或多列的值的范围，从逻辑表达式判断数据的有效性。

④默认（Default）约束
向数据表中插入记录时，如果用户没有指定该列的值，而该列需要一个明确值的情况下，则插入默认约束定义的值。

⑥非空（Not null）约束
该约束的列不允许使用空值。

4．2．3 实现主键约束
1、企业管理器中：在表设计视图中实现。

如图4－1所示
如果是多个字段设置主键的情况，安Ctrl 键同时选中几个字段，设置主键。

图4－1 设置主键
2、定义的T-SQL 语句：
[CONSTRAINT constraint_name] PRIMARY KEY [(
CONSTRAINT ：约束 constraint_name ：约束名称 3、创建表时设置主键
单个字段设置主键（字段级约束）
例1 项目编号 int CONSTRAINT PK_pno PRIMARY KEY 或者 项目编号 int PRIMARY KEY 多个字段设置主键（表级约束） 例2 项目编号 int,
项目名称 char(10),
CONSTRAINT PK_pno_pname PRIMARY KEY(项目编号,项目名称) 注：以上两个例子，只是创建表语句的一部分，不是全部。

4、修改表时添加主键 ALTER TABLE table_name
ADD [CONSTRAINT constraint_name] PRIMARY KEY [(5、修改表时删除主键 ALTER TABLE table_name DROP CONSTRAINT constraint_name
注：所有删除约束的T-SQL 语句相同，后面不再赘述。

6、设置主键的注意点
①修改表上已经存在的主键时，只能先删除原有主键再重新建立一个新的主键约束。

②当主键被别的表引用时，不允许删除主键约束，除非首先将引用主键的外键删除。

③区分字段级主键约束与表级主键约束。

④使用相关语句删除主键约束时，会用到约束名称。

*7、可以使用sp_pkeys table_name 查看定义好的主键约束。

4．2．4 实现唯一约束
1、企业管理器中：打开表设计视图，选择“属性”按钮，如图4－2所示
在索引/键选项页中新建唯一约束。

图4－2 在表设计视图属性对话框设置唯一约束
2、定义的T-SQL语句：
[CONSTRAINT constrain_name] UNIQUE [(字段名[,…n]) ]
参数说明：
UNIQUE：唯一
3、创建表时设置唯一约束
单个字段设置唯一约束（字段级约束）
例3 部门名称char(18) null CONSTRAINT UN_dname UNIQUE 或者部门名称char(18) null UNIQUE
多个字段设置唯一约束（表级约束）
例4 部门名称char(10) ,
部门负责人char(10) ,
CONSTRAINT UN_dname_dm UNIQUE (部门名称,部门负责人)
注：以上两个例子，只是创建表语句的一部分，不是全部。

4、修改表时添加唯一约束
所用的语句和添加主键约束相似，只是关键字的不同
ALTER TABLE table_name
ADD [CONSTRAINT constraint_name] UNIQUE [(字段名[,…n])] 5、唯一约束和主键约束的区别．．
： ①UNIQUE 约束用于非主键的一列或列组合；
②一个表可以定义多个UNIQUE 约束，而只能定义一个PRIMAYR KEY 约束；
③UNIQUE 约束可以用于允许空值的列，而PRIMAYR KEY 约束则不能用于允许空值的列。

4．2．5 实现外键约束
1、企业管理器中：打开表设计视图，选择“属性”按钮，打开“关系”选项页，如图4－3所示
图4－3在表设计视图属性对话框设置外键约束
2、定义的T-SQL 语句：
[CONSTRAINT constrain_name] FOREIGN KEY [(字段名[,...n]) ] 从表的列名 REFERENCES ref_table (ref_column[...n]) 主表名和主表中对应的列名 [ ON DELETE {CASCADE|NO ACTION}] [ ON UPDATE {CASCADE|NO ACTION}] 参数说明：
[ ON DELETE {CASCADE|NO ACTION}] ：如果指定CASCADE ，执行级联删除相关记录；如果指定NO ACTION ，则在删除父表中的被引用到的记录时，SQL Server 将返回一个错误操作信息并拒绝删除操作。

[ ON UPDATE {CASCADE|NO ACTION}]：如果指定CASCADE ，执行级联更新相关记录；如果指定NO ACTION ，则在更新父表中的被引用到的记录时，SQL Server 将返回一个错误操作信息并拒绝更新操作。

[ NOT FOR PLICATION]：数据通过复制添加到数据表时，不执行外键约束。

3、创建表时设置外键约束
例5 若已有员工表（员工编号，员工姓名）其中“员工编号”是主键，接下来定义项目表（项目编号，项目负责人），那么“项目负责人”就可以定义一个外键约束，它与“员工表”
主键的“员工编号”进行关联。

项目负责人 char(6) null CONSTRAINT FK_dno FOREIGN KEY REFERENCES 员工表 (员工编号)
或者 项目负责人 char(6) null FOREIGN KEY REFERENCES 员工表 (员工编号) 4、修改表时添加外键约束 ALTER TABLE 项目表
ADD CONSTRAINT FK_pname FOREIGN KEY （项目负责人） REFERENCES 员工表 (员工编号) 5、说明
①级联更新相关记录：当主键表中的主键值被修改后，系统将自动强制的修改外键表中所有外键值与主键表中被修改主键值相同的行，以保证表间的参照完整性。

②级联删除相关记录：当主键表中的主键值所在行被删除后，系统将自动强制的删除外键表中所有外键值与主键表中被删除主键值相同的行，以保证表间的参照完整性。

③一个表可以有多个外键约束。

④外键约束只能引用被引用表中为主键（PRIMARY KEY ）或者唯一约束（UNIQUE ）的列或者被引用表中在唯一索引（UNIQUE INDEX ）内引用的列。

⑤主键和外键的数据类型和长度必须一致。

⑥定义表间参照关系：先定义主表的主键，再对从表定义外键约束。

4．2．6 实现核查约束
1、企业管理器中：打开表设计视图，选择“属性”按钮，打开“CHECK 约束”选项页，如图4－4所示
图4－4 在表设计视图属性对话框设置CHECK 约束
2、定义的T-SQL 语句：
[CONSTRAINT constraint_name] CHECK (logical_expression) 参数说明：
logical_expression ：逻辑表达式返回TRUE 或 FALSE 3、创建表时设置核查约束
例 6 月薪 smallmaney null
CONSTRAINT CK_esa CHECK (月薪>=1000 AND 月薪<=2000)
或者 月薪 smallmaney null CHECK (月薪>=1000 AND 月薪
<=2000)
4、修改表时添加核查约束
例7 ALTER TABLE 员工表
ADD CONSTRAINT CK_no CHECK (员工编号like ‘[A~Z][0~9][0~9][0~9]’)
将员工编号限定为4位，其中第一位的取值范围是’A’~’Z’或者’a’~’z’，其他位为’0’~’9’，输入其他符号被认为无效。

5、注意
①列可以有任意多个核查约束，并且约束条件中可以包含用AND和OR组合起来的多个逻辑表达式。

②约束条件必须取值为布尔表达式，并且不能引用其他表。

③使用T-SQL修改核查约束，必须先删除已有的核查约束，然后再重新定义。

4．2．7 实现默认约束
1、在企业管理器中，在表设计视图中实现。

如图4－5所示
图4－5 在表设计视图设置默认约束
2、创建表时设置默认约束
例7 性别char(2) notnull DEFAULT ‘男’
3、修改表时添加默认约束
例8 ALTER TABLE 员工表
ADD CONSTRAINT Default_sex DEFAULT ‘男’FOR 性别
4．2．8 默认对象
1、默认是一种数据库对象，可以绑定到数据表的一列或多列上，产生与DEFAULT约束相同的作用。

2、默认和DEFAULT约束不同点：
①DEFAULT约束在创建表或修改表时定义，嵌入到被定义的表的结构中，删除表时DEFAULT约束也被删除。

②默认对象作为一种单独存储的数据库对象，它独立于表之外，需要用特定的语句进行定义
和删除。

删除表时不能删除默认对象。

3、在企业管理器中创建和应用默认对象
创建默认：
在数据库所在的树型目录中选中“默认值”，点右键选择新建默认值，输入默认对象的名称和默认值，确定。

如图4－6所示
图4－6 创建默认对话框
应用默认：
选中已创建的默认对象，点右键选择属性，如图4－7所示。

点击帮定列，出现如图4－8所示对话框，选择绑定的表和对应的列。

可选多个表和多个列。

图4－8 绑定默认到列的对话框
4、在企业管理器中删除默认对象
要删除已经应用的默认对象，必须先解除所绑定的列，再将默认对象删除。

解除绑定
选中默认对象，打开图4－8所示的对话框，删除已帮定列。

删除默认
解除绑定列后，选中默认对象，点右键删除即可。

5、用T-SQL 语句创建、绑定、删除默认对象
①创建默认：CREATE DEFAULT default_name AS constant_expression 参数说明：
default_name ：默认对象名称；
constant_expression ：默认对象的表达式 ① 绑定列：
sp_bindefault [@defname=]’default_name ’,[@objname=]’object_name ’ 参数说明：
[@defname=]’default_name ’：默认名称；
[@objname=]’object_name ’：绑定默认值列的名称。

格式为：“表名.列名” ③解除帮定列：sp_unbindefault [@objname=]’object_name ’ ④删除默认：DROP DEFAULT {default_name}[…n]
4．2．9 设置规则
1、规则是一种数据对象，作用类似于CHECK 约束，在插入数据行时，指定数据值的范围。

2、规则与CHECK 约束的区别：
①CHECK 约束是在创建表或修改表时定义，嵌入被定义的表结构，在删除表时同时CHECK 约束也被删除；一个列可以有多个CHECK 约束。

②规则是独立存储的数据对象，需要用特定的语句定义和删除，删除表时不能删除规则。

一个列只能有一个规则，但一个规则可应用于多个列。

3、在企业管理器中创建和应用规则
创建规则：
在数据库所在的树型目录中选中“规则”，点右键选择新建规则，输入规则的名称和逻辑表达式，确定。

如图4－9所示
图4－9 创建规则对话框
应用规则：
选中已创建的规则，点右键选择属性，如图4－10所示。

点击帮定列，出现如图4－11所示对话框，选择绑定的表和对应的列。

可选多个表和多个列。

图4－11 绑定规则到列的对话框
4、在企业管理器中删除规则
要删除已经应用的规则，必须先解除所绑定的列，再将规则删除。

解除绑定
选中规则，打开图4－11所示的对话框，删除已帮定列。

删除默认
解除绑定列后，选中规则，点右键删除即可。

5、用T-SQL 语句创建、绑定、删除规则
①创建规则：CREATE RULE rule_name AS condition_expression 参数说明：
rule_name ：规则名称；
constant_expression ：规则条件表达式。

可以是内置函数、算术表达式、数学表达式或者组合，表达式中必须包含一个变量，变量前面都要有@符号。

绑定列：
sp_bindrule [@rulename=]’rule_name ’,[@objname=]’object_name ’ 参数说明：
[@defname=]’default_name ’：默认名称；
[@objname=]’object_name ’：绑定默认值列的名称。

格式为：“表名.列名” ③解除绑定列：sp_unbindrule [@objname=]’object_name ’ ④删除默认：DROP RULE { rule_name }[…n]
4．2．10 创建关系图
关系是表之间的连接，用一个表的外键连接一个表的主键。

如果强制表之间的引用完整性，则关系线在数据库关系图中以一根实线表示；如果INSERT 和UPDATE
不强制引用
完整性，则以虚线表示。

关系线的终结点显示一个主键符号以表示主键表，无穷符号表示外键表。

创建关系图的方法：在企业管理器中利用向导创建关系图。

①在企业管理器左边的目录树结构中打开要创建关系图的数据库的节点，选中“关系”，点右键选择“新建数据库关系图”，打开“创建数据库关系图向导”窗口，如图4－12所示
图4－12 “创建数据库关系图向导”窗口
②单击【下一步】，打开“选择要添加的表”窗口，如图4－13所示。

将要建立关系的表从左边的列表框选到右边的列表框。

图4－13 “选择要添加的表”窗口
③单击【下一步】，打开，如图4－14所示
图4－14 “正在完成数据库关系图向导”对话框
④点击【完成】出现刚才创建的关系图，如图4－15所示
表示表与表之间关系
的关系线
如图4－15 创建好的关系图
说明：
⏹已经建立好的连接也可以进行修改，选中“关系线”点右键在出现的对话框中选择“从
数据库中删除关系”，然后在重新建立关系。

⏹如果创建表的时候没有设置外键，表与表之间的关系线就不能自动创建，需要手动连接。

⏹也可以通过建立关系图的这个方法来设置外键约束。

选中“关系线”点右键在出现的对
话框中选择“属性”，出现设置外键约束的对话框如图4－16所示，进行连接字段调整。

⏹关系图可以表现多张表之间的关系以及它们之间的连接字段。

图4－16 设置外键约束的对话框
⑤最后给关系图命名并保存。

4．3 上机内容
4．3．1 在企业管理器中管理数据表实现数据完整性
1、将Company数据库文件附加到SQL SERVER服务器上。

（在企业管理器中操作）
2、打开上次练习创建的表：“员工情况表”，删除所有的数据即清空数据表。

3、打开该表的设计视图，为该表设置主键（编号）。

4、在“性别”列上设置核查约束只能输入“男”或者“女”。

5、增加一个字段“身份证号”，要求实现该字段最多只能输入18个字符并设置唯一约束。

6、再创建一张表“仓库借物表”，包含的字段如下，数据类型根据需要自定义。

仓库借物表（借物编号，管理员编号，所借物品名称，借物数量，借物时间，规还时间）
7、为“仓库借物表”设置主键。

主键为：
8、为“仓库借物表”设置外键约束实现数据参照完整性。

（提示：设置外键的字段为“员工情况表”和“仓库借物表”的连接字段）
9、为字段组合“借物编号，管理员编号，所借物品名称”设置唯一约束
10、创建默认对象DEF_SALARY，值为2500。

将该默认对象绑定到“员工情况表”的“工资”字段。

11、创建规则RU_NO，实现的功能是可限定编号为4位，其中第一位的取值范围是’A’~’Z’或者’a’~’z’，其他位为’0’~’9’。

将该规则绑定到“员工情况表（编号）”和“仓库借物表（借物编号/管理员编号）”的编号类字段。

12、分别向两张表输入三条记录。

（注意记录输入的顺序，先操作主键表，再操作外键表。

要实现数据完参照整性）
4．3．2在查询分析器中管理数据表实现数据完整性
1、在Company数据库中，增加一张表：物品表。

字段如下，数据类型根据需要自定义。

物品表（物品编号，物品名称，类别，单价，计量单位，供应商）
要求：①设置主键②为“物品名称”设置唯一约束③为“类别”设置默认约束“其他”
2、在“物品表”中增加一个字段“数量”，并为其设置核查约束，使它的取值范围在0～5000
3、在“仓库借物表”增加外键约束FK_OBJ实现数据完整性。

（提示：设置外键的字段为“物品表”和“仓库借物表”的连接字段）
4、将JXGL数据库文件附加到SQL SERVER服务器上。

5、创建默认对象DE_sex，默认值为“男”。

将该默认对象绑定到Students表的ssex列。

6、创建规则RU_ credit，限定输入的值必须大于0。

将该规则绑定到Course表的credit列。

7、删除第5，6题创建的默认对象和规则。

（提示：先解除绑定再删除对象）
4.4 补充练习
1、创建SC表，包含字段如下SC(sno,cno,grade)，创建表的过程中设置主键。

其中sno－学号，cno－课程号，grade－成绩。

数据类型根据需要自定义。

提示：在设计字段的数据类型时应注意该表分别与Students表和Course表有关系，其中关联字段数据类型必须一致。

2、是否有必要为该表的字段设置唯一约束，为什么？
3、创建关系图S_C_S用来表示Students表、Course表、SC表三张表之间的关系。

4、根据创建好的关系图，说明这三张表中有那几个字段设置了外键约束。

4．5 上机结果
在D:\新建一个文件夹，命名为“你的学号”＋“你的姓名”。

将上机过程中使用过的数据库文件和T-SQL命令全部保存在该文件夹中，命名任意。

最后将所有内容压缩上传。

（注意：移动数据库文件之前，要先从数据库服务器上分离数据库）将对应的语句记录在题目的空白处。

4．6 课后练习
Readers表（rno，rname，idcard，spet，dept，bday）
Lend_Return表（rno，bookid，lenddate，retuendate，fine）
说明：
在Readers表中rno－读者编号，rname－读者姓名，idcard－身份证号，spet－专业，dept －系别，bday－出生日期
在Lend_Return表中rno－读者编号，bookid－图书编号，lenddate－借书日期，retuendate －还书日期，fine－罚金
1、用T-SQL语句创建Readers表（读者信息表）和Lend_Return表（借阅表），
字段如上，数据类型自定义。

同时为这两张表设置主键，为Readers表的“idcard”（身份证号）设置唯一约束
注意：区分字段级约束和表级约束
2、修改Readers表增加一个字段rsex(性别)，为该字段设置CHECK约束，只能输入“男”或者“女”。

注意逻辑表达式的格式
3、修改Readers表，为“spet”（专业）增加默认约束，默认值为“计算机应用”
4、为Lend_Return表设置外键约束，将该表与Readers表联接起来，实现参照完整性。

注意连接的表以及字段
5、创建规则RU_money，将它绑定到“Lend_Return表”的“fine”(罚金)字段，使得该字段不能取负值。

6、创建默认对象DEF_FINE，将它绑定到“Lend_Return表”的“fine”(罚金)字段，使得该字段默认值为0
7、向两张表各输入一条记录。

注意输入记录的顺序
8、删除规则RU_money。

9、删除默认对象DEF_FINE。

10、删除Lend_Return表中第7题添加的记录。