《SQLServer与数据库应用开发 第5章 数据的完整性.ppt》由会员分享,可在线阅读,更多相关《SQLServer与数据库应用开发 第5章 数据的完整性.ppt(46页珍藏版)》请在三一办公上搜索。
1、SQL Server与数据库应用开发,第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.数据完整性的概念与分类,2域完整性域完整性(domain integrity)是指数据库表中的字段值必须满足某种特定的数据类型或范围、精度等规定。例如,在“用户”表中,“用户编号”字段内容只能填入规定长度的学号,而“用户角色”字段只能填入“学生”、“教师”或“管理员”,试卷表中“考试日期”只能填入日期型数据。3参照完整性参照完整性(referential integrity)是指的是两个表中记录之间的对应关系。它保证了表之间数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。例如,在“班级”表中各记录的“系部编号”列的值必
4、须是在“系部”表中“系部编号”列存在的值,也就是说,班级必须是系里存在的班级,这时候“系部”表就称为主表,“班级”表称为从表。“系部编号”就是从表“班级”相对于主表“系部”表的参照列。在SQL Server 2012中,参照完整性的根本要求是从表中不能出现主表没对应值的记录。以下三种会操作导致这种结果:(1)给从表中插入记录时,主表中没有对应的记录。(2)从表中已经存在了对应记录,但单独修改了主表中从表参照的列值。(3)删除了主表中的记录,导致从表中已经存在的对应记录无法参照。,5.1.数据完整性的概念与分类,4用户定义的完整性不同的关系数据库系统根据其应用环境的不同,往往还需要一些特殊的约束
5、条件。用户定义的完整性(user-defined integrity)即是针对某个特定关系数据库的约束条件,它反映了某一具体应用所涉及的数据必须满足的语义要求。SQL Server 2012提供了定义和检验这类完整性的机制,以便用统一的系统方法来处理它们,例如触发器,而不是用应用程序来承担这一功能。其他的完整性类型都支持用户定义的完整性。,5.1.数据完整性的概念与分类,约束是强制数据库实现完整性的方式和机制。通过建立约束可以实现完整性所要求的效果。约束就是一种强制性的规定,在SQL Server 中提供的约束是通过定义字段的取值规则来维护数据完整性的。严格说来,在SQL Server 中支持
6、六类约束:NOT NULL(非空)约束、CHECK(检查)约束、UNIQUE(唯一)约束、PRIMARY KEY(主键)约束.FOREIGN KEY(外键)约束和DEFAULT(默认)约束。下面分别进行介绍。1PRIMARY KEY 约束 将哪些值唯一的列或者列的集合定义为主键约束,这些列称为主键列。主键列的值不能是空值。每个表都应有一个主键,保证记录的唯一性,也就实现了实体的完整性。一个表中只有一个主键约束。例如,要在“用户”表中区分每一个用户,区分的唯一标志不是用户姓名,而是每个用户唯一对应的“用户编号”值,“用户编号”在表中就应该设为主键。2UNIQUE约束唯一约束与主键约束相似,它保证
7、了非主键列值的唯一性。唯一约束允许一个空值。主键约束优先于唯一索引。例如,在“系部”表中可以将“系部名称”作为唯一性,用来保证记录的唯一性。一个表中可以有多个唯一约束。,5.2约束的概念与分类,3NOT NULL约束非空约束用来强制数据的域完整性,它用来设定某列值不能为空。例如,在“学生”表中,“学号”字段值不能为空,在插入记录的时候这个字段里必须有值存在。4CHECK 约束 CHECK约束指定列中输入值的布尔(取值为TRUE或FALSE)搜索条件,拒绝所有结果为FALSE的值。可以为每列指定多个CHECK约束。CHECK约束可保证域的完整性5DEFAULT约束 利用默认值可以为未填入值的列强
8、制填入一个默认情况下的值。例如,对“用户”表中的角色列,如未填入任何值则可把角色默认填入“学生”。6FOREIGN KEY约束标识表之间的关系 如果一个表的数据依赖于另外一个表的数据,那就需要定义外键约束,以免数据的不一致。外键是在一个表中的一个列或多个列,它不是该表的主键,但是它是其他表的主键。这时,主键所在的表是主表,外键所在的表是从表。外键约束保证主表中的主键列与从表中的外键列的值是一致的、正确的。例如,在“tb_class”表中,“dept_num”字段的值不是该表的主键,而它却是“tb_dept”表的主键,两个表就存在参照关系。,5.2约束的概念与分类,5.3.1创建主键约束 1.用
9、对象资源管理器创建主键约束 下面以用户表“tb_dept”为例,介绍使用对象资源管理器创建主键约束的操作步骤:(1)创建好tb_user后,在“对象资源管理器”窗格中依次展开“服务器”、“数据库”、“TestDB”、“表”结点。找到需要修改的表名(这里为“tb_dept”表),右击该表,在弹出的快捷菜单中选择“设计”命令,如图5-1所示。(2)在“表设计器”窗口中,选择需要设为主键的字段,如果需要选择多个字段,可按住【Ctrl】键再选择其他列。(3)选择好后,右击该字段,从弹出的快捷菜单中选择“设置主键”命令,如图5-2所示,或单击工具栏(4)执行完命令后,在该列前面会出现钥匙图样,说明主键设
10、置成功,如图5-3所示。(5)设置完成主键后,关闭“表设计器”窗口。注意:因为主键是唯一的,所以,如果表中的列原来有数据,并且数据有重复,那么在设置主键时会出现错误。,5.3约束的创建,5.3 约束的创建,图5-1 选择“设计”命令,图5-2 选择“设计主键”命令,图5-3 主键设置成功,2.使用SQL语句创建主键约束下面分别使用建表SQL命令和修改表命令创建主键约束。【案例5-1】在TestDB数据库中,建立“班级”表tb_class,将“class_num”设置为主键。表结构如表5-1所示。,5.3 约束的创建,表5-1 班级表tb_class的表结构,创建表时创建主键的代码如下:GOCR
11、EATE TABLE tb_class(class_num nvarchar(10)constraint pk_num PRIMARY KEY NOT NULL,class_name nvarchar(50)NULL,dept_num nvarchar(10)NULL,)GO,5.3 约束的创建,5.3 约束的创建,【案例5-1】在创建班级表“tb_class”时没有指定主键,可修改班级表“tb_class”,将“class_num”设置为主键。代码如下:ALTER TABLE tb_classADD CONSTRAINT pk_num PRIMARY KEY CLUSTERED(class_
12、num)GO注意:如果表中已经在class_num上建立主键,则使用企业管理器先移除主键,否则会提示错误。创建好主键后可使用企业管理器查看表结构来查看主键。,5.3.2创建唯一约束 在一张数据表中,有时除主键需要具有唯一性外,还有其他列也需要具有唯一性。例如,在系部表中tb_dept,主键为“dept_num”,但是另外一个字段dept_name虽不是主键,也需保证它的唯一性,这时就需要创建表中的唯一约束。1使用对象资源管理器创建唯一约束下面以系部表“tb_dept”为例,为“dept_name”字段创建唯一约束。操作步骤如下:(1)在“对象资源管理器”窗格中,右击需要设置唯一约束的表tb_d
13、ept,在弹出的快捷菜单中选择“设计”命令,打开“表设计器”窗口。(2)在“表设计器”窗口中,右击需要设置为唯一约束的字段dept_name,在弹出的快捷菜单中选择“索引/键”命令,如图6-7所示,也可以直接单击工具栏中的“管理索引和键”按钮,打开“索引/键”对话框,如图6-8所示。,5.3 约束的创建,(2)在“表设计器”窗口中,右击需要设置为唯一约束的字段dept_name,在弹出的快捷菜单中选择“索引/键”命令,如图5-4所示,也可以直接单击工具栏中的“管理索引和键”按钮,打开“索引/键”对话框,如图5-5所示。,5.3 约束的创建,图5-4 选择“索引/键”命令,图5-5“索引/键”对
14、话框,(3)在打开的“索引/键”对话框中,单击“添加”按钮,结果如图5-6所示。(4)设置好相关选项后,单击“关闭”按钮,完成唯一约束的创建。这时,不只是该表的主键必须为唯一,并且被设置为唯一约束的字段同样也必须为唯一。,5.3 约束的创建,图5-6 创建唯一约束,2使用SQL语句创建唯一约束 使用SQL语句创建唯一约束,可以用CREATE TABLE命令在创建表的同时完成,为已经存在的表创建唯一约束,其语法格式如下:ALTER TABLE table_nameADDCONSTRAINT constraint_nameUNIQUE CLUSTERED|NONCLUSTERED(column,n
15、)其中:table_name为需要创建唯一约束的表名称;constraint_name为唯一约束的名称;column是表中需要创建唯一约束的字段名称,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 NULL,dept_num nvarchar(
16、10)NULL)GO,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-7 表的唯一约束,5.3.3创建检查约束 检查约束对输入的数据的值做检查
17、,可以限定数据输入,从而维护数据的域完整性。例如,对试卷设置表“tb_setpaper”中“s_v”字段的内容,只允许为15分,不允许小于1分的分值和大于5分的分值出现。可以利用对象资源管理器或SQL语句来创建检查约束。1使用对象资源管理器创建检查约束 下面以“tb_setpaper”表为例,介绍如何对“分值”字段内容创建检查约束。操作步骤如下:(1)在“对象资源管理器”窗格中,右击需要设置唯一约束的表“tb_setpaper”,在弹出的快捷菜单中选择“设置”命令,打开“表设计器”窗口。(2)在“表设计器”窗口中右击需要创建检查约束的字段“s_v”,在弹出的快捷菜单中选择“CHECK约束”命令
18、,如图5-8所示,打开“CHECK约束”对话框。(3)在“CHECK约束”对话框中,单击“添加”按钮,然后在“(名称)”文本框中输入检查约束名称,在约束“表达式”文本框中输入约束条件,这里输入“s_v 0 and s_v 5”,如图5-9所示。,5.3 约束的创建,(4)单击“关闭”按钮关闭对话框,完成检查约束的创建。注意:如果表中原来就有数据,并且数据类型或范围与所创建的约束相冲突,那么约束将不能成功创建。,5.3 约束的创建,5-8 选择“CHECK约束”命令,图5-9 设置“CHECK约束”条件,2使用SQL语句创建检查约束 使用SQL语句在创建表的同时创建检查约束【案例5.4】利用SQ
19、L语句创建“tb_setpaper”表,并且在创建的同时创建检查约束,使选择题的试题分值在15之间。CREATE TABLE dbo.tb_setpaper(cos_num nvarchar(10)NOT NULL,testdate datetime NOT NULL,s_num tinyint NULL,s_v tinyint NULL constraint ck_s_v check(s_v0 and s_v0 and m_v5),j_num tinyint NULL,j_v tinyint)GO,5.3 约束的创建,在已经创建好的表中,也可以用SQL语句对其创建检查约束。其语法格式如下:A
20、LTER TABLE table_nameADD CONSTRAINT constraint_nameCHECK(logical_expression)其中:table_name是需要创建检查约束的表名称。constraint_name是检查约束的名称。logical_expression是检查约束的条件表达式。,5.3 约束的创建,【案例5-5】在“tb_setpaper”表中,添加检查约束,使用判断题的分值在1-5之间。代码如下:USE TestDB GOALTER TABLE tb_setpaperADD CONSTRAINT ck_j_vCHECK(j_v0 and j_v5)GO,5
21、.3 约束的创建,5.3.4创建默认约束 在用户输入某些数据时,希望一些数据在没有特例的情况下被自动输入,例如,对于一个学生管理数据库表,学生的注册日期应该是数据录入的当天日期;学生的修学年限是固定的值;学生性别默认是“男”等情况,这个时候需要对数据表创建默认约束。下面分别用例子说明如何在对象资源管理器中和利用SQL语句创建默认约束。1使用对象资源管理器创建默认约束 以“tb_user”表为例,在“user_role”字段创建默认为“学生”的默认约束。操作步骤如下:(1)在“对象资源管理器”窗格中,右击需要创建默认约束的表“tb_user”,在弹出的快捷菜单中选择“设计”命令,打开“表设计器”
22、窗口。(2)选择需要创建默认约束的字段user_role,然后在下方的“列属性”选项卡中的“默认值或绑定”文本框中输入默认值“学生”,如图5-10所示。注意:单引号不需要输入,在表保存后,在单引号外还会自动生成一对小括号。(3)关闭“表设计器”窗口。,5.3 约束的创建,5.3 约束的创建,图5-10 创建默认值,2.使用SQL语句创建默认约束在创建表的同时,可以对创建的表中的字段创建默认约束,如【案例6-6】。【案例5.6】在TestDB数据库中新建试卷表“testpaper”表,并将“testdate”设置为当前日期。代码如下:CREATE TABLE dbo.tb_testpaper(u
23、ser_num nvarchar(20)NOT NULL,cos_num nvarchar(10)NOT NULL,question_type nvarchar(8)NOT NULL,Qinpaper_num int NULL,QinDB_num int NULL,answer nchar(8)NULL,testdate datetime NOT NULL Default getdate()GO,5.3 约束的创建,使用SQL语句同样可以为已存在的表创建默认约束。其语法格式如下:ALTER TABLE table_nameADD CONSTRAINT constraint_nameDEFAUL
24、T constraint_expressionFOR column_name其中:table_name是需要创建默认约束的表名称。constraint_name是默认约束名称。constraint_expression是默认值。FOR column_name是需要创建默认约束的字段名称。,5.3 约束的创建,【例5.7】在TestDB数据库中的试卷设置表“tb_setpaper”中,为“testdate”字段创建默认值为当前日期的默认约束。代码如下:USE TestDBGOALTER TABLE tb_setpaperADD CONSTRAINT df_dateDEFAULT getdate(
25、)FOR testdateGO,5.3 约束的创建,5.3.5创建外键约束 外键是用来维护表与表之间对应唯一关系的一种方法。可以利用对象资源管理器或SQL语句来创建外键约束。1使用对象资源管理器创建外键约束 下面以班级表“tb_class”为例,为系部列“dept_num”创建外键约束。操作步骤如下:(1)在“对象资源管理器”窗格中,右击需要创建外键约束的表tb_class,在弹出的快捷菜单中选择“设计”命令,打开“表设计器”窗口。(2)选择需要创建外键约束的字段“dept_num”,单击工具栏中的“关系”按钮,或右击该字段,在弹出的快捷菜单中选择“关系”命令,打开“外键关系”对话框,如图5-
26、11所示。(3)在“外键关系”对话框中,单击“添加”按钮,然后单击“表和列规范”的按钮,打开“表和列”对话框。在“主键表”下拉列表中选择系部表“tb_dept”,由于是在班级表“tb_class”上创建外键,外键表是不可选的,分别在“主键表”和“外键表”的下面选择“dept_num”字段,如图5-12所示。(4)单击“确定”按钮,然后在“外键关系”对话框中进行相关设置后单击“关闭”按钮即可。,5.3 约束的创建,5.3 约束的创建,图5-11 创建外键,图5-12 选择“主表”与“参照列”,2.使用SQL语句创建外键约束使用SQL语句创建外键约束的语法格式为:ALTER TABLE table
27、_name ADD CONTRAINT constraint_name FOREIGN KEY(column_name,n)REFERENCES ref_table(ref_column_name,n)其中:table_name是需要创建外键约束的表名称。constraint_name是外键约束名称。,5.3 约束的创建,【案例5.8】在TestDB数据库中的用户表“tb_user”表中,为系部编号“dept_num”字段创建一个外键约束,从而保证输入有效的系部编号。代码如下:USE TestDBGOALTER TABLE tb_userADD CONSTRAINT fk_xbbhFOREIG
28、N KEY(dept_num)REFERENCES tb_dept(dept_num),5.3 约束的创建,对于创建好的约束,根据实际需要可以查看其定义信息。SQL Server 提供了多种查看约束信息的方法,主要有使用对象资源管理器和系统存储过程查看两种方法。1利用对象资源管理器查看约束信息 使用对象资源管理器查看约束信息的操作步骤如下:(1)在“对象资源管理器”窗格中,右击要查看约束的表,在弹出的快捷菜单中选择“设计”命令,打开“表设计器”窗口。(2)右击该表,在弹出的快捷菜单中分别选择“关系”、“索引/键”、“CHECK约束”等命令查看约束信息,如图5-13所示。,5.4查看约束的定义,
29、5.4查看约束的定义,图5-13 可视化方式查看约束,图5-14 用户存储过程查看约束,2利用存储过程查看约束信息存储过程sp_helptext是用来查看约束的一个系统提供的存储过程,可以通过查询分析器来查看约束的名称、创建者、类型和创建时间。其语法格式为:EXEC sp_help 约束名称如果该约束有具体的定义和文本,那么可以用sp_helptext来查看。其语法格式为:EXEC sp_helptext 约束名称【案例5.9】使用系统存储过程查看TestDB 数据库中定义的ck_s_v约束信息和文本信息。代码如下,结果如图5-14所示。USE TestDB GOEXEC sp_help ck
30、_s_vGOEXEC sp_helptext ck_s_vGO,5.4查看约束的定义,前面讲了约束如何建立,约束在建立后可能根据实际情况需要删除,可以使用对象资源管理器来删除约束,也可以使用SQL语句来删除约束。1用对象资源管理器来删除表约束 使用对象资源管理器删除约束非常方便,正如在建立约束时一样,只需要在“表设计器”窗口中,将“设置主键”前的复选框取消即可删除主键约束,或删除默认值以删除默认约束;如图5-7所示,单击“删除”按钮删除唯一约束;如图5-9所示,单击“删除”按钮删除检查约束;如图5-11所示,单击“删除”按钮删除外键约束。2使用DROP命令删除表约束 利用SQL语句也可以方便地
31、删除一个或多个约束。其语法格式如下:ALTER TABLE table_name DROP CONSTRAINT constraint_name,n,5.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 CONSTRAINT ck_m_vGO,5.5删除约束,规则类似于CHECK约束,是用来限制数据字段的输入值的范围,实现强制数据的域完整性。但规则不同于
32、CHECK约束,在前面用到的CHECK约束可以针对一个列应用多个CHECK约束,但一个列不能应用多个规则;规则需要被单独创建,而CHECK约束在创建表的同时可以一起创建;规则比CHECK约束更复杂功能更强大;规则只需要创建一次,以后可以多次应用,可以应用于多个表多个列,还可以应用到用户定义的数据类型上。使用规则包括规则的创建、绑定、解绑和删除。可以在查询分析器中用SQL语句完成。1创建规则 规则作为一种数据库对象,在使用前必须被创建。创建规则的SQL命令是CREATE RULE。其语法格式如下:CREATE RULE rule_name AS condition_expression其中:ru
33、le_name是规则的名称,命名必须符合SQL Server 的命名规则。condition_expression是条件表达式。,5.6 使用规则,2绑定规则要使创建好的规则作用到指定的列或表等,还必须将规则绑定到列或用户定义的数据类型上才能够起作用。在查询分析器中,可以利用系统存储过程将规则绑定到字段或用户定义的数据类型上。其语法格式如下:EXECUTE sp_bindrule 规则名称,表名.字段名|自定义数据类型名【案例6.11】创建一个xb_rule规则,将它绑定到“tb_user”表的“usr_role”字段,保证输入数据只能为“学生”或“教师”。代码如下:USE TestDB GO
34、CREATE RULE role_ruleASrole in(学生,教师)GOEXEC sp_bindrule role_rule,tb_user.user_roleGO,5.6 使用规则,3解绑规则如果字段已经不再需要规则限制输入了,那么必须把已经绑定了的规则去掉,这就是解绑规则。在查询分析器中,同样用存储过程来完成解绑操作。其语法格式如下:EXECUTE sp_unbindrule 表名.字段名|自定义数据类型名4删除规则如果规则已经没有用了,那么可以将其删除。在删除前应先对规则进行解绑,当规则已经不再作用于任何表或字段等时,则可以用DROP RULE删除一个或多个规则。其语法格式如下:D
35、ROP RULE 规则名称,n【案例5.12】从student数据库中删除xb_rule规则。代码如下:USE TestDB GOexec sp_unbindrule tb_user.user_roleGOdrop rule role_rule,5.6 使用规则,默认(也称默认值、缺省值)是一种数据对象,它与DEFAULT(默认)约束的作用相同,也是当向表中输入数据的时候,没有为列输入值的时候,系统自动给该列赋一个“默认值”。与DEFAULT约束不同的是默认对象的定义独立于表,类似规则,可以通过定义一次,多次应用任意表的任意列,也可以应用于用户定义数据类型。默认对象的使用方法类似于规则,同样包
36、括创建、绑定、解绑和删除。这些操作可以在查询分析器中完成。,5.7 使用默认,1创建默认值在查询分析器中,创建默认对象的语法格式如下:CREATE DEFAULT default_nameAS default_description其中:default_name是默认值名称,必须符合SQL Server 2005命名规则。default_description是常量表达式,可以包含常量、内置函数或数学表达式。2绑定默认值默认值创建之后,必须将其绑定到表的字段或用户自定义的数据类型上才能产生作用。在查询分析器中使用系统存储过程来完成绑定。其语法格式如下:EXECUTE sp_bindefault
37、 默认名称,表名.字段名|自定义数据类型名,5.7 使用默认,【例5.13】创建一个df_v默认,将其绑定到“tb_setpaper”表的“s_v”字段,使单选题默认分值为2。代码如下:USE TestDB GOCREATE DEFAULT df_v AS 2GOEXEC sp_bindefault df_v,tb_setpaper.s_vGO3解绑默认值类似规则,对于不需要再利用默认的列,可以利用系统存储过程对其解绑。其语法格式如下:EXECUTE sp_unbindefault 表名.字段名|自定义数据类型名,5.7 使用默认,4删除默认值当默认值不再有存在的必要时,可以将其删除。在删除前,必须先对默认值解绑。在查询分析器中使用DROP语句删除默认值。其语法格式如下:DROP DEFAULT default_name,n【案例5.14】从TestDB数据库中将df_v默认值删除。代码如下:USE TestDB GOEXEC sp_unbindefault tb_setpaper.s_vGODROP DEFAULT df_vGO,5.7 使用默认,见习题与答案,习题,