第11讲数据完整性

2023/2/31回顾索引索引的作用是提高查询速度，但索引会降低更新速度。聚集索引与非聚集索引，唯一索引与非唯一索引，单列索引与复合索引，主键索引。视图视图是一个虚表，只存储查询定义，不存储数据。在视图上可以进行增、删、改、查。增删改等操作有一定限制。数据库原理与应用第11讲数据完整性2023/2/33提纲重点分析数据完整性要求使用约束来强制完整性难点：外键约束与检查约束1.数据完整性概述关键词：实体完整、参照完整、自定义完整.2023/2/35数据完整性有3种类型实体完整性(EntityIntegrity)、参照完整性(ReferentialIntegrity)、用户定义的完整性(User-definedIntegrity)又称为域完整性。知识回顾关系的数据完整性3个规则实体完整性：通过主关键字约束和惟一约束实现参考完整性：通过外键约束来实现域完整性：通过有效性检查来实现2023/2/37在SQLServer中保障数据完整性的办法规则(Rule)默认(Default)-------略约束(Constraint)-----重点学习触发器(Trigger)------单独的章节介绍2023/2/38规则(Rule)规则(Rule)只能通过T_sql语句建，不能使用管理平台工具建。规则是单独存储的独立的数据库对象，可以多次被绑定。语法如下：CREATERULE语句用于在当前数据库中创建规则，其语法格式如下：

CREATE

RULE

rule_name

AS

condition_expression使用sp_helptext

系统存储过程可以查看规则的文本信息。sp_helptext

（规则/黙认值/触发器/未加密的存储过程或视图）使用sp_bindrule绑定规则sp_bindrule

规则名,‘表名.字段名‘使用sp_unbindrule解除规则sp_unbindrule

'employee.hire_date'删除规则。DROP

RULEhire_date_rule2023/2/39规则在管理台查看。2023/2/310约束(Constraint)约束定义了一列数据或一行数据表的的限制条件。使用约束优先于使用触发器、规则和默认值。约束在表定义中声明。SQLServer中有6种约束：主键约束外键约束惟一性约束检查约束默认约束非空值约束①PRIMARYKEY约束

PRIMARYKEY约束标识列或列集，这些列或列集的值唯一标识表中的行。一个PRIMARYKEY约束可以：作为表定义的一部分在创建表时创建。添加到还没有PRIMARYKEY约束的表中（一个表只能有一个PRIMARYKEY约束）。如果已有PRIMARYKEY约束，则可对其进行修改或删除。例如，可以使表的PRIMARYKEY约束引用其他列，更改列的顺序、索引名、聚集选项或PRIMARYKEY约束的填充因子。定义了PRIMARYKEY约束的列的列宽不能更改。②FOREIGNKEY约束

FOREIGNKEY约束称为外键约束，用于标识表之间的关系，以强制参照完整性，即为表中一列或者多列数据提供参照完整性。FOREIGNKEY约束也可以参照自身表中的其他列，这种参照称为自参照。

FOREIGNKEY约束可以在下面情况下使用：作为表定义的一部分在创建表时创建。如果FOREIGNKEY约束与另一个表（或同一表）已有的PRIMARYKEY约束或UNIQUE约束相关联，则可向现有表添加FOREIGNKEY约束。一个表可以有多个FOREIGNKEY约束。对已有的FOREIGNKEY约束进行修改或删除。例如，要使一个表的FOREIGNKEY约束引用其他列。定义了FOREIGNKEY约束列的列宽不能更改。使用FOREIGNKEY约束，还应注意以下几个问题：一个表中最多可以有253个可以参照的表，因此每个表最多可以有253个FOREIGNKEY约束。在FOREIGNKEY约束中，只能参照同一个数据库中的表，而不能参照其他数据库中的表。FOREIGNKEY子句中的列数目和每个列指定的数据类型必须和REFERENCE子句中的列相同。FOREIGNKEY约束不能自动创建索引。参照同一个表中的列时，必须只使用REFERENCE子句，而不能使用FOREIGNKEY子句。在临时表中，不能使用FOREIGNKEY约束。外键约束创建外键约束有两种方法，通过管理平台和SQL语句（1）在SQLServer管理平台中添加外部键约束，在SQLServer管理平台中添加外部键约束。如图所示。

