（七）数据库完整性

最新推荐文章于 2024-05-26 09:41:28 发布

xyzso1z

最新推荐文章于 2024-05-26 09:41:28 发布

阅读量6.4k

点赞数 7

分类专栏： SQL 文章标签： sql 数据库安全

本文链接：https://blog.csdn.net/xyzso1z/article/details/71088998

版权

SQL 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

一、实体完整性

1.实体完整性定义

关系模型的实体完整性在CREATE TABLE 中用PRIMARY KEY定义。对单属性构成的码有两种说明方法，一种是定义为列级约束条件，另一种是定义为表级约束条件。对多个属性构成的码只有一种说明方法，即定义为表级约束条件。

将Student表中的Sno属性定义为码。

CREATE TABLE Student
	(Sno CHAR(9) PRIMARY KEY,	/*在列级定义主码*/
	 Sname CHAR(20) NOT NULL,
	 Ssex CHAR(2),
	 Sage SMALLINT,
	 Sdept CHAR(20)
	);

或者

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.实体完整性检查和违约处理

用PRIMARY KEY短语定义了关系的主码后，每当用户程序对基本表插入一条记录或者对主码列进行更新操作时，DBMS将按实体完整性规则自动进行检查。包括：

1.检查主码值是否唯一，如果不唯一则拒绝插入或修改。

2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。

从而保证了实体完整性。

检查记录中主码值是否唯一的一种方法是进行全表扫描，以次判断表中每一条记录的主码值与将插入记录上的主码值（或者修改的新主码值）是否相同，如图所示：

全表扫描时十分耗时的。为了避免对基本表进行全表扫描，RDBMS核心一般都在主码上自动建立一个索引，如图的B+树索引，通过索引查找基本表中是否已经存在新的主码值，将大大提高效率。例如，如果新插入记录的主码值是25，通过主码索引，从B+树的根结点开始查找，只要读取3个节点就可以知道该主码值已经存在，所以不能插入这条记录。这3个结点是根节点（51）、中间结点（12 30）、叶节点（15 20 25）。

二、参照完整性

1.参照完整性定义

关系模型的参照完整性在CREATE TBALE 中用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.参照完整性检查和违约处理

一个参照完整性将两个表中的相应元组联系起来了。因此，对被参照表和参照表进行增删改操作时有可能破坏参照完整性，必须进行检查。

例如，对表SC和Student有四种可能破坏参照完整性的情况，如下表：

1.SC表中增加一个元组，该元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。

2.修改SC表中的一个元组，修改后该元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。

3.从Student表删除一个元组，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。

4.修改Student表中一个元组的Sno属性，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。

上述的不一致发生时，系统可以采用以下的策略加以处理。

1.拒绝（NO ACTION）执行

不允许该操作执行。该策略一般设置为默认策略。

2.级连（CASCADE)操作

当删除或修改被参照表（Student)的一个元组造成了与参照表（SC)的不一致，则删除或修改参照表中的所有造成不一致的元组。

例如，删除Student表中的元组，Sno值为14042401，则从要SC表中级连删除SC.Sno=‘14042401’的所元组。

3.设置为空值

当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

例如，有下面2个关系

学生（学号，姓名，性别，专业号，年龄）

专业（专业号，专业名）

学生关系的“专业号”是外码，因为专业号是专业关系的主码。

假设专业表中某个元组被删除，专业号为12，按照设置为空值的策略，就要把学生表中专业号=12的所有元组的专业号设置为空值。这对应了这样的语义：某个专业删除了，该专业的所有学生专业未定，等待重新分配专业。

这里要讲解一下外码能否接受空值的问题。

例如，这里的学生表中，“专业号”是外码，按照应用的实际情况可以取空值，表示这个学生的专业尚未确定。

但在学生-选课数据库中，Student关系为被参照关系，其主码为Sno。SC为参照关系，Sno是外码。若SC的Sno为空值，则表明上不存在的某个学生，或者某个不知学号的学生，选修了某门课程，其成绩记录在Grade列中。这与学校的应用环境是不相符，因此SC的Sno不能取空值。同样，SC的Cno列不能取空值。

