SQLServer与数据库应用开发 第5章 数据的完整性

1、sql server与数据库应用开发与数据库应用开发第第5 5章章 数据的完整性数据的完整性5.1 完整性的概念与分类 5.2 约束的类型 5.3 约束的创建 5.4 查看约束 5.5 删除约束 5.6 使用规则 5.7 使用默认 主要内容主要内容【知识要求】： 1.掌握数据完整性的涵义 2.掌握各类约束与完整性对应关系【能力要求】： 1.熟练掌握各种约束的创建方法 2.熟练掌握约束的查看、修改、删除等管理方法知识与能力要求知识与能力要求5.1.1数据完整性的概念数据完整性是指存储在数据库中的数据的正确性、一致性与可靠性。如果数据库中存储有不一致和违背语义的数据，则该数据库称为已丧失数据的完整

2、性。例如：在数据库表中出现完全重复的记录而造成数据冗余，如果人的“年龄”值设置为2000，虽然值正确，但已经没有意义。考试系统中，没有学生基本信息，却出现了该学生的考试成绩等。都属于数据异常或者不一致，都违反了数据完整性。5.1.2数据完整性的分类数据完整性根据其实现的目标可分为：实体完整性、域完整性、参照完整性及用户自定义的完整性1实体完整性 实体完整性（entity integrity）规定了同一表中的每一行记录在表中是唯一的。也可以这样说，在同一表中不能存在完全相同的记录。只有保证了任何记录都是不重复的，可以区分的，在对数据进行操作时才可以和其他记录区分开。例如，要对“用户”表中姓名为“

3、刘杰”的记录进行更改，更新操作只能针对姓名为“刘杰” 这条记录，那么在操作的时候就需一种方式来指向确定的记录。5.1.5.1.数据完整性的概念与分类数据完整性的概念与分类2域完整性域完整性（domain integrity）是指数据库表中的字段值必须满足某种特定的数据类型或范围、精度等规定。例如，在“用户”表中，“用户编号”字段内容只能填入规定长度的学号，而“用户角色”字段只能填入“学生”、“教师”或“管理员” ，试卷表中“考试日期”只能填入日期型数据。3参照完整性参照完整性（referential integrity）是指的是两个表中记录之间的对应关系。它保证了表之间数据的一致性，防止了数据

4、丢失或无意义的数据在数据库中扩散。例如，在“班级”表中各记录的“系部编号”列的值必须是在“系部”表中“系部编号”列存在的值，也就是说，班级必须是系里存在的班级，这时候“系部”表就称为主表， “班级”表称为从表。 “系部编号”就是从表“班级”相对于主表“系部”表的参照列。在sql server 2012中，参照完整性的根本要求是从表中不能出现主表没对应值的记录。以下三种会操作导致这种结果：（1）给从表中插入记录时，主表中没有对应的记录。（2）从表中已经存在了对应记录，但单独修改了主表中从表参照的列值。（3）删除了主表中的记录，导致从表中已经存在的对应记录无法参照。5.1.5.1.数据完整性的概念

5、与分类数据完整性的概念与分类4用户定义的完整性不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性（user-defined integrity）即是针对某个特定关系数据库的约束条件，它反映了某一具体应用所涉及的数据必须满足的语义要求。sql server 2012提供了定义和检验这类完整性的机制，以便用统一的系统方法来处理它们，例如触发器，而不是用应用程序来承担这一功能。其他的完整性类型都支持用户定义的完整性。5.1.5.1.数据完整性的概念与分类数据完整性的概念与分类约束是强制数据库实现完整性的方式和机制。通过建立约束可以实现完整性所要求的效果。约束就是

6、一种强制性的规定，在sql server 中提供的约束是通过定义字段的取值规则来维护数据完整性的。严格说来，在sql server 中支持六类约束：not null（非空）约束、check（检查）约束、unique（唯一）约束、primary key（主键）约束.foreign key（外键）约束和default（默认）约束。下面分别进行介绍。1primary key 约束 将哪些值唯一的列或者列的集合定义为主键约束，这些列称为主键列。主键列的值不能是空值。每个表都应有一个主键，保证记录的唯一性，也就实现了实体的完整性。一个表中只有一个主键约束。例如，要在“用户”表中区分每一个用户，区分的唯一