点击此处对外键进行编辑选择主键表、字段，外键表、字段外键约束外键约束外键约束与主键约束相同，也分为表约束与列约束。定义表级外键约束的语法格式如下：[CONSTRAINTconstraint_name]FOREIGNKEY(column_name[,…n])REFERENCESref_table[(ref_column[,…n])][ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]][NOTFORREPLICATION]定义列级外键约束的语法格式如下：[CONSTRAINTconstraint_name][FOREIGNKEY]REFERENCESref_table[NOTFORREPLICATION]外键约束FROEIGNKEY：指定外键名称REFERENCES:指定关联的表③UNIQUE约束

UNIQUE约束在列集内强制执行值的唯一性。对于UNIQUE约束中的列，表中不允许有两行包含相同的非空值。主键也强制执行唯一性，但主键不允许空值，而且每个表中主键只能有一个，但是UNIQUE列却可以有多个。

UNIQUE约束优先于唯一索引。惟一约束例子：在grade表中定义“课程编号”这列惟一约束CREATETABLEgrade(学号CHAR(8)NOTNULL,课程编号CHAR(8)NOTNULLCONSTRAINTu_grade_课程编号UNIQUENONCLUSTERED,分数CHAT(10)NULL)定义列级约束惟一约束例子：在grade表中定义“课程编号”这列惟一约束，在表的列都定义完毕后，定义惟一约束定义约束：方法二CREATETABLEgrade(学号CHAR(8)NOTNULL,课程编号CHAR(8)NOTNULL,分数CHAT(10)NULL，CONSTRAINTu_grade_课程编号UNIQUE

NONCLUSTERED（课程编号）)惟一约束通过管理平台创建惟一约束，打开管理平台，选择studentsdb数据库，选中grade表，右键单击选择“修改表”，选择其中要设置惟一约束的列，右键“索引/键”，如图1，打开后，在“是否惟一”项中选择“是”，则创建了惟一约束。如图2如图1如图2检查约束检查(Check)约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。检查约束用于定义插入或者是修改一行时所满足的条件，通常限制某些字段的取值范围，比如人的性别只能是男和女，检查约束既可以作为列级约束，也可以作为表级约束。检查约束用途21创建检查约束常用的操作方法有如下两种：使用SQLServer管理平台创建检查约束；用Transact-SQL语句创建检查约束。（1）使用SQLServer管理平台创建检查约束，如图14-3所示图14-3创建检查约束对话框

点击此处可进行CHECK约束编辑检查约束CHECK创建检查约束检查约束CHECK第二种方法，通过SQL语句创建如果表已经存在，则可以通过下面的SQL语句创建例如：grade(学号，课程编号，分数)为分数这一列设置检查约束，分数的值在0~100之间创建检查约束ALTERTABLEgradeADDCONSTRAINTCK_分数CHECK(分数<100and分数>0)注意：多个CHECK条件需要用逻辑运算符连接检查约束CHECK第二种方法，通过SQL语句创建如果表不存在，则需要在创建表的时候通过SQL语句创建某个列或者是多列的检查约束。例如：创建grade(学号，课程编号，分数)表，指定分数的取值范围在0~100之间创建检查约束CREATETABLEgrade(学号CHAR(8)NOTNULL,课程编号CHAR(8)NOTNULL，分数CHAT(10)NULL，CONSTRAINTCK_grade_分数CHECK(分数<100and分数>0),

)练习为studentsdb数据库的student_info表的学号添加有效性约束：学号的最左边两位字符是00。ALTERTABLEstudent_infoADDCONSTRAINTCK_学号

CHECK(学号LIKE'00%')默认约束默认(Default)约束通过定义列的默认值或使用数据库的默认值对象绑定表的列，以确保在没有为某列指定数据时，来指定列的值。默认值可以是常量，也可以是表达式，还可以为NULL值。默认约束的定义和用途26创建默认约束常用的操作方法有如下两种：使用SQLServer管理平台创建默认约束；创建默认约束的Transact-SQL语句操作法。（1）使用SQLServer管理平台创建默认约束，如图所示图:创建默认约束对话框

默认约束DEFAULT管理平台创建默认约束默认约束DEFAULT默认约束的语法格式如下[CONSTRAINTconstraint_name]DEFAULTconstant_expression[FORcolumn_name]SQL语句创建默认约束默认约束DEFAULT例子：在创建表的同时，添加默认约束创建一个表student_info(学号，姓名，性别，家庭住址)，其中性别的默认值是“男”CREATETBALEstudent_info(学号CHAR(8)NOTNULL,姓名CHAR(12)NOTNULL,性别CHAR(2)DEFAULT‘男’NULL)SQL语句创建默认约束默认约束DEFAULT例子：为student_info表中的性别添加默认值，默认值是“男”ALTERTABLEstudent_infoADDCONSTRAINTsex_defaultDEFAULT‘男’FOR

