SQL--九、数据完整性ppt课件

1、第九章数据完好性教学目的 数据完好性概述 学会运用约束来实施数据完好性维护 掌握运用规那么来实现数据完好性维护重点和难点 重点: PRIMARY KEY 、UNIQUE 规那么的创建与运用 默许值的创建与运用 难点: 绑定与松绑数据库完好性 数据库的完好性 数据的正确性和相容性 数据的完好性和平安性是两个不同概念数据的完好性和平安性是两个不同概念 数据的完好性数据的完好性 防止数据库中存在不符合语义的数据，也就是防止数据库防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据中存在不正确的数据 防备对象：不合语义的、不正确的数据防备对象：不合语义的、不正确的数据 数据的平安性数据

2、的平安性 维护数据库防止恶意的破坏和非法的存取维护数据库防止恶意的破坏和非法的存取 防备对象：非法用户和非法操作防备对象：非法用户和非法操作数据库完好性的方式实体完好性参照完好性用户定义的完好性属性上的约束条件的定义规那么与默许值default实体完好性定义 关系模型的实体完好性 CREATE TABLE中用PRIMARY KEY定义 单属性构成的码有两种阐明方法 定义为列级约束条件 定义为表级约束条件 对多个属性构成的码只需一种阐明方法 定义为表级约束条件 实体完好性定义(续) (1)在列级定义主码 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY，

3、 Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20) );例1 将Student表中的Sno属性定义为码实体完好性定义(续)(2)在表级定义主码 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)，CONSTRAINT PK_Sno PRIMARY KEY (Sno) ); 实体完好性定义(续) CREATE TABLE SC (Sno CHAR(9)

4、 NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno，Cno) /*只能在表级定义主码*/ ); 例2将SC表中的Sno，Cno属性组定义为码实体完好性检查插入或对主码列进展更新操作时，RDBMS按照实体完好性规那么自动进展检查。包括： 检查主码值能否独一，假设不独一那么回绝插入或修正 检查主码的各个属性能否为空，只需有一个为空就回绝插入或修正实体完好性检查 (续)检查记录中主码值能否独一的一种方法是进展全表扫描参照完好性定义 关系模型的参照完好性定义 在CREATE TABLE中用FOREIGN KEY短语定义哪些

5、列为外码 用REFERENCES短语指明这些外码参照哪些表的主码 参照完好性定义(续)例3 定义SC中的参照完好性 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno， Cno)， /*在表级定义实体完好性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)， /*在表级定义参照完好性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表级定义参照完好性*/ );例如，关系SC中一个

6、元组表示一个学生选修的某门课程的成果，Sno，Cno是主码。Sno，Cno分别参照援用Student表的主码和Course表的主码用户定义的完好性 用户定义的完好性就是针对某一详细运用的数据必需满足的语义要求 RDBMS提供，而不用由运用程序承当属性上的约束条件的定义 CREATE TABLE时定义 列值非空NOT NULL 列值独一UNIQUE 检查列值能否满足一个布尔表达式CHECK属性上的约束条件的定义(续) 不允许取空值 例例5 在定义在定义SC表时，阐明表时，阐明Sno、Cno、Grade属性不允许取空值。属性不允许取空值。 CREATE TABLE SC Sno CHAR(9) N

7、OT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT NOT NULL， PRIMARY KEY (Sno， Cno)， /* 假设在表级定义实体完好性，隐含了假设在表级定义实体完好性，隐含了Sno，Cno不允许取空值，不允许取空值，那么在列级不允许取空值的定义就不用写了那么在列级不允许取空值的定义就不用写了 * / ； 属性上的约束条件的定义(续) 列值独一 例例6 建立部门表建立部门表DEPT，要求部门称号，要求部门称号Dname列取值独一，部门编列取值独一，部门编号号Deptno列为主码列为主码 CREATE TABLE DEPT (Deptno N

8、UMERIC(2)， Dname CHAR(9) UNIQUE，/*要求要求Dname列值独一列值独一*/ Location CHAR(10)， PRIMARY KEY (Deptno) )；属性上的约束条件的定义(续) 用CHECK短语指定列值应该满足的条件例例7 Student表的表的Ssex只允许取只允许取“男或男或“女。女。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(8) NOT NULL， Ssex CHAR(2) CHECK (Ssex IN (男男，女女) ) ， /*性别属性性别属性Ssex只允许取只允许

9、取男男或或女女 */ Sage SMALLINT， Sdept CHAR(20) );属性上的约束条件检查和违约处置 插入元组或修正属性的值时，RDBMS检查属性上的约束条件能否被满足 假设不满足那么操作被回绝执行 元组上的约束条件的定义在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件 元组上的约束条件的定义(续) CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(8) NOT NULL， Ssex CHAR(2)， Sage SMALLINT，

10、Sdept CHAR(20)， PRIMARY KEY (Sno)， CHECK (Ssex=女 OR Sname NOT LIKE Ms.%) /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/ )；性别是女性的元组都能经过该项检查，由于Ssex=女成立；当性别是男性时，要经过检查那么名字一定不能以Ms.打头例9 当学生的性别是男时，其名字不能以Ms.打头。元组上的约束条件检查插入元组或修正属性的值时，RDBMS检查元组上的约束条件能否被满足假设不满足那么操作被回绝执行 5.4 完好性约束命名子句 CONSTRAINT 约束 CONSTRAINT PRIMARY KEY短语

