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

《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 使用默认,见习题与答案,习题,