因此对于参照完整性，除了应该定义外码，还应该定义外码列是否允许空值。

一般地，当对参照表和被参照表的操作违反了参照完整性，系统选用默认策略，即拒绝执行。如果想让系统采用其他策略则必须在创建表的显示地加以说明。

显示说明参照完整性的违约处理实例。

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	/*当删除student表中的元组时，级连删除SC表中相应的元组*/
	          ON UPDATE CASCADE,    /*当更新student表中的Sno时，级连更新SC表中相应的元组*/
	       FOREIGN KEY(Cno) REFERENCES Course(Cno) /*在表级定义参照完整性*/
	          ON DELETE NO ACTION	/*当删除course表中的元组造成了与SC表不一致时拒绝删除*/
	          ON UPDATE CASCADE,    /*当更新course表中的cno时，级连更新SC表中相应的元组*/
	       );

三、用户定义的完整性

1.属性上的约束条件的定义

在CREATE TABLE 中定义属性的同时可以根据应用要求，定义属性上的约束条件，即属性值限制，包括：

.列值非空（NOT NULL短语）

.列值唯一（UNIQUE短语）

.检查列值是否满足一个布尔表达式（CHECK语句）

1.不允许取空值

在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

CREATE TABLE SC
	(Sno CHAR(9) NOT NULL.	/*Sno属性不允许取空值*/
	 Cno CHAR(4) NOT NULL,	/*Cno属性不允许取空值*/
	 Grade SMALLINT NOT NULL, /*Grade属性不允许取空值*/
	 PRIMARY KEY(Sno,Cno), 	  /*如果在表级定义实体完整性，隐含了Sno,Cno不允许取空值则在列级不允许取空值的定义就不必写了*/
	 ……			/*其它语句省略*/
	);

2.列值唯一

建立部门表DEPT,要求部门名称Dname列取值唯一，部门编号Deptno列为主码。

CREATE TABLE DEPT
		(Deptno NUMERIC(2),
		 Dname CHAR(9) UNIQUE,	/*要求Dname列值唯一*/
		 Location CHAR(10),
		 PRIMARY KEY(Deptno)
		);

3.用CHECK短语自定列值应该满足的条件

Student 表的Ssex只允许取“男”或“女”。

CREATE TABLE Student
		(Sno CHAR(9) PRIMARY KEY,		/*在列级定义主码*/
		 Sname CHAR(8) NOT NULL,		/*Sname属性不允许取空值*/
		 Ssex CHAR(2) CHECK(Ssex IN('男‘，’女‘），	/*性别属性Ssex只允许取’男‘或’女‘*/
 		 Sage SMALLINT,
		 Sdept CHAR(20)
		);

SC表的Grade的值应该在0至100之间。

CREATE TABLE SC
	     (Sno CHAR(9) NOT NULL,
 	      Cno CHAR(4) NOT NULL,
	      Grade SMALLINT CHECK(Grade>=0AND Grade<=100), 	
              PRIMARY KEY (Sno,Cno),
	      FOREIGN KEY(Sno) REFERENCES Student(Sno),
	      FOREIGN KEY(Cno) REFERENCES Course(Cno)
 	     );

2.属性上的约束条件检查和违约处理

当往表中插入元组或修改属性的值时，DBMS就检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行。

3.元组上的约束条件的定义

与属性上约束条件的定义类似，在CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件，即元组级的限制。同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件。

当学生的性别是男时，其名字不能以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.打头，因为Ssex=’男‘时，条件要想为真值，Sname NOT LIKE 'MS.%'必须为真值。

4.元组上的约束条件检查和违约处理

当往表中插入元组或修改属性的值时，DBMS就检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行。

四、完整性约束命名子句

以上讲解的完整性约束约束条件都在CHECK TABLE 语句中定义。SQL还在CHECK TABLE 语句中提供了完整性约束命名子句CONSTRAINT,用来对完整性约束条件命名。从而可以灵活地增加、删除一个完整性约束条件。

1.完整性约束条件命名子句

CONSTRAINT <完整性约束条件名>[PRIMARY KEY 短语|FOREIGN KEY 短语|CHECK 短语]

建立学生登记表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<30),
		 Ssex CHAR(2)
		     CONSTRAINT C4 CHECK(Ssex IN('男' ,'女'),
		 CONSTRAINT StudentKey PRIMARY KEY(Sno)
		);

在 Student表上建立5个约束条件，包括主码约束（命名StudentKey)以及C1、C2、C3、C4四个列级约束。