7、标志不是用户姓名，而是每个用户唯一对应的“用户编号”值，“用户编号”在表中就应该设为主键。2unique约束唯一约束与主键约束相似，它保证了非主键列值的唯一性。唯一约束允许一个空值。主键约束优先于唯一索引。例如，在“系部”表中可以将“系部名称”作为唯一性，用来保证记录的唯一性 。一个表中可以有多个唯一约束。5.25.2约束的概念与分类约束的概念与分类3not null约束非空约束用来强制数据的域完整性，它用来设定某列值不能为空。例如，在“学生”表中，“学号”字段值不能为空，在插入记录的时候这个字段里必须有值存在。4check 约束 check约束指定列中输入值的布尔（取值为true或false

8、）搜索条件，拒绝所有结果为false的值。可以为每列指定多个check约束。 check约束可保证域的完整性5default约束 利用默认值可以为未填入值的列强制填入一个默认情况下的值。例如，对“用户”表中的角色列，如未填入任何值则可把角色默认填入“学生”。6foreign key约束标识表之间的关系 如果一个表的数据依赖于另外一个表的数据，那就需要定义外键约束，以免数据的不一致。外键是在一个表中的一个列或多个列，它不是该表的主键，但是它是其他表的主键。这时，主键所在的表是主表，外键所在的表是从表。外键约束保证主表中的主键列与从表中的外键列的值是一致的、正确的。例如，在“tb_class”表中

9、，“dept_num”字段的值不是该表的主键，而它却是“tb_dept”表的主键,两个表就存在参照关系。5.25.2约束的概念与分类约束的概念与分类5.3.1创建主键约束 1.1.用对象资源管理器创建主键约束用对象资源管理器创建主键约束 下面以用户表“tb_depttb_dept”为例，介绍使用对象资源管理器创建主键约束的操作步骤： （1）创建好tb_user后，在“对象资源管理器”窗格中依次展开“服务器”、“数据库”、“testdb”、“表”结点。找到需要修改的表名（这里为“tb_dept”表），右击该表，在弹出的快捷菜单中选择“设计”命令，如图5-1所示。 （2）在“表设计器”窗口中，选择

10、需要设为主键的字段，如果需要选择多个字段，可按住【ctrl】键再选择其他列。 （3）选择好后，右击该字段，从弹出的快捷菜单中选择“设置主键”命令，如图5-2所示，或单击工具栏 （4）执行完命令后，在该列前面会出现钥匙图样，说明主键设置成功，如图5-3所示。 （5）设置完成主键后，关闭“表设计器”窗口。注意：因为主键是唯一的，所以，如果表中的列原来有数据，并且数据有重复，那么在设置主键时会出现错误。5.35.3约束的创建约束的创建5.3 5.3 约束的创建约束的创建图5-1 选择“设计”命令图5-2 选择“设计主键”命令图5-3 主键设置成功2. 2. 使用使用sqlsql语句创建主键约束语句创

11、建主键约束下面分别使用建表sql命令和修改表命令创建主键约束。【案例5-1】在testdb数据库中，建立“班级”表tb_class，将“class_num”设置为主键。表结构如表5-1所示。 5.3 5.3 约束的创建约束的创建字段名称数据类型（宽度）允许空值约束说明class_numnvarchar(10)否主键班级编号class_namenvarchar(50)是班级名称dept_numnvarchar(10)是系部编号表5-1 班级表tb_class的表结构 创建表时创建主键的代码如下：gocreate table tb_class(class_num nvarchar(10) cons

12、traint pk_num primary key not null,class_name nvarchar(50) null,dept_num nvarchar(10) null, ) go5.3 5.3 约束的创建约束的创建5.3 5.3 约束的创建约束的创建【案例5-1】在创建班级表 “tb_class”时没有指定主键，可修改班级表 “tb_class”，将“class_num”设置为主键。代码如下：alter table tb_classadd constraint pk_num primary key clustered (class_num)go注意：如果表中已经在class_nu

13、m上建立主键，则使用企业管理器先移除主键，否则会提示错误。创建好主键后可使用企业管理器查看表结构来查看主键。5.3.25.3.2创建唯一约束创建唯一约束 在一张数据表中，有时除主键需要具有唯一性外，还有其他列也需要具有唯一性。例如，在系部表中tb_dept，主键为“dept_num”，但是另外一个字段dept_name虽不是主键，也需保证它的唯一性，这时就需要创建表中的唯一约束。1使用对象资源管理器创建唯一约束下面以系部表“tb_dept”为例，为“dept_name”字段创建唯一约束。操作步骤如下： （1）在“对象资源管理器”窗格中，右击需要设置唯一约束的表tb_dept,在弹出的快捷菜单中

