课件第7章 数据完整性.ppt

LOGO本章学习目标：本章学习目标：理解数据完整性的概念和控制机制理解数据完整性的概念和控制机制掌握使用规则、默认值及约束来实现数据的掌握使用规则、默认值及约束来实现数据的完整性完整性7.1 工作场景导入工作场景导入怎样规定插入的学生年龄值在怎样规定插入的学生年龄值在1120之间？之间？如何限制如何限制“性别性别”列只能输入列只能输入“男男”或或“女女”？如果入学学生中男生占大多数，怎样在如果入学学生中男生占大多数，怎样在“性性别别”列设置默认值列设置默认值“男男”，从而削减批量插入，从而削减批量插入的数据量。的数据量。强制数据完整性能够保证数据库中数据的强制数据完整性能够保证数据库中数据的质量。例如，如果输入了员工质量。例如，如果输入了员工ID值为值为214的员工，则数据库不允许其他员工拥有相的员工，则数据库不允许其他员工拥有相同同ID值。如果某一列的取值范围是值。如果某一列的取值范围是1到到10，则数据库将不接受插入此范围之外的值。则数据库将不接受插入此范围之外的值。如果表中有一个存储公司部门编号的如果表中有一个存储公司部门编号的department_id列，则该列应当只能够列，则该列应当只能够插入有效的公司部门编号值。插入有效的公司部门编号值。引导问题：引导问题：(1) 如何使用如何使用“规则规则”限定输入的年龄值在限定输入的年龄值在1120之间？之间？(2) 如何为如何为“性别性别”列设置默认值列设置默认值“男男”，以，以减少插入数据量？减少插入数据量？(3) 如何实现强制数据完整性？如何实现强制数据完整性？7.2 如何实现数据完整性如何实现数据完整性数据完整性的含义包括以下内容：数据完整性的含义包括以下内容：（1）数值的完整性，指数据类型与取值的正）数值的完整性，指数据类型与取值的正确性。确性。（2）表内数据不相互矛盾。）表内数据不相互矛盾。 （3）表间数据不相互矛盾，指数据的关联性）表间数据不相互矛盾，指数据的关联性不被破坏。不被破坏。实体完整性：要求表中每一条记录（每一行实体完整性：要求表中每一条记录（每一行数据）是唯一的，即它必须至少拥有一个惟数据）是唯一的，即它必须至少拥有一个惟一标识以区分不同的数据行。实现方法有：一标识以区分不同的数据行。实现方法有：主键约束主键约束PRIMARY KEY、惟一性约束、惟一性约束UNIQUE、惟一索引、惟一索引UNIQUE INDEX、标识标识IDENTITY等等 。按照数据完整性的功能分为按照数据完整性的功能分为4类：类：7.2 如何实现数据完整性如何实现数据完整性域完整性：限定表中输入数据的数据类型与域完整性：限定表中输入数据的数据类型与取值范围。实现方法：默认值约束取值范围。实现方法：默认值约束DEFAULT或默认对象、核查约束或默认对象、核查约束CHECK、外键约束外键约束FOREIGN KEY、规则、规则RULE 、数据类型、非空性约束数据类型、非空性约束NOT NULL等。等。7.2 如何实现数据完整性如何实现数据完整性引用完整性：指对数据库进行添加、删除、引用完整性：指对数据库进行添加、删除、修改数据时，要维护表间数据的一致性。实修改数据时，要维护表间数据的一致性。实现方法：外键约束现方法：外键约束FOREIGN KEY、核查约、核查约束束CHECK、触发器、触发器TRIGGER、存储过程、存储过程PROCEDURE。7.2 如何实现数据完整性如何实现数据完整性用户定义的完整性：用户定义的完整性：用于实现用户特殊要求用于实现用户特殊要求的数据规则或格式。实现方法：默认值的数据规则或格式。实现方法：默认值DEFAULT、核查约束、核查约束CHECK、规则、规则RULE 等。等。 7.2 如何实现数据完整性如何实现数据完整性7.3 规则对象的基本操作规则对象的基本操作 创建规则对象的基本语法格式如下：创建规则对象的基本语法格式如下：CREATE RULE schema_name rule_name AS condition_expression ; 各参数的含义如下：各参数的含义如下：schema_name：规则所属架构的名称。：规则所属架构的名称。rule_name：新规则的名称。规则名称必：新规则的名称。规则名称必须符合标识符规则。根据需要，指定规则所须符合标识符规则。根据需要，指定规则所有者名称。有者名称。condition_expression：定义规则的条：定义规则的条件。规则可以是件。规则可以是WHERE子句中任何有效的子句中任何有效的表达式，并且可以包括诸如算术运算符、关表达式，并且可以包括诸如算术运算符、关系运算符和谓词系运算符和谓词(如如 IN、LIKE、BETWEEN)这样的元素。规则不能引用列这样的元素。规则不能引用列或其他数据库对象，可以包括不引用数据库或其他数据库对象，可以包括不引用数据库对象的内置函数，不能使用用户定义函数。对象的内置函数，不能使用用户定义函数。condition_expression包括一个变量。包括一个变量。每个局部变量的前面都有一个每个局部变量的前面都有一个符号。该表符号。该表达式引用通过达式引用通过UPDATE或或INSERT语句输入语句输入的值。在创建规则时，可以使用任何名称或的值。在创建规则时，可以使用任何名称或符号表示值，但第一个字符必须是符号表示值，但第一个字符必须是符号。符号。7.3.2 绑定规则对象绑定规则对象将创建好的规则对象绑定到某个数据表的将创建好的规则对象绑定到某个数据表的列上，规则对象才会起约束作用。列上，规则对象才会起约束作用。绑定规则对象的语法格式如下：绑定规则对象的语法格式如下：Exec Sp_bindrule 规则对象名规则对象名, 表名表名.列名列名【例【例6-5】建立数据表】建立数据表“学生学生”，包括，包括“姓名姓名”、“性别性别”、“年龄年龄”字段，要求年龄字段值为字段，要求年龄字段值为1120。CREATE TABLE 学生学生( 姓名姓名 nvarchar(4), 性别性别 nvarchar(1),年龄年龄 tinyint)Exec Sp_bindrule age_rule,学生学生.年龄年龄7.3.3 验证规则对象验证规则对象【例【例6-6】向】向“学生学生”表中插入一条记录，该表中插入一条记录，该学生的年龄为学生的年龄为215。INSERT INTO 学生学生 values(刘星刘星,215)执行上述插入语句，结果如图执行上述插入语句，结果如图6-3所示。所示。7.3.4 解除规则对象绑定解除规则对象绑定如果表中列不再需要规则对象，可以将规如果表中列不再需要规则对象，可以将规则对象解除绑定。执行删除规则对象前，则对象解除绑定。执行删除规则对象前，规则对象仍然存储在数据库中，还可以再规则对象仍然存储在数据库中，还可以再绑定到其他列上。绑定到其他列上。解除绑定的命令格式如下：解除绑定的命令格式如下：Sp_unbindrule 表名表名.列名列名【例【例6-7】解除】解除“学生学生”表中的规则对象绑定。表中的规则对象绑定。Exec Sp_unbindrule 学生学生.年龄年龄7.3.5 删除规则对象删除规则对象如果需要删除规则对象，必须先解除对该如果需要删除规则对象，必须先解除对该规则对象的所有绑定，然后从数据库中清规则对象的所有绑定，然后从数据库中清除。删除规则对象的语法格式如下。除。删除规则对象的语法格式如下。DROP RULE 规则对象名组规则对象名组【例【例6-8】删除规则对象】删除规则对象age_rule和和sex_rule。DROP RULE age_rule, sex_rule说明：可以同时删除多个规则对象，规则说明：可以同时删除多个规则对象，规则对象名之间用对象名之间用“，”分隔。分隔。7.4 默认值对象的基本操默认值对象的基本操作作创建默认值对象的语法格式如下：创建默认值对象的语法格式如下：CREATE DEFAULT default_name As condition_expression ;其中，其中，As子句后面的表达式主要是对于子句后面的表达式主要是对于定义的默认值对象赋值。定义的默认值对象赋值。【例【例6-9】创建一个名为】创建一个名为sex_default 的默认值对象，默认值为的默认值对象，默认值为“男男”。CREATE DEFAULT sex_default As 男男7.4.2 默认值对象绑定默认值对象绑定默认值对象必须绑定到数据列或用户定义默认值对象必须绑定到数据列或用户定义的数据类型中才能得到应用。绑定默认值的数据类型中才能得到应用。绑定默认值对象使用系统存储过程对象使用系统存储过程Sp_bindefault，其语法格式如下：其语法格式如下：Exec Sp_bindefault 默认值对象名默认值对象名, 表名表名.列名列名【例【例6-10】将默认值对象】将默认值对象sex_default绑定到绑定到“学生学生”表的表的性别性别列上。列上。Exec Sp_bindefault sex_default, 学生学生.性别性别7.4 默认值对象的基本操默认值对象的基本操作作7.4.3 解除默认值对象绑定解除默认值对象绑定解除默认值对象绑定就是将默认值对象从解除默认值对象绑定就是将默认值对象从表的列上分离开来，只要不执行删除默认表的列上分离开来，只要不执行删除默认值对象，则该默认值对象就会一直存储在值对象，则该默认值对象就会一直存储在数据库中，还可再绑定到其他数据列上。数据库中，还可再绑定到其他数据列上。解除默认值对象使用系统存储过程解除默认值对象使用系统存储过程Sp_unbindefault，其语法格式如下：，其语法格式如下：Sp_unbindefault 表名表名.列名列名【例【例6-11】将】将sex_default默认值对默认值对象从象从“学生学生”表的表的“性别性别”列中分离。列中分离。Exec Sp_unbindefault 学生学生.性别性别7.4 默认值对象的基本操默认值对象的基本操作作删除默认值对象就是从数据库中清除默认删除默认值对象就是从数据库中清除默认值对象的定义，该默认值对象不能再绑定值对象的定义，该默认值对象不能再绑定到任何数据表的列上。删除默认值对象之到任何数据表的列上。删除默认值对象之前，必须解除对其所有的绑定。前，必须解除对其所有的绑定。删除默认值对象的语法格式如下：删除默认值对象的语法格式如下：DROP default 默认值对象名组默认值对象名组【例【例6-12】删除默认值对象】删除默认值对象sex_default。DROP default sex_default7.4 默认值对象的基本操默认值对象的基本操作作7.5 完整性约束完整性约束 SQL Server 2016中提供了下列完整中提供了下列完整性约束机制来强制数据表中列数据的完性约束机制来强制数据表中列数据的完整性：整性：PRIMARY KEY约束约束 FOREIGN KEY约束约束 UNIQUE约束约束 CHECK约束约束 DEFAULT定义定义 允许空值允许空值7.5.1 PRIMARY KEY 约束约束 一个表通常可以通过一个列或多个列组合的数据来唯一个表通常可以通过一个列或多个列组合的数据来唯一标识表中的每一行，这个列或列的组合就被称为表上的一标识表中的每一行，这个列或列的组合就被称为表上的主键。创建表中的主键是为了保证数据的实体完整性。主键。创建表中的主键是为了保证数据的实体完整性。 PRIMARY KEY （主键）约束用于定义基本表的主键，（主键）约束用于定义基本表的主键，它是惟一确定表中每一条记录的标识符，其值不能为它是惟一确定表中每一条记录的标识符，其值不能为NULL，也不能重复，以此来保证实体的完整性，也不能重复，以此来保证实体的完整性创建表时定义主键约束创建表时定义主键约束列级列级表级表级更改表的主键约束更改表的主键约束1）使用）使用SSMS图形化界面创建主键约束图形化界面创建主键约束CREATE table student(snm char(15) primary key,sname nvarchar(6),)CREATE TABLE sc(snm char(15),cnm char(6),grade int,primary key (snm,cnm)2.）使用使用Transact-SQL语句操作法设置主键约束语句操作法设置主键约束其语法形式如下：其语法形式如下：CONSTRAINT constraint_name PRIMARY KEY （ column_name ）7.5.2 UNIQUE约束约束使用使用T-SQL语句创建语句创建create table 雇员表雇员表(雇员号雇员号 char(10) primary key, 雇员名雇员名 nvarchar(8) unique )alter table studentadd constraint uk_sname_sunique (sname)select * from studentwith (index=uk_sname_s)alter table studentdrop constraint uk_sname_s7.5.2 UNIQUE约束约束通过SSMS可以完成创建和修改唯一性约束的操作 使用Transact-SQL语句完成惟一性约束的操作，其语法形式如下： CONSTRAINT constraint_name UNIQUE （ column_name ）primary key 约束与约束与unique 约束的主要区别：约束的主要区别：一个数据表只能创建一个一个数据表只能创建一个primary key 约束，但一个表中可根约束，但一个表中可根据需要对不同的列创建若干个据需要对不同的列创建若干个unique 约束。约束。primary key 字段的值不允许为字段的值不允许为NULL,而而unique字段的值可取字段的值可取NULL.一般创建一般创建primary key约束时，系统会自动产生索引，索引的约束时，系统会自动产生索引，索引的默认类型为聚簇索引。创建默认类型为聚簇索引。创建unique 约束时，系统会自动产生约束时，系统会自动产生一个一个unique索引索引,索引的默认类型为非聚簇索引。索引的默认类型为非聚簇索引。相同点：相同点：二者均不允许表中对应字段存在重复值。二者均不允许表中对应字段存在重复值。二者均自动创建一个唯一索引二者均自动创建一个唯一索引7.5.3 check约束约束1）使用使用SSMS图形化界面创建检查约束图形化界面创建检查约束 alter table xsadd ssex char(2)constraint sex_ckcheck(ssex in (男,女)alter table xs_kcdrop constraint sex_ck7.5.3 check约束约束alter table xs_kcadd constraint grade_ckcheck(grade=0 and grade=0 and scomegrade=750) ) 例如例如create table stu(sno char(10), sname varchar(8), ssex char(2)constraint sex_ckcheck (ssex in (男,女), sdept varchar(20)constraint sdept_defa1default (计算机科学)7.5.5 NULL 约束约束 1)使用使用SSMS图形化界面设置空值约束图形化界面设置空值约束。用用Transact-SQL语句创建空值约束。语句创建空值约束。其语法形式如下：其语法形式如下：直接在相应的语句后书写直接在相应的语句后书写 NULL | NOT NULL 1.)在在SSMS图形化平台上添加外键约束。图形化平台上添加外键约束。 7.5.6 foreign key 约束约束外键约束确保数据的参照完整性。当外键表中的FOREIGN KEY 外键引用主键表中的PRIMARY KEY主键时，外键表与主键表就建立了关系，并成功加入到数据库中，外键约束定义一个或多个列，这些列可以引用同一个表或另外一个表中的主键约束列或UNIQUE约束列。使用Transact-SQL语句设置外部键约束 ，其语法形式如下：CONSTRAINT constraint_name FOREIGN KEY（column_name,n）REFERENCES ref_table （ref_column,n）小结小结本章主要介绍sql server 2016 规则、默认和完整性约束机制。主要内容包括1. 规则对象的基本操作2. 默认值对象的基本操作3. 完整性约束LOGO