建立教师表TEACHER,要求每个教师的应发工资不低于3000元。

应发工资实际上就是实发工资列Sal与扣除项Deduct之和。

CREATE TABLE TEACHER
		(Eno NUMERIC(4) PRIMARY KEY,
		 Ename CHAR(10),
		 Job CHAR(8),
 		 Sal NUMERIC(7,2),
		 Deduct NUMERIC(7,2),
		 Deptno NUMERIC(2),
		 CONSTRAINT EMPFKey FOREIGN KEY(Deptno) REFERENCES DEPT(Deptno),
		 CONSTRAINT C1 CHECK (Sal+Deduct>=3000)
		);

2.修改表中完整性限制

可以使用ALTER TABLE语句修改表中的完整性限制。

去掉Student表中对性别的限制。

ALTER TABLE Student
 DROP CONSTRAINT C4;

修改表Student中的约束条件，要求学好改为900000-999999之间，年龄由小于30改为小于40.（可以先删除原来的约束条件，再增加新的约束条件）

ALTER TABLE Student
  DROP CONSTRAINT C1;
ALTER TABLE Student
  ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999),
ALTER TABLE Student
  DROP CONSTRAINT C3;
ALTER TABLE Student
  ADD CONSTRAINT C3 CHECK(Sage<40);

五、域中的完整性限制

SQL可以用CREATE DOMAIN 语句建立一个域以及该域应该满足的完整性约束条件。

建立一个性别域，并声明性别域的取值范围。