14、选择“设计”命令，打开“表设计器”窗口。 （2）在“表设计器”窗口中，右击需要设置为唯一约束的字段dept_name，在弹出的快捷菜单中选择“索引/键”命令，如图6-7所示，也可以直接单击工具栏中的“管理索引和键”按钮，打开“索引/键”对话框，如图6-8所示。5.3 5.3 约束的创建约束的创建（2）在“表设计器”窗口中，右击需要设置为唯一约束的字段dept_name,在弹出的快捷菜单中选择“索引/键”命令，如图5-4所示，也可以直接单击工具栏中的“管理索引和键”按钮，打开“索引/键”对话框，如图5-5所示。5.3 5.3 约束的创建约束的创建图5-4 选择“索引/键”命令图5-5 “索引/键

15、”对话框（3）在打开的“索引/键”对话框中，单击“添加”按钮，结果如图5-6所示。（4）设置好相关选项后，单击“关闭”按钮，完成唯一约束的创建。这时，不只是该表的主键必须为唯一，并且被设置为唯一约束的字段同样也必须为唯一。5.3 5.3 约束的创建约束的创建图5-6 创建唯一约束 2使用sql语句创建唯一约束 使用sql语句创建唯一约束，可以用create table命令在创建表的同时完成，为已经存在的表创建唯一约束，其语法格式如下：alter table table_nameaddconstraint constraint_nameunique clustered|nonclustered(

16、column,n)其中： table_name为需要创建唯一约束的表名称；constraint_name为唯一约束的名称；column是表中需要创建唯一约束的字段名称 5.3 5.3 约束的创建约束的创建【案例5-3】在创建testdb数据库的班级表tb_class的同时，为class_name字段创建唯一约束。之后，可视化移除唯一键，再通过修改表添加唯一键。代码如下：gocreate table dbo.tb_class(class_num nvarchar (10) not null,class_name nvarchar (50) constraint uk_name unique nu

17、ll,dept_num nvarchar (10) null) go5.3 5.3 约束的创建约束的创建use testdbgoalter table tb_classadd constraint uk_nameunique nonclustered (class_name)go注意：如果表中已经在class_name上建立唯一键，则使用企业管理器先移除唯一键，否则会提示错误。执行完上述sql语句后，可以打开“索引/键”对话框重新查看该表的唯一约束，如图5-7所示。可以看到，刚才的sql语句为class_name字段创建了一个名称为uk_name的唯一约束。5.3 5.3 约束的创建约束的创建

18、图5-7 表的唯一约束5.3.3创建检查约束 检查约束对输入的数据的值做检查，可以限定数据输入，从而维护数据的域完整性。例如，对试卷设置表“tb_setpaper”中“s_v”字段的内容，只允许为15分，不允许小于1分的分值和大于5分的分值出现。可以利用对象资源管理器或sql语句来创建检查约束。1使用对象资源管理器创建检查约束 下面以“tb_setpaper”表为例，介绍如何对“分值”字段内容创建检查约束。操作步骤如下：（1）在“对象资源管理器”窗格中，右击需要设置唯一约束的表“tb_setpaper”，在弹出的快捷菜单中选择“设置”命令，打开“表设计器”窗口。（2）在“表设计器”窗口中右击需

19、要创建检查约束的字段“s_v”，在弹出的快捷菜单中选择“check约束”命令，如图5-8所示，打开“check约束”对话框。（3）在“check约束”对话框中，单击“添加”按钮，然后在“(名称)”文本框中输入检查约束名称，在约束“表达式”文本框中输入约束条件，这里输入“s_v 0 and s_v0 and s_v0 and m_v0 and j_v5)go 5.3 5.3 约束的创建约束的创建5.3.4创建默认约束 在用户输入某些数据时，希望一些数据在没有特例的情况下被自动输入，例如，对于一个学生管理数据库表，学生的注册日期应该是数据录入的当天日期；学生的修学年限是固定的值；学生性别默认是“男