11、| FOREIGN KEY短语 | CHECK短语 完好性约束命名子句(续) CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999)， Sname CHAR(20) CONSTRAINT C2 NOT NULL， Sage NUMERIC(3) CONSTRAINT C3 CHECK (Sage 30)， Ssex CHAR(2) CONSTRAINT C4 CHECK (Ssex IN ( 男，女)， CONSTRAINT StudentKey PRIMARY KEY(Sno

12、) )；在Student表上建立了5个约束条件，包括主码约束命名为StudentKey以及C1、C2、C3、C4四个列级约束。例10 建立学生登记表Student，要求学号在9000099999之间，姓名不能取空值，年龄小于30，性别只能是“男或“女。完好性约束命名子句(续) 修正表中的完好性限制 运用ALTER TABLE语句修正表中的完好性限制完好性约束命名子句(续)n可以先删除原来的约束条件，再添加新的约束条件n ALTER TABLE Studentn DROP CONSTRAINT C1;n ALTER TABLE Studentn ADD CONSTRAINT C1 CHECK (

13、Sno BETWEEN 900000 AND 999999);n ALTER TABLE Studentn DROP CONSTRAINT C3;n ALTER TABLE Studentn ADD CONSTRAINT C3 CHECK (Sage = 1980-01-01 and hire_date = 1980-01-01 and hire_date = getdate()规那么的绑定与松绑1.规那么的绑定 存储过程Sp_bindrule 可以绑定一个规那么到表的一个列或一个用户自定义数据类型上其语法如下: sp_bindrule rulename = rule, objname = o

14、bject_name , futureonly各参数阐明如下 rulename = rule -指定规那么称号objname = object_name - 指定规那么绑定的对象 futureonly -此选项仅在绑定规那么到用户自定义数据类型上时才可以运用.当指定此选项时仅以后运用此用户自定义数据类型的列会运用新规那么而当前曾经运用此数据类型的列那么不受影响.例: 绑定规那么hire_date_rule 到用户自定义数据类型hire_date 上去. exec sp_bindrule hire_date_rule, hire_date运转结果如下:Rule bound to data typ

15、e.The new rule has been bound to column(s) of the specified user data type.规那么的松绑存储过程Sp_unbindrule 可解除规那么与列或用户自定义数据类型的绑定.其语法如下 sp_unbindrule objname = object_name ,futureonly其中futureonly选项同绑定时一样仅用于用户自定义数据类型它指定现有的用此用户自定义数据类型定义的列依然坚持与此规那么的绑定假设不指定此项一切由此用户自定义数据类型定义的列也将随之解除与此规那么的绑定例:解除规那么hire_date_rule 与

16、用户自定义数据类型birthday 的绑定带futureonly选项 exec sp_unbindrule birthday, futureonly运转结果如下:(1 row(s) affected)Rule unbound from data type.规那么绑定到用户的自定义类型规那么绑定到表的列规那么的删除可以在企业管理器 中选择规那么单击右键从快捷菜单中选择delete 选项可运用drop rule 命令删除当前数据库中的一个或多个规那么其语法如下 drop rule rule_name ,.n缺省值default是往用户输入记录时没有指定详细数据的列中自动插入的数据缺省值对象,与al

17、ter table 或create table 命令操作表时用default 选项指定的缺省值功能类似,但缺省值对象可以用于多个列或用户自定义数据类型.它的管理与运用同规那么有许多类似之处,表的一列或一个用户自定义数据类型也只能与一个缺省值相绑定.创建缺省值create default 命令用于在当前数据库中创建缺省值对象,其语法如下: create default default_name as constant_expression 其中constant_expression 子句是缺省值的定义constant_expression 子句可以是数学表达式或函数,也可以包含表的列名或其它数据

18、库对象.例1: 创建生日缺省值birthday_defa create default birthday_defa as 1978-1-1例2: 创建姓名缺省值name_defa create default name_defa as user用企业管理器创建缺省值查看缺省值用存储过程Sp_helptext 查看缺省值运用Sp_helptext 存储过程可以查看缺省值的细节例: 查看缺省值today exec sp_helptext today运转结果如下:-Text-create default dp_date as getdate()绑定绑定UDT绑定列绑定列绑定缺省值sp_bindefa

19、ult 可以绑定一个缺省值到表的一个列或一个用户自定义数据类型上,其语法如下 sp_bindefault defname = default, objname = object_name , futureonly其中futureonly选项仅在绑定缺省值到用户自定义数据类型上时才可以运用当指定此选项时仅以后运用此用户自定义数据类型的列会运用新缺省值而当前曾经运用此数据类型的列那么不受影响例:绑定缺省值today 到用户自定义数据类型hire_date 上 exec sp_bindefault today, hire_date运转结果如下- -Default bound to data type.The new default has been bound to columns(s) of the specified user data type.解除绑定存储过程Sp_unbindefault 可以解除缺省值与表的列或用户自定义数据类型的绑定,其语法如下 Sp_u