数据库系统-数据库的完整性

小旺不正经
119 阅读7分钟

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第16天，点击查看活动详情

数据库系统-数据库的完整性

数据库完整性指的是数据的正确性和相容性

数据的正确性：指数据是符合现实世界语义、反映当前实际状况的

数据的相容性：指数据库同一对象在不同关系表中的数据是符合逻辑的

数据库管理系统就应该为数据完整性实现如下功能：

  • 提高定义完整性约束条件的机制

    完整约束条件也称完整性规则，是数据库中数据必须满足的语义条件规则

    为保证数据的正确、有效和相容性的一些规则

    数据的主码、外码、一些约束规则

  • 提供完整性检查的方法

    数据库管理系统中检查数据是否满足完整性规则的机制称我完整性检查

    一般在执行INSERT、UPDATE、DELETE时检查

  • 违约处理

    数据库管理系统若发现用户的操作违背了完整性约束条件将采取一定的动作，如拒绝（NO ACTION）执行该操作或级联(CASCADE)执行其他操作，进行违约处理以保证数据的完整性

实体完整性-PRIMARY KEY

实体完整性的定义：主码唯一且非空

关系模型的实体完整性

  • CREATE TABLE中用 PRIMARY KEY定义

单属性构成的码有两种声明方法

  • 定义我列级约束条件
  • 定义我表级约束条件

对多个属性构成的码只有一种说明方法

  • 定义我表级约束条件

实体完整性检查和违约处理

插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查，包括：

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

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

     在列级定义主码
     CARATE 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)
     );

参照完整性-REFERENCES

参照完整性定义

参照完整性规则就是定义外键与被参照的主键之间的引用规则 外键或者值为空，或者等于其所参照的关系中的某个元组的主键值

在CREATE TABLE 中用FOREIGN KEY短语定义哪些列我外码

用REFERENCES短语指明这些外码参照哪些表的主码

参照完整性违约处理

拒绝（NO ACTION）执行 默认策略

级联（CASCADE）操作

设置我空值（SET-NULL）

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

     Sno，Cno分别参照引用Student表的主码和Course表的主码
     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)
     );

     显式说明参照完整性的违约处理示例
     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
         级联删除对应元组
      ON UPDATE CASCADE,
         级联更新对应元组
      FOREIGN KEY (Cno) REFERENCES Course(Cno)
      ON DELETE NO ACTION
         删除时造成与SC表不一致时拒绝删除
      ON UPDATE CASCADE
         级联删除对应元组
     );

用户定义的完整性-CHECK

用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求，用户可以自定义完整性机制，由数据库管理系统检查，并给出结果。实际上就是指明关系中属性的取值范围，防止属性的值与应用语义矛盾

属性上的约束条件定义（CREATE TABLE时定义）

  • 列值非空（NOT NULL）
  • 列值唯一（UNIQUE）
  • 检查列值是否满足一个布尔表达式(CHECK)

建立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),
     );

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

     CREATE TABLE DEPT
     (
      Deptno NUMERIC(2),
      Dname CHAR(9) UNIQUE,
      Location CHAR(10),
      PRIMARY KEY (Deptno)
     );

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

     CREATE TABLE Student
     (
      Sno CHAR(9) PRIMARY KEY,
      Sname CHAR(8) NOT NULL,
      Ssex CHAR(2) CHECK(Ssex IN ('男','女')),
      Sage SMALLINT,
      Sdept CHAR(20)
     );

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

插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足

如果不满足则操作被拒绝执行

元组上的约束条件的定义

在CREATE TABLE 时可以用CHECK短语定义元组上的约束条件，即元组级的限制

同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件

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

  • 插入元组或修改属性的值，RDBMS检查元组上的约束条件是否被满足
  • 如果不满足则操作被拒绝执行

当学生的性别是男时，其名字不能以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.%')
	)

完整性约束命名子句-CONSTRAINT

CONSTRAINT 约束语句格式：

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

建立学生登记表Student，要求学号在90000-99999之间，姓名不能取空值，年龄小于30，性别只能是‘男’或‘女’

	CREATE TABLE Student
	(
		Sno NUMERIC(6)
		CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 999999),
		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四个列级约束

修改表中的完整性限制

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

修改表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);

域中的完整性限制-DOMAIN

SQL支持域的概念，并可以用CREATE DOMAIN语句建立一个域以及该满足的完整性约束条件

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

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

建立一个性别域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'));

断言-ASSERTION

断言还是和域一样，不同的数据库可能实现方式不同

在SQL中可以使用数据定义语言中的CREATE ASSERTION语句，通过声明性断言（declarative assertions）来指定更具一般性的约束

可以定义涉及多个表或聚焦操作的比较复杂的完整性约束

断言创建以后，任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值的操作都会被拒绝执行

定义断言

标准语句格式：

	CREATE ASSERTION <断言名><CHECK子句>;

每个断言都被赋予一个名字，<CHECK子句>中的约束条件与WHERE子句的条件表达式类似

限制每一个门课程最多60名学生选修

	CREATE ASSERTION ASSE_SC_CNUMI
	CHECK(60>=ALL(SELECT count(*) FROM SC GROUP by cno));

删除断言

DROP ASSERTION <断言名>;

如果断言很复杂，则系统在检测和维护断言上的开销较高，这是在使用断言时应该注意的

触发器-TRIGGER

触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是自动调用而执行的。

而存储工程则需要主动调用其名字执行

触发器：trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行

定义触发器

CREATE TRIGGER语法格式：

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

定义触发器的语法说明：

1.创建者：表的拥有者

2.触发器名

3.表名：触发器的目标表，不能建立在视图上

4.触发事件：INSERT、DELETE、UPDATE

5.触发器类型

行级触发器（FOR EACH ROW）

语句级触发器（FOR EACH STATEMENT）

6.触发条件

触发条件为真，则执行触发动作体，否则不执行

省略WHEN触发条件时，只要触发器激活（触发器事件）则立刻执行触发动作体

7.触发动作体

触发动作体可以是一个匿名PL\SQL过程块

也可以是对已创建存储过程的调用

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

	CREATE TRIGGER Insert_Or_Update_Sal
		BEFORE INSERT OR UPDATE ON Teacher
		FOR EACH ROW
		AS BEGIN
			IF(new.job='教授')AND (new.Sal<4000) THEN
			new.Sal=4000;
			END IF;
		END;

激活触发器

触发器的执行，是由触发器事件激活的，并由数据库服务器自动执行。

一个数据表上可能定义了多个触发器

同一个表上的多个触发器激活时遵循如下的执行顺序：

  • 执行该表上的BEFORE 触发器
  • 激活触发器的SQL语句
  • 执行表上的AFTER触发器

删除触发器

删除触发器的SQL语句

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

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

删除教师表Teacher上的触发器Insert_Sal

	DROP TRIGGER Insert_Sal NO Teacher;

\

avatar
文章
阅读
粉丝