Bobby Blog

large picture of the cover

数据库系统概论-基础篇-数据库完整性

  • 数据库的完整性是指数据的正确性和相容性。
  1. 数据的正确性：是指数据是符合现实世界语义，反映了当前实际情况的；例如，学生的学号必须唯一，性别只能是男或女。
  2. 数据的相容性：是指数据库同一对象在不同关系表中的数据是符合逻辑的；例如，学生所选的课程必须是学校开设的课程，学生所在的院系必须是学校已成立的院系。
  • 数据的完整性和安全性是两个不同的概念。
  1. 数据的完整性：防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据；防范对象是不合语义的、不正确的数据。
  2. 数据的安全性：保护数据库，防止恶意的破坏和非法的存取；防范对象是非法用户和非法操作。

完整性是阻止合法用户通过合法操作向数据库中加入不正确的数据。

安全性防范的是非法用户和非法操作存取数据库中的正确数据。

  • 为维护数据库的完整性，DBMS必须：
  1. 提供定义完整性约束条件的机制。
  2. 提供完整性检查机制。
  3. 进行违约处理。
  • 由DBMS进行完整性检查的好处：
  1. 不必由应用程序来完成，从而减轻了应用程序员的负担。
  2. 能够为所有的用户和所有的应用提供一致的数据库完整性，避免出现漏洞。

实体完整性

定义实体完整性

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

例1：Sno为Student表的主码

CREATE TABLE Student (
	Sno VARCHAR ( 9 ) PRIMARY KEY,
	Sname VARCHAR ( 20 ) NOT NULL,
	Ssex VARCHAR ( 2 ),
	Sage SMALLINT,
Sdept VARCHAR ( 20 ) 
);

例2：Sno，Cno为SC表的主码

CREATE TABLE `SC` (
	`Sno` VARCHAR ( 10 ) NOT NULL,
	`Cno` VARCHAR ( 4 ) NOT NULL,
	`Grade` SMALLINT DEFAULT NULL,
PRIMARY KEY ( `Sno`, `Cno` ) 
);

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

用PRIMARY KEY短语定义了关系的主码后，插入或对主码列进行更新操作时，关系数据库管理系统按照实体完整性规则自动进行检查：检查主码值是否唯一，如果不唯一则拒绝插入或修改；检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。

检查记录中主码值是否唯一的一种方法是进行全表扫描：依次判断表中每一条记录的主码值与将插入记录上的主码值（或修改的新主码值）是否相同；缺点是十分耗时。

为避免对基本表进行全表扫描，RDBMS一般都在主码上自动建立一个索引。如下图的B+树索引，通过索引查找基本表中是否已经存在新的主码值将大大提高效率。

B树索引检查主码是否唯一.png

例如，如果新插入记录的主码值是25，通过主码索引，从B+树的根结点开始查找，只要读取三个结点就可以知道该主码值已经存在，所以不能插入这条记录。

参照完整性

定义参照完整性

关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。

例1：SC表中Sno，Cno分别参照引用Studnet表和Course表的主码

CREATE TABLE `SC` (
	`Sno` VARCHAR ( 10 ) NOT NULL,
	`Cno` VARCHAR ( 4 ) NOT NULL,
	`Grade` SMALLINT DEFAULT NULL,
	PRIMARY KEY ( `Sno`, `Cno` ),
	FOREIGN KEY ( `Sno` ) REFERENCES `Student` ( `Sno` ),
FOREIGN KEY ( `Cno` ) REFERENCES `Course` ( `Cno` ) 
);

参照完整性检查和违约处理

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

违约处理一般分为：拒绝执行；级联操作；设置为空值；

用户定义的完整性

用户定义的完整性是针对某一具体应用的数据必须满足的语义要求。RDBMS提供了定义和检验用户定义完整性的机制，不必由应用程序承担。

属性上的约束条件

属性上约束条件的定义

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

  • 列值非空（NOT NULL）。
  • 列值唯一（UNIQUE）。
  • 检查列值是否满足一个条件表达式（CHECK短语）。

例：使用CHECK短语指定列值应该满足的条件；

  • Student表的Ssex只允许取男或女

    CREATE TABLE `Student` (
  `Sno` varchar(10)  NOT NULL,
  `Sname` varchar(20)  DEFAULT NULL,
  `Ssex` char(2) CHECK (Ssex IN ('男','女')),
  `Sage` smallint DEFAULT NULL,
  `Sdept` varchar(20)  DEFAULT NULL,
  PRIMARY KEY (`Sno`),
  UNIQUE KEY `Sname` (`Sname`)
) ;

  • SC表的Grade的值应该在0到100之间

