5.0 数据库完整性详解(PRIMARY KEY、REFERENCES、CHECK、CONSTRAINT、DOMAIN、TRIGGER) |
您所在的位置:网站首页 › 数据库中数据完整性 › 5.0 数据库完整性详解(PRIMARY KEY、REFERENCES、CHECK、CONSTRAINT、DOMAIN、TRIGGER) |
文章目录
0.思维导图1.数据库完整性概述2.实体完整性---PRIMARY KEY(1)实体完整性的定义(2)实体完整性检查和违约处理
2.参照完整性---REFERENCES(1)参照完整性定义(2)参照完整性检查和违约处理
3.用户定义的完整性---CHECK(1)属性上的约束条件定义(2)属性上的约束条件检查和处理(3)元组上的约束条件的定义(4)元组上的约束条件检查和违约处理
4.完整性约束命名子句---CONSTRAINT5.域中的完整性限制---DOMAIN6.断言---ASSERTION(1)定义断言(2)删除断言
7.触发器---TRIGGER(1)定义触发器(2)激活触发器(3)删除触发器
0.思维导图
简单介绍: 首先先概述一下数据库完整性指的是什么,数据库完整性指的是数据的正确性和相容性。数据的正确性是指数据是符合现实世界语义、反映当前实际状况的;比如说人类的性别,只能是男和女。数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的。比如说年龄一般都在1-100岁,当然也有超过一百岁的,反正没有两百岁,三百岁成仙的人类。既然我们学的是数据库,那么数据库管理系统就应该为数据完整性实现如下的功能: 1.提供定义完整性约束条件的机制 完整约束条件也称完整性规则,是数据库中数据必须满足的语义条件规则为保证数据的正确、有效和相容性的一些规则数据的主码、外码、一些约束规则2.提供完整性检查的方法 数据库管理系统中检查数据是否满足完整性规则的机制称为完整性检查一般在执行INSERT、UPDATE、DELETE时检查3.违约处理 数据库管理系统若发现用户的操作违背了完整性约束条件将采取一定的动作,如拒绝(NO ACTION)执行该操作或级联(CASCADE)执行其他操作,进行违约处理以保证数据的完整性。此前写的一篇文章提到了关系完整性约束的基本概念 关系完整性的基本概念,而本篇文章讲的是利用SQL如何去实现这些约束机制,如何实现完整性规则 2.实体完整性—PRIMARY KEY (1)实体完整性的定义关系模型的实体完整性 CREATE TABLE中用PRIMARY KEY定义单属性构成的码有两种说明方法 定义为列级约束条件定义为表级约束条件对多个属性构成的码只有一种说明方法 定义为表级约束条件举几个例子 将Student表中的Sno属性定义为码 (1)在列级定义主码 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2) , Sage SMALLINT, Sdept CHAR(20)); (2)在表级定义主码 CREATE TABLE Student (Sno CHAR(9), Sname CHAR(20) NOT NULL, Ssex CHAR(2) , Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY (Sno) ); 将SC表中的Sno,Cno属性组定义为码 CREATE TABLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMARY KEY (Sno,Cno) /*只能在表级定义主码*/ ); (2)实体完整性检查和违约处理插入或对主码列进行更新操作时,RDBMS按照实体完整性规则自动进行检查。包括: 检查主码值是否唯一,如果不唯一则拒绝插入或修改检查主码的各个属性是否为空,只要有一个为空就拒绝插入或修改检查记录中主码值是否唯一的一种方法是进行全表扫描 关系模型的参照完整性定义 在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码用REFERENCES短语指明这些外码参照哪些表的主码举个例子 例如,关系SC中一个元组表示一个学生选修的某门课程的成绩, (Sno,Cno)是主码。 Sno,Cno分别参照引用Student表的主码和Course表的主码 定义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) /*在表级定义参照完整性*/ ); (2)参照完整性检查和违约处理参照完整性违约处理 拒绝(NO ACTION)执行 默认策略 级联(CASCADE)操作 设置为空值(SET-NULL) 对于参照完整性,除了应该定义外码,还应定义外码列是否允许空值 [例4] 显式说明参照完整性的违约处理示例 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) ON DELETE CASCADE /*级联删除SC表中相应的元组*/ ON UPDATE CASCADE, /*级联更新SC表中相应的元组*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/ ON UPDATE CASCADE /*当更新course表中的cno时,级联更新SC表中相应的元组*/ ); 经过测试,当UPDATE更新Student和Course表数据时,SC也自动更新当删除Student的数据时,SC同步删除相应数据当删除Course的数据时,拒绝删除,因为SC中有相应的外码数据,并设置了NO ACTION当删除SC的数据时,对Student和Course无影响CREATE TABLE时定义 列值非空(NOT NULL)列值唯一(UNIQUE)检查列值是否满足一个布尔表达式(CHECK) 1.不允许取空值 [例5] 在定义SC表时,说明Sno、Cno、Grade属性不允许取空值。 CREATE TABLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT NOT NULL, PRIMARY KEY (Sno, Cno), /* 如果在表级定义实体完整性,隐含了Sno,Cno不允许取空值, 则在列级不允许取空值的定义就不必写了 */ ); 2.列值唯一 [例6]建立部门表DEPT,要求部门名称Dname列取值唯一, 部门编号Deptno列为主码 CREATE TABLE DEPT (Deptno NUMERIC(2), Dname CHAR(9) UNIQUE,/*要求Dname列值唯一*/ Location CHAR(10), PRIMARY KEY (Deptno) ); 3. 用CHECK短语指定列值应该满足的条件 [例7]Student表的Ssex只允许取“男”或“女”。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK (Ssex IN (‘男’,‘女’) ) , /*性别属性Ssex只允许取'男'或'女' */ Sage SMALLINT, Sdept CHAR(20) ); (2)属性上的约束条件检查和处理 插入元组或修改属性的值时,RDBMS检查属性上的约束条件是否被满足如果不满足则操作被拒绝执行 (3)元组上的约束条件的定义 在CREATE TABLE时可以用CHECK短语定义元组上的约束条件,即元组级的限制同属性值限制相比,元组级的限制可以设置不同属性之间的取值的相互约束条件 [例9] 当学生的性别是男时,其名字不能以Ms.打头。 CREATE TABLE Student (Sno CHAR(9), Sname CHAR(8) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY (Sno), CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%') /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/ ); 性别是女性的元组都能通过该项检查,因为Ssex=‘女’成立; 当性别是男性时,要通过检查则名字一定不能以Ms.打头 (4)元组上的约束条件检查和违约处理 插入元组或修改属性的值时,RDBMS检查元组上的约束条件是否被满足如果不满足则操作被拒绝执行 4.完整性约束命名子句—CONSTRAINTCONSTRAINT 约束语句格式: CONSTRAINT [PRIMARY KEY短语 |FOREIGN KEY短语 |CHECK短语]举个例子: [例10] 建立学生登记表Student,要求学号在90000~99999之间, 姓名不能取空值,年龄小于30,性别只能是“男”或“女”。 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 =ALL( SELECT count (*)/*此断言的谓词,涉及聚集操作count */ FROM SC /*和分组函数group by的SQL语句*/ GROUP by cno ); [例5.20 限制每个学期每一门课程最多60名学生选修] 首先修改SC表的模式,增加一个“学期(TERM)”的属性。 ALTER TABLE SC ADD TERM DATE; /*先修改SC表,增加TERM属性,它的类型是DATE*/ 然后定义断言: CREATE ASSERTION ASSE_SC_CNUM2 CHECK (60>=ALL ( select count (*) from SC group by Cno,TERM )); (2)删除断言DROP ASSERTION ; 如果断言很复杂,则系统在检测和维护断言上的开销较高,这是在使用断言时应该注意的。 7.触发器—TRIGGER 触发器(Trigger)是用户定义在关系表上的一类由事件驱动的特殊过程 由服务器自动激活可以进行更为复杂的检查和操作,具有更精细和更强大的数据控制能力 (1)定义触发器CREATE TRIGGER语法格式: CREATE TRIGGER {BEFORE | AFTER} ON FOR EACH {ROW | STATEMENT} [WHEN ]定义触发器的语法说明: 创建者:表的拥有者触发器名表名:触发器的目标表,不能建立在视图上触发事件:INSERT、DELETE、UPDATE触发器类型 行级触发器(FOR EACH ROW); 语句级触发器(FOR EACH STATEMENT);触发条件 触发条件为真,则执行触发动作体,否则不执行; 省略WHEN触发条件时,只要触发器激活(触发器事件)则立刻执行触发动作体; 触发动作体 触发动作体可以是一个匿名PL/SQL过程块; 也可以是对已创建存储过程的调用; [例18] 定义一个BEFORE行级触发器, 为教师表Teacher定义完整性规则“教授的工资不得低于4000元, 如果低于4000元,自动改为4000元”。 CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher /*触发事件是插入或更新操作*/ FOR EACH ROW /*行级触发器*/ AS BEGIN /*定义触发动作体,是PL/SQL过程块*/ IF (new.Job='教授') AND (new.Sal |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |