《数据库完整性》PPT课件.ppt

主讲教师：卫琳,第 9 章 数据完整性,本章的学习目标： 操纵数据时的问题和解决机制 约束的基本概念和类型 管理DEFAULT约束 管理CHECK约束 管理主键约束 管理UNIQUE约束 管理外键约束 禁止约束的场景,9.1 数据完整性概述,数据完整性的含义包括以下内容： （1）数值的完整性，指数据类型与取值的正确性。 （2）表内数据不相互矛盾。 （3）表间数据不相互矛盾，指数据的关联性不被破坏。,实体完整性：要求表中每一条记录（每一行数据）是惟一的，即它必须至少拥有一个惟一标识以区分不同的数据行。实现方法有：主键约束PRIMARY KEY、惟一性约束UNIQUE、惟一索引UNIQUE INDEX、标识IDENTITY等 。,按照数据完整性的功能分为4类：,9.1 数据完整性概述,值域完整性：限定表中输入数据的数据类型与取值范围。实现方法：默认值约束DEFAULT或默认对象、核查约束CHECK、外键约束FOREIGN KEY、规则RULE 、数据类型、非空性约束NOT NULL等。,9.1 数据完整性概述,引用完整性：指对数据库进行添加、删除、修改数据时，要维护表间数据的一致性。实现方法：外键约束FOREIGN KEY、核查约束CHECK、触发器TRIGGER、存储过程PROCEDURE。,9.1 数据完整性概述,用户定义的完整性：用于实现用户特殊要求的数据规则或格式。实现方法：默认值DEFAULT、核查约束CHECK、规则RULE 等。,9.1 数据完整性概述,9.2 使用约束实施数据的完整性,约束用于实现表内数据的完整性，属于对象级。SQL Server 支持的约束有如下几种：非空约束NOT NULL，主键约束PRIMARY KEY、惟一约束UNIQUE 、核查约束CHECK、外键约束 FOREIGN KEY、默认值约束DEFAULT。,9.3 约束的概念和类型,在SQL Server中，对于基本表的约束分为列约束和表约束。列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用“，”分隔，定义表约束时必须指出要约束的列的名称。 完整性约束的基本语法格式如下： CONSTRAINT constraint_name(约束名) ,9.3 约束的概念和类型,共有六种约束： 主键约束（primary key constraint） 唯一性约束(unique constraint) 检查约束(check constraint) 默认约束(default constraint) 外键约束(foreign key constraint) 空值约束(NULL),9.4 管理约束,9.4 管理约束 本节详细研究各种DEFAULE、CHECK、主键、UNIQUE、外键等约束的特点、创建方式、修改等内容。,约束：,9.4.1 primary key 约束 一个表通常可以通过一个列或多个列组合的数据来唯一标识表中的每一行，这个列或列的组合就被称为表上的主键。创建表中的主键是为了保证数据的实体完整性。 PRIMARY KEY （主键）约束用于定义基本表的主键，它是惟一确定表中每一条记录的标识符，其值不能为NULL，也不能重复，以此来保证实体的完整性 创建表时定义主键约束 列级 表级 更改表的主键约束,1）使用SSMS图形化界面创建主键约束,create table mysc (sno char(4) not null, cno char(10) not null, grade tinyint) alter table mysc add constraint mysc_pk primary key (sno,cno) alter table mysc drop constraint mysc_pk,2.）使用Transact-SQL语句操作法设置主键约束 其语法形式如下： CONSTRAINT constraint_name PRIMARY KEY （ column_name ）,9.4.2 UNIQUE约束 使用T-SQL语句创建 create table 雇员表 (雇员号 char(10) primary key, 雇员名 nvarchar(8) unique ) alter table student add constraint uk_sname_s unique (sname) select * from student with (index=uk_sname_s) alter table student drop constraint uk_sname_s,UNIQUE约束,通过SSMS可以完成创建和修改唯一性约束的操作,使用Transact-SQL语句完成惟一性约束的操作，其语法形式如下： CONSTRAINT constraint_name UNIQUE （ column_name ）,9.4.2 UNIQUE约束 使用SSMS图形界面创建唯一性约束,primary key 约束与unique 约束的主要区别： 一个数据表只能创建一个primary key 约束，但一个表中可根据需要对不同的列创建若干个unique 约束。 primary key 字段的值不允许为NULL,而unique字段的值可取NULL. 一般创建primary key约束时，系统会自动产生索引，索引的默认类型为聚簇索引。创建unique 约束时，系统会自动产生一个unique索引,索引的默认类型为非聚簇索引。 相同点： 二者均不允许表中对应字段存在重复值。 二者均自动创建一个唯一索引,9.4.3 check约束,1）使用SSMS图形化界面创建检查约束,alter table xs add ssex char(2) constraint sex_ck check(ssex in (男,女) alter table xs_kc drop constraint sex_ck,9.4.3 check约束,alter table xs_kc add constraint grade_ck check(grade=0 and grade=750) alter table xs_kc drop constraint grade_ck,2）使用T-SQL命令创建检查约束,9.4.4 DEFAULT 约束,使用SSMS图形化界面创建默认约束,用Transact-SQL语句创建默认约束。其语法形式如下： CONSTRAINT constraint_name DEFAULT constraint_expression FOR column_name,create table mystudent2 (sno char(10), sname nvarchar(4), ssex char(2), sdept nvarchar(10) constraint sdept_defa default (计算机科学), scomegrade smallint constraint cg_ck check (scomegrade=0 and scomegrade=750) ),例如,create table stu (sno char(10), sname varchar(8), ssex char(2) constraint sex_ck check (ssex in (男,女), sdept varchar(20) constraint sdept_defa1 default (计算机科学) ),9.4.5 NULL 约束,1)使用SSMS图形化界面设置空值约束。,用Transact-SQL语句创建空值约束。其语法形式如下： 直接在相应的语句后书写 NULL | NOT NULL ,1.)在SSMS图形化平台上添加外键约束。,9.4.6 foreign key 约束,使用Transact-SQL语句设置外部键约束 ，其语法形式如下： CONSTRAINT constraint_name FOREIGN KEY（column_name,n）REFERENCES ref_table （ref_column,n）,create table xs (sno char(10) primary key) create table xs_kc (sno char(10), cno char(4), grade tinyint, foreign key (sno) references xs(sno) on update cascade on delete cascade ),create table kc (cno char(4) primary key, cname nvarchar(20), cpno char(4), credit tinyint) alter table xs_kc add constraint kc_foreign foreign key (cno) references kc(cno),alter table xs_kc drop constraint kc_foreign,9.4.3 foreign key 约束,2.使用T-SQL命令,9.5 规则对象,约束是作用于表的数据库对象，离开了表，约束将不再存在。除了约束外，SQL Server还提供了两个独立于表的数据库对象用以维护数据库的完整性，即默认和规则。规则的作用类似于核查约束，若将一个规则绑定到指定列上，则可以检查该列的数据是否符合规则的要求。,9.5 规则对象 9.5.1 创建规则 9.5.2 绑定规则 9.5.3 解除规则 说明： 规则与检查约束的主要区别： 一列只能绑定一个规则，但却可以设置多个检查约束。 规则的优点： 仅创建一次就可以绑定到数据库的多个表的列上，使同一数据库中所有表的不同列共享。规则还可以绑定到现一数据库中一个以上的用户定义的数据类型上。,9.5.1 创建规则,创建规则的语法如下。 命令格式： CREATE RULE rule_name AS rule_expression 命令说明： （1）rule_name为要建立的规则的名称。 （2）rule_expression为规则表达式，它与WHERE子句中的条件表达式类似，可以使用比较表达式、逻辑表达式、LIKE子句等，所不同的是需要将WHERE子句中的列名改为一个以开头并符合SQL Server命名规则的参数。,例：创建一个规则，用于限制输入到该规则绑定的列中的值只能是该规则中列出的值。 use test go create rule list_rule as list in(数据结构,操作系统,数据库原理) go exec sp_bindrule list_rule,ame go,9.5.2 绑定规则,规则创建后，它仅仅是一个数据库对象，与其他数据库对象并没有联系。要将规则应用于一个表（或用户定义的数据类型）中，必须将其与表中的指定列（或用户定义的数据类型）相结合，称之为将规则绑定（bind）到列（或用户定义的数据类型），或称绑定规则。绑定规则需要使用系统存储过程sp_bindrule ，语法如下。 命令格式： sp_bindrule rule_name, table_name.column_name 或 sp_bindrule rule_name, user_defined_datatype ,futureonly_ flag 命令说明：,例：创建一个规则，并绑定到表KC的课程号列，用于限制课程号的输入范围。 create table mykc (cno char(4), cname nvarchar(20), cpno char(4), credit tinyint) use test go create rule kc_rule as range like 1-50-90-90-9 go exec sp_bindrule kc_rule,o Go,说明：创建的规则对先前已存在于数据库中的数据无效。,例：定义一个用户数据类型course_num,然后将前面定义的规则kc_rule绑定到用户数据类型上，最后定义表,其课程号的数据类型为course_num。 use test go exec sp_addtype course_num,char(4),not null exec sp_bindrule kc_rule,course_num go create table mykc1 (cno course_num, cname nvarchar(20), cpno char(4), credit tinyint) go 说明：当在用户定义的数据类型列中插入或更新值时，绑定到该类型的规则才会激活。规则不检验变更，所以在向用户定义数据类型的变更赋值时，不能与列绑定的规则冲突。,9.5.3 解除绑定,当表中不再需要规则时，可以将规则与列分离，称为解除绑定。解除绑定需要使用系统存储过程sp_unbindrule ，语法如下。 命令格式： sp_unbindrule table_name.column_name 或 sp_unbindrule user_defined_datatype , futureonly_ flag ,9.5.4 删除规则,删除规则的前提是解除数据库中所有表的绑定。删除规则语法如下。 命令格式： DROP RULE rule_name,删除规则对象，首先要解除绑定关系（被绑定对象与规则对象之间）,例：解除课程号列、自定义数据类型course_num与kc_rule之间的绑定关系，并删除规则对象kc_rule。 use test go if exists (select name from sysobjects where name=kc_rule and type=R) begin exec sp_unbindrule o exec sp_unbindrule course_num drop rule kc_rule end go,use test go if exists (select name from sysobjects where name=list_rule and type=R) begin exec sp_unbindrule ame drop rule list_rule end go,9.6 默 认,9.6.1 创建默认 创建默认的语法如下。 命令格式： CREATE DEFAULT default_name AS default_expression 命令说明： （1）default_name是符合SQL Server标识符规则的默认值名称。 （2）default_expression是常量，用以指出默认值的具体数值或字符串。,默认值约束及默认值对象,1.在表中定义及修改删除默认值约束 a.默认值约束定义 create table mystudent (sno char(10), sname nvarchar(4), ssex char(2), sdept nvarchar(10) default (计算机科学), scomegrade smallint constraint cg_ck check (scomegrade=0 and scomegrade=750) ) 说明：插入行后重新打开表，可看到设定的默认值。,create table mystudent2 (sno char(10), sname nvarchar(4), ssex char(2), sdept nvarchar(10) constraint sdept_defa default (计算机科学), scomegrade smallint constraint cg_ck check (scomegrade=0 and scomegrade=750) ),等价于,create table stu (sno char(10), sname varchar(8), ssex char(2) constraint sex_ck check (ssex in (男,女), sdept varchar(20) constraint sdept_defa default (计算机科学) ),默认值约束及默认值对象,1.在表中定义及修改删除默认值约束 b.修改删除默认值约束 alter table xs add sdept nvarchar(10) constraint sdept_defu default (计算机科学) alter table xs add comedate date constraint cd_defau default getdate() with values alter table xs drop constraint cd_defau alter table xs drop column comedate,说明：with values仅用在对表添加新字段的情况下，若使用了with values，则将为表中现有行添加的新字段提供默认值；若没有使用，那么每一行的新列中都将为null,只有在插入行时才有默认值。,使用SSMS可直接删除有默认值约束的列,默认值约束及默认值对象,2. 定义和绑定默认值对象 create default xf_default as 0 go exec sp_bindefault xf_default,kc.credit create table book (bno char(10), bname nvarchar(30) not null, hire_date date not null ) go create default today as getdate() go exec sp_bindefault today,book.hire_date go,create table mys2 (sno char(12), sname nvarchar(4), ssex char(2), scomedate date ) go exec sp_bindefault today,mys2.scomedate go,use test go exec sp_addtype birth_date,date,null go create default day as 1990-0-0 go exec sp_bindefault day,birth_date,use test go if exists (select name from sysobjects where name=day and type=D) begin exec sp_unbindefault birth_date drop default day end go,9.6.2 绑定默认,绑定默认的语法如下。 命令格式： sp_bindefault default_name , table_name.column_name 或 sp_bindefault default_name , user_defined_datatype , futureonly_ flag 命令说明： （1）default_name 为默认值数据库对象的名称。 （2）其他语法项目的用法与规则类似。,9.6.3 解除绑定,解除默认绑定的语法如下。 命令格式： sp_unbin