20、”等情况，这个时候需要对数据表创建默认约束。 下面分别用例子说明如何在对象资源管理器中和利用sql语句创建默认约束。 1使用对象资源管理器创建默认约束 以“tb_user”表为例，在“user_role”字段创建默认为“学生”的默认约束。操作步骤如下： （1）在“对象资源管理器”窗格中，右击需要创建默认约束的表“tb_user”，在弹出的快捷菜单中选择“设计”命令，打开“表设计器”窗口。 （2）选择需要创建默认约束的字段user_role，然后在下方的“列属性”选项卡中的“默认值或绑定”文本框中输入默认值 “学生”，如图5-10所示。 注意注意：单引号不需要输入，在表保存后，在单引号外还会自动

21、生成一对小括号。 （3）关闭“表设计器”窗口。 5.3 5.3 约束的创建约束的创建5.3 5.3 约束的创建约束的创建图5-10 创建默认值2. 2. 使用使用sqlsql语句创建默认约束语句创建默认约束在创建表的同时，可以对创建的表中的字段创建默认约束，如【案例6-6】。【案例5.6】在testdb数据库中新建试卷表“testpaper”表，并将“testdate”设置为当前日期。代码如下：create table dbo.tb_testpaper(user_num nvarchar(20) not null,cos_num nvarchar(10) not null,question_t

22、ype nvarchar(8)not null,qinpaper_num int null ,qindb_num int null,answer nchar(8) null,testdate datetime not null default getdate()go5.3 5.3 约束的创建约束的创建使用sql语句同样可以为已存在的表创建默认约束。其语法格式如下：alter table table_nameadd constraint constraint_namedefault constraint_expressionfor column_name其中： table_name是需要创建默认

23、约束的表名称。 constraint_name是默认约束名称。 constraint_expression是默认值。 for column_name是需要创建默认约束的字段名称。5.3 5.3 约束的创建约束的创建【例5.7】在testdb数据库中的试卷设置表“tb_setpaper”中，为“testdate”字段创建默认值为当前日期的默认约束。代码如下：use testdbgoalter table tb_setpaperadd constraint df_datedefault getdate() for testdatego5.3 5.3 约束的创建约束的创建5.3.5创建外键约束 外键

24、是用来维护表与表之间对应唯一关系的一种方法。可以利用对象资源管理器或sql语句来创建外键约束。 1使用对象资源管理器创建外键约束 下面以班级表“tb_class”为例，为系部列“dept_num”创建外键约束。操作步骤如下： （1）在“对象资源管理器”窗格中，右击需要创建外键约束的表tb_class，在弹出的快捷菜单中选择“设计”命令，打开“表设计器”窗口。 （2）选择需要创建外键约束的字段“dept_num”，单击工具栏中的“关系”按钮，或右击该字段，在弹出的快捷菜单中选择“关系”命令，打开“外键关系”对话框，如图5-11所示。 （3）在“外键关系”对话框中，单击“添加”按钮，然后单击“表和

25、列规范”的按钮，打开“表和列”对话框。在“主键表”下拉列表中选择系部表 “tb_dept”，由于是在班级表“tb_class” 上创建外键，外键表是不可选的，分别在“主键表”和“外键表”的下面选择“dept_num”字段，如图5-12所示。 （4）单击“确定”按钮，然后在“外键关系”对话框中进行相关设置后单击“关闭”按钮即可。 5.3 5.3 约束的创建约束的创建5.3 5.3 约束的创建约束的创建图5-11 创建外键图5-12 选择“主表”与“参照列”2. 使用sql语句创建外键约束使用sql语句创建外键约束的语法格式为： alter table table_name add contrai

26、nt constraint_name foreign key(column_name,n) references ref_table(ref_column_name,n)其中： table_name是需要创建外键约束的表名称。 constraint_name是外键约束名称。5.3 5.3 约束的创建约束的创建【案例5.8】在testdb数据库中的用户表“tb_user”表中，为系部编号“dept_num”字段创建一个外键约束，从而保证输入有效的系部编号。代码如下：use testdbgoalter table tb_useradd constraint fk_xbbhforeign key (

27、dept_num)references tb_dept(dept_num)5.3 5.3 约束的创建约束的创建对于创建好的约束，根据实际需要可以查看其定义信息。sql server 提供了多种查看约束信息的方法，主要有使用对象资源管理器和系统存储过程查看两种方法。 1 1利用对象资源管理器查看约束信息利用对象资源管理器查看约束信息 使用对象资源管理器查看约束信息的操作步骤如下：（1）在“对象资源管理器”窗格中，右击要查看约束的表，在弹出的快捷菜单中选择“设计”命令，打开“表设计器”窗口。（2）右击该表，在弹出的快捷菜单中分别选择“关系”、“索引/键”、“check约束”等命令查看约束信息，如图

28、5-13所示。 5.45.4查看约束的定义查看约束的定义 5.45.4查看约束的定义查看约束的定义图5-13 可视化方式查看约束图5-14 用户存储过程查看约束2 2利用存储过程查看约束信息利用存储过程查看约束信息存储过程sp_helptext是用来查看约束的一个系统提供的存储过程，可以通过查询分析器来查看约束的名称、创建者、类型和创建时间。其语法格式为：exec sp_help 约束名称如果该约束有具体的定义和文本，那么可以用sp_helptext来查看。其语法格式为：exec sp_helptext 约束名称【案例5.9】使用系统存储过程查看testdb 数据库中定义的ck_s_v约束信息

29、和文本信息。代码如下，结果如图5-14所示。use testdb goexec sp_help ck_s_vgoexec sp_helptext ck_s_vgo5.45.4查看约束的定义查看约束的定义 前面讲了约束如何建立，约束在建立后可能根据实际情况需要删除，可以使用对象资源管理器来删除约束，也可以使用sql语句来删除约束。1用对象资源管理器来删除表约束 使用对象资源管理器删除约束非常方便，正如在建立约束时一样，只需要在“表设计器”窗口中，将 “设置主键”前的复选框取消即可删除主键约束，或删除默认值以删除默认约束；如图5-7所示，单击“删除”按钮删除唯一约束；如图5-9所示，单击“删除”按

30、钮删除检查约束；如图5-11所示，单击“删除”按钮删除外键约束。2使用drop命令删除表约束 利用sql语句也可以方便地删除一个或多个约束。其语法格式如下： alter table table_name drop constraint constraint_name,n5.55.5删除约束删除约束 【案例5.10】删除“tb_setpaper”表中的check约束ck_s_v与ck_m_v。代码如下：use testdb goalter table tb_setpaperdrop constraint ck_s_vgo alter table tb_setpaperdrop constrain

31、t ck_m_vgo 5.55.5删除约束删除约束 规则类似于check约束，是用来限制数据字段的输入值的范围，实现强制数据的域完整性。但规则不同于check约束，在前面用到的check约束可以针对一个列应用多个check约束，但一个列不能应用多个规则；规则需要被单独创建，而check约束在创建表的同时可以一起创建；规则比check约束更复杂功能更强大；规则只需要创建一次，以后可以多次应用，可以应用于多个表多个列，还可以应用到用户定义的数据类型上。 使用规则包括规则的创建、绑定、解绑和删除。可以在查询分析器中用sql语句完成。1创建规则 规则作为一种数据库对象，在使用前必须被创建。创建规则的s

32、ql命令是create rule。其语法格式如下： create rule rule_name as condition_expression其中： rule_name是规则的名称，命名必须符合sql server 的命名规则。 condition_expression是条件表达式。5.6 5.6 使用规则使用规则 2绑定规则要使创建好的规则作用到指定的列或表等，还必须将规则绑定到列或用户定义的数据类型上才能够起作用。在查询分析器中，可以利用系统存储过程将规则绑定到字段或用户定义的数据类型上。其语法格式如下：execute sp_bindrule 规则名称,表名.字段名|自定义数据类型名【案例

33、6.11】创建一个xb_rule规则，将它绑定到“tb_user”表的“usr_role”字段，保证输入数据只能为“学生”或“教师”。代码如下：use testdb gocreate rule role_ruleasrole in(学生,教师)goexec sp_bindrule role_rule,tb_user.user_rolego 5.6 5.6 使用规则使用规则3解绑规则如果字段已经不再需要规则限制输入了，那么必须把已经绑定了的规则去掉，这就是解绑规则。在查询分析器中，同样用存储过程来完成解绑操作。其语法格式如下：execute sp_unbindrule 表名.字段名|自定义数据类型名4删除规则如果规则已经没有用了，那么可以将其删除。在删除前应先对规则进行解绑，当规则已经不再作用于任何表或字段等时，则可以用drop rule删除一个或多个规则。其语法格式如下： drop rule 规则名称,n【