CREATE TABLE `SC` (
  `Sno` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `Cno` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
  `Grade` smallint CHECK (Grade >= 0 AND Grade <= 100),
  PRIMARY KEY (`Sno`,`Cno`),
  KEY `Cno` (`Cno`),
  FOREIGN KEY (`Sno`) REFERENCES `Student` (`Sno`),
  FOREIGN KEY (`Cno`) REFERENCES `Course` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

元组上的约束条件

元组上约束条件的定义

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

例：当学生的性别是男时，其名字不能以Ms开头

CREATE TABLE `Student` (
  `Sno` varchar(10)  NOT NULL,
  `Sname` varchar(20)  DEFAULT NULL,
  `Ssex` char(2) CHECK (Ssex IN ('男','女')),
  `Sage` smallint DEFAULT NULL,
  `Sdept` varchar(20)  DEFAULT NULL,
  PRIMARY KEY (`Sno`),
  UNIQUE KEY `Sname` (`Sname`),
	CHECK (Ssex = '女' OR Sname NOT LIKE 'Ms%')
) ;

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

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

完整性约束命名子句

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

完整性约束命名子句

CONSTRAINT <完整性约束条件名> <完整性约束条件>

<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK短语等。

例1：建立学生表Student，要求学号在90000~99999之间，年龄小于30，性别只能是男或女

CREATE TABLE Student (
	`Sno` INT  PRIMARY KEY,
	`Sname` VARCHAR(10) NOT NULL,
	`Sage` SMALLINT ,
	`Ssex` VARCHAR(5) ,
	 CONSTRAINT `C1` CHECK (Sno BETWEEN 90000 AND 99999),
	 CONSTRAINT `C2` CHECK (Sage < 30),
	 CONSTRAINT `C3` CHECK (Ssex IN ('男','女'))
);

修改表中的完整性限制

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

例2：去掉Student表中对性别的限制

ALTER TABLE Student DROP CONSTRAINT `C3`;

例3：修改表Studnet中的约束条件，年龄由小于30改为小于40

ALTER TABLE Student DROP CONSTRAINT `C2`;
ALTER TABLE Student ADD CONSTRAINT `C2` CHECK (Sage < 40);

触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程。任何用户对表的增、删、改操作均有服务器自动激活相应的触发器。可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

定义触发器

CREATE
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

trigger_name：同一模式下，触发器名的名称必须是唯一的；

trigger_time：触发器执行时间。可以是BEFORE或AFTER，表示在要修改的每一行之前或之后激活触发器；

trigger_event：激活触发器的操作类型，可以是INSERT、UPDATE、DELETE；

tbl_name：必须是一个永久表，不能定义在视图上；

trigger_order：可以为具有相同触发器事件和动作时间的给定表定义多个触发器，默认情况下，具有相同触发事件和动作时间的触发器按创建顺序激活，FOLLOWS新触发器在现有出发器之后执行，PRECEDES新的触发器在现有触发器之前激活；

trigger_body：触发器激活时要执行的语句。要执行多条语句，使用BEGIN…END复合语句构造。这使能够使用存储例程中所允许的相同语句。在body中可以使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/DELETE事件之前的旧值。

例1：当对表SC 的Grade 属性进行修改时，若分数增加了10% 则将此次操作记录到SC_U 表中SC_U（Sno,Cno,Oldgrade,Newgrade ）

CREATE TRIGGER `ST_T` AFTER UPDATE ON `SC` FOR EACH ROW 
BEGIN 
	IF OLD.Grade * 1.1 <= NEW.Grade THEN 
	INSERT INTO `S_T`.`SC_U`(`Sno`, `Cno`, `Oldgrade`, `Newgrade`) VALUES ( NEW.Sno, NEW.Cno, OLD.Grade, NEW.Grade);
	END IF;
END;

激活触发器

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

  1. 执行该表上的BEFORE触发器；
  2. 激活触发器的SQL;
  3. 执行该表上的AFTER触发器;

删除触发器

DROP TRIGGER [schema_name.]trigger_name

如果删除表，则也会删除表的触发器。



参考文献：数据库系统概论-第5版[王珊，萨师煊]

site logo

Hi,Developer

© 2025 Bobby Blog