性别SQL语句创建默认约束默认约束DEFAULT练习：为grade（学号，课程编号，分数）中的“课程编号”创建默认约束，默认值为“0001”ALTERTABLEgradeADDCONSTRAINT

课程编号_defaultDEFAULT‘0001’FOR

课程编号2023/2/331工号商品号数量销售日期工号姓名性别身份证号生日入职日期移动电话商品号商品名单位单价员工表销售表商品表数据完整性要求：（1）实体完整，不能有重复行，用主键约束，员工表中的身份证不能出现同号，用唯一约束。（2）参照完整，销售表中的工号取值必须来自员工表，商品号取值必须来自商品表，用外键约束（3）姓名不能为空，用非空约束。（4）性别只能取‘男’或‘女’，入职日期不能比生日大18年，移动电话必须为“1？？？？？？？？？”，用检查约束。（5）当性别没有输入值时，默认为‘男’,当单位没有输入值时，默认为‘个’用默认约束。分析实例2023/2/332案例表2.通过创建约束来强制完整性关键词：可以在每列上定义（称为列约束)，也可以在所有的列之后定义(称为表约束).2023/2/334说明：创建的所有约束都有约束名、约束类型和约束对象。约束名可以显式指出，如“CONSTRAINT

约束名”，如果没有显式指出，系统会自动生成一个约束名。如果一个约束只涉及一个列，可以在列的定义中创建该约束。一个约束涉及一个或多个列，则约束都可以在所有列之后创建。案例2023/2/3358.4.2外键约束例：关系图课堂小练如下图建表，表名：student，注意表中约束：1、主键约束2、姓名不为空3、性别默认为‘男’4、身份证号唯一，长度为185、手机约束：11位，如：'1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘2023/2/3363.综合练---课后体会2023/2/338如下表学号课程号成绩学号姓名性别身份证号生日手机号课程号课程名学分学生表课程表成绩表2023/2/339--创建数据库CreateDatabaseTEACHONPRIMARY(NAME=Teach_dat,FILENAME='D:\YZ\TEACH.mdf')LOGON(NAME=Teach_log,FILENAME='D:\YZ\TEACH.ldf')gouseteachgo2023/2/340--创建数据表CREATETABLE学生表(学号char(10)PrimaryKey,

姓名nchar(4)NOTNULL,

性别nchar(1)NOTNULLDEFAULT'男',生日DateTime,身份证号char(18)UNIQUE,手机char(11),CHECK(性别='男'or性别='女'),CHECK(手机Like'1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))go2023/2/341CREATETABLE课程表(课程号char(6)PRIMARYKEYNONCLUSTERED,

课程名nchar(10)NOTNULLUNIQUE,

学分intNOTNULL)goCREATETABLE成绩表(学号char(10)NOTNULLFOREIGNKEYREFERENCES学生表(学号),

课程号char(6)NOTNULLFOREIGNKEYREFERENCES课程表(课程号),

成绩intDEFAULT(0),PRIMARYKEYNONCLUSTERED(学号,课程号))go2023/2/342--添加数据INSERTINTO学生表(学号,姓名,性别,生日,身份证号,手机)VALUES('1000000001','一','男','1981-1-1',,)INSERTINTO学生表(学号,姓名,性别,生日,身份证号,手机)VALUES('1000000002','二','男','1981-1-1',,)INSERTINTO学生表(学号,姓名,性别,生日,身份证号,手机)VALUES('1000000003','三','男','1981-1-1',,)goINSERTINTO课程表(课程号,课程名,学分)VALUES('NN0001','计算机网络',4)INSERTINTO课程表(课程号,课程名,学分)VALUES('NN0002','数据库原理与应用',4)INSERTINTO课程表(课程号,课程名,学分)VALUES('NN0003','计算机高级语言',4)goINSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000001','NN0001',65)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000002','NN0001',74)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000003','NN0001',53)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000001','NN0002',77)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000002','NN0002',82)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000003','NN0002',75)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000001','NN0003',81)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000002','NN0003',91)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000003','NN0003',66)go2023/2/343--创建视图CREATEVIEW课程平均成绩视图asSELECT课程名,AVG(b.成绩)as平均成绩FROM课程表aJOIN成绩表bona.课程号=b.课程号GROUPBYa.课程名goCREATEVIEW学生修读学分视图asSELECTa.姓名,SUM(c.学分)as修读学分,sum(casewhenb.成绩>60thenc.学分else0