CREATE DOMAIN GenderDomain CHAR(2)
	CHECK (VALUE IN('男','女');

这样Student表中对Ssex的说明可以改为

Ssex GenderDomain;

建立一个性别域GenderDomain，并对其中的限制命名。

CREATE DOMAIN GenderDomain CHAR(2)
	CONSTRAINT GD CHECK (VALUE IN('男','女');

删除域GenderDomain的限制条件GD。

ALTER DOMAIN GenderDomain
 DROP CONSTRAINT GD;

在域GenderDomain上增加限制条件GDD。

ALTER DOMAIN GenderDomain
	ADD CONSTRAINT GDD CHECK(VALUE IN ('1','0'));

六、触发器

1.定义触发器

SQL使用触发器CREATE TRIGGER命令建立触发器，其一般格式为：

CREATE TRIGGER <触发器>
{ BEFORE|AFTER } <触发事件> ON <表名>
FOR EACH { ROW | STATEMENT }
[WHEN <触发条件>]
<触发动作体>

下面对定义触发器的各部分语法进行详细说明：

1.表的拥有者即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器

2.触发器名

触发器名可以包含模式名，也可以不包含模式名。统一模式下，触发器名必须是唯一的；并且触发器名和<表名>必须在统一模式下。

3.表名

当这个表的数据发生变化时，将激活定义在该表上相应<触发事件>的触发器，因此，该表也称为触发器的目标表。

4.触发事件

触发事件可以使INSERT、DELETE或UPSATE，也可以是这几个事件的组合，如INSERT OR DELETE等。UPDATE后面还可以有OF<触发器,……>，即进一步指明修改哪些列时触发器激活。

5.触发器类型

触发器按照所触发动作的间隔尺寸可以分为行级触发器（FOR EACH ROW)和语句级触发器(FOR EACH STATEMENT).

6.触发条件

触发器被激活时，只有当触发条件为真时触发动作才执行；否则触发动作不执行。如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行。

7.触发动作体

触发动作体既可以是一个匿名PL/SQL过程块，也可以是对已创建存储过程的调用。如果是行级触发器，在两种情况下，用户都可以在过程体中使用NEW 和OLD引用UPDATE /INSERT事件之后的新值和UPDATE /DELETE事件之前的旧值。如果是语句级触发器，则不能触发动作体中使用NEW或OlD进行引用。

如果触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。

定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

CREATE TRIGGER InSert_Or_UpDate_Sal	/*在教师表Teacher上定义触发器*/
    BEFORE INSERT OR UPDATE ON Teacher  /*触发事件是插入或更新操作*/
    FOR EACH ROW			/*这是行级触发器*/
    AS BEGIN				/*定义触发动作体，这是一个PL/SQL过程块*/
        IF(new.job='教授')AND(new.Sal<4000)THEN	   /*因为是行级触发器，可在过程体中使用插入或更新操作后的新值*/
            new.Sal=4000;
        END IF;
    END;				/*触发动作体结束*/

定义AFTER行级触发器，当教师表Teacher的工资发生变化后就自动在工资变化表Sales_log中增加一条相应记录。

首先建立工资变化表Sal_log.

CREATE TABLE Sal_log
		(Eno NUMERIC(4) REFERENCES teacher(Eno),
 		 Sal NUMERIC(7,2),
		 Username CHAR(10),
		 Date TIMESTAMP
		);
CREATE TRIGGER Insert_Sal	/*建立了一个触发器*/
  AFTER INSERT OR Teacher	/*触发事件是INSERT*/
  FOR EACH ROW
      AS BEGIN
          INSERT INTO Sal_log VALUES(
               new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
          END;
CREATE TRIGGER Update_Sal	/*建立了一个触发器*/
   AFTER UPDATE ON Teacher	/*触发事件是UPDATE*/
   FOR EACH ROW
   AS BEGIN
     IF(new.Sal<>old.Sal) THEN
            INSERT INTO Sal_log VALUES(
                 new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
     END IF;
   END;

2.激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行的。一个数据表上可能定义了多个触发器，比如多个BEFORE触发器，多个AFTER触发器等。同一个表上的多个触发器激活时遵循如下执行顺序：

（1）执行该表上的BEFORE触发器；

（2）激活触发器的SQL语句；

（3）执行该表上的AFTER触发器。

对于同一个表上的多个BEFORE(AFTER)触发器，遵循“谁先创建谁先执行”的原则，即按照触发器创建的时间先后顺序执行。有些RDBMS是按照触发器名称的字母排序顺序执行触发器。

执行修改某个教师工资的SQL语句，激活上述定义的触发器。

UPDATE Teacher SET Sal =800 WHERE Ename="陈平"；

执行顺序是：

.执行触发器Insert_Or_Update_Sal;

.执行SQL语句”UPDATE Teacher SET Sal=800 WHERE Ename="陈平“；”；

.执行触发器Insert_Sal;

.执行触发器Update_Sal/

3.删除触发器

删除触发器的SQL语句：

DROP TRIGGER<触发器名>ON<表名>;

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。

DROP TRIGGER Insert_Sal ON Teacher;

xyzso1z

关注

7
点赞
踩
26

收藏

觉得还不错? 一键收藏
0
评论
（七）数据库完整性

一、实体完整性1.实体完整性定义关系模型的实体完整性在CREATE TABLE 中用PRIMARY KEY定义。对单属性构成的码有两种说明方法，一种是定义为列级约束条件，另一种是定义为表级约束条件。对多个属性构成的码只有一种说明方法，即定义为表级约束条件。将Student表中的Sno属性定义为码。CREATE TABLE Student (Sno CHAR(9) PRIMARY
复制链接

扫一扫