【数据库系统概论】数据库完整性与触发器--复习

在数据库系统概论中，数据库完整性是指确保数据库中数据的准确性、一致性和有效性的一组规则和约束。数据库完整性主要包括实体完整性、参照完整性和用户定义完整性。以下是详细的复习内容：

1. 数据库完整性概述

数据库完整性是指一组规则，这些规则保证数据库中的数据是有效的、准确的，并且符合预定的要求。它通过一系列的完整性约束来控制数据的有效性和一致性。常见的完整性约束包括：

• 实体完整性：确保每个实体在表中有唯一标识。
• 参照完整性：确保表与表之间的引用关系有效。
• 用户定义完整性：确保数据符合用户定义的特定规则。

2. 实体完整性

实体完整性确保每个关系表中的行（元组）可以通过某一列或一组列来唯一标识，通常使用主键来实现。（即每个关系表中必须要有一个主键，这里的主键可以是单个主键也可以是复合主键）

2.1 定义实体完整性

• 实体完整性约束：一个关系中的每个元组必须有一个唯一标识符（主键），且主键的值不能为空（NOT NULL）。
• 实体完整性通过主键来实现，主键中的每个属性都必须有唯一值，并且不能为空。

  ​
  --实体完整性定义的语法
  列名 类名数据类型 PRIMARY KEY，--将该列定义为主键（主码）​

  具体的定义实体完整性的实例代码：

1. 列级完整性约束定义（即只有一个主码）：

   -- 创建学生表，学号作为主键
   CREATE TABLE Students (Sno CHAR(20) PRIMARY KEY,-- Sno 主键约束，确保学号唯一且不为NULLSname CHAR(20) UNIQUE,--这里的UNIQUE是要保证学生名字不重复Sex CHAR(6),Smajor VARCHAR(40)
   );
   

2. 表级完整性约束（即有多个候选码构成主码）：

   -- 创建学生选课表，学号和课程号作为主键
   CREATE TABLE SC(Sno CHAR(8),Cno CHAR(5),Grade SMALLINT,Semester CHAR(5),Teachingclass CHAR(8),,PRIMARY KEY(Sno,Cno)  -- Sno,Cno主键约束，确保(学号,课程号)唯一且不为NULL
   );
   

2.2 实体完整性检查

• 实体完整性检查是对主键约束的验证，确保插入或更新数据时，主键字段没有重复或空值。

2.3 实体完整性违约检查

• 当插入的数据违反了主键约束，如主键字段为空或重复，数据库会拒绝插入操作，并返回错误信息。

SQL示例：

-- 创建学生表，学号为主键
CREATE TABLE Students (StudentID INT PRIMARY KEY,  -- 主键约束StudentName VARCHAR(50),Age INT
);-- 插入数据（违反实体完整性约束，学号重复）
INSERT INTO Students (StudentID, StudentName, Age)
VALUES (1, 'Alice', 20);INSERT INTO Students (StudentID, StudentName, Age)
VALUES (1, 'Bob', 22);  -- 此操作会失败，因为StudentID=1跟上面那条插入语句里的StudenID重复了，如果这里改成VALUES (2, 'Bob', 22);的话可以成功插入

3. 参照完整性

参照完整性确保数据库表之间的外键关系有效，外键的值必须在被引用的表中存在。

3.1 定义参照完整性

• 参照完整性约束：表中的外键列的值必须在被引用表的主键列中存在，或者外键列的值可以为NULL。

  CREATE TABLE SC(Sno CHAR(8),Cno CHAR(5),Grade SMALLINT,Semester CHAR(5),Teachingclass CHAR(8),PRIMARY KEY(Sno,Cno),--定义表级完整性FOREIGN KEY(Sno) REFERENCES Student(Sno),--在表级定义参照完整性，Sno是外码，被参照表是StudentFOREIGN KEY(Cno) REFERENCES Course(Cno)--在表级定义参照完整性，Cno是外码，被参照表是Course
  );

3.2 参照完整性检查

• 参照完整性检查的是外键字段的值是否在被引用的父表中存在，或者外键值是否为 NULL（如果外键字段允许为 NULL，则子表中可以有外键列的值为空（NULL））。这样可以确保表与表之间的引用关系是准确和一致的。

3.3 参照完整性违约检查

• 插入：插入外键值时，必须检查外键值是否在引用表中存在。当向子表插入数据时，数据库会检查外键值是否在父表中存在。如果外键值不在父表的主键或唯一键列中，插入操作将会失败。

  -- 创建父表：Students
  CREATE TABLE Students (StudentID INT PRIMARY KEY,StudentName VARCHAR(50)
  );-- 创建子表：Enrollments，外键StudentID引用Students表中的StudentID
  CREATE TABLE Enrollments (EnrollmentID INT PRIMARY KEY,StudentID INT,CourseName VARCHAR(50),FOREIGN KEY (StudentID) REFERENCES Students(StudentID)  -- 外键约束
  );-- 向Students表插入数据
  INSERT INTO Students (StudentID, StudentName) 
  VALUES (1, 'Alice');-- 尝试向Enrollments表插入数据，插入有效的外键值
  INSERT INTO Enrollments (EnrollmentID, StudentID, CourseName)
  VALUES (1, 1, 'Math');  -- 成功，因为StudentID=1在Students表中存在-- 尝试向Enrollments表插入无效的外键值
  INSERT INTO Enrollments (EnrollmentID, StudentID, CourseName)
  VALUES (2, 2, 'Science');  -- 失败，因为StudentID=2在Students表中不存在
  

• 删除：如果父表的某行数据被删除，且该行数据在子表中作为外键引用，数据库系统会阻止删除操作，除非使用级联删除（CASCADE DELETE）。在没有级联删除的情况下，父表中的记录不能被删除，因为它们在子表中有外键引用。使用 ON DELETE CASCADE 后，当删除父表中的记录时，相关的子表记录也会被自动删除。

  -- 尝试删除Students表中的学生数据
  DELETE FROM Students WHERE StudentID = 1;  -- 失败，因为该学生在Enrollments表中有外键引用-- 使用级联删除：删除学生时自动删除相关的注册记录
  CREATE TABLE Enrollments (EnrollmentID INT PRIMARY KEY,StudentID INT,CourseName VARCHAR(50),FOREIGN KEY (StudentID) REFERENCES Students(StudentID) ON DELETE CASCADE  -- 级联删除
  );-- 删除学生数据时，相关的注册记录也会自动删除
  DELETE FROM Students WHERE StudentID = 1;  -- 成功，相关的Enrollments记录也被删除
  

• 更新：当更新父表中的主键值时，数据库会检查是否有子表中的外键引用该主键。如果有引用，数据库会阻止更新操作，除非使用了适当的级联更新（CASCADE UPDATE）操作。

-- 尝试更新父表中的主键
UPDATE Students SET StudentID = 2 WHERE StudentID = 1;  -- 失败，因为StudentID=1在Enrollments表中有外键引用-- 使用级联更新：更新父表主键时自动更新子表中的外键
CREATE TABLE Enrollments (EnrollmentID INT PRIMARY KEY,StudentID INT,CourseName VARCHAR(50),FOREIGN KEY (StudentID) REFERENCES Students(StudentID) ON UPDATE CASCADE  -- 级联更新
);-- 更新父表中的主键时，相关的子表记录中的外键会自动更新
UPDATE Students SET StudentID = 2 WHERE StudentID = 1;  -- 成功，Enrollments表中的外键也被更新

4. 用户定义完整性

用户定义的完整性是数据库中用户根据具体业务需求而设定的规则。这些规则是由数据库的使用者（比如开发者或者数据库管理员）根据实际需要来定义的，主要用来确保数据符合业务逻辑。

4.1 属性上的约束

属性约束是用来限制单个字段中的数据，比如限制字段的值范围或格式，确保数据符合预期。

4.1.1 范围约束（检查字段的值是否在特定范围内）

举个例子，假设我们要求员工的年龄必须大于等于18岁。我们可以在数据库中添加一个约束，确保年龄字段的值始终满足这个条件。

-- 创建员工表，确保年龄大于等于18岁
CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,EmployeeName VARCHAR(50),Age INT CHECK (Age >= 18)  -- 确保员工年龄 >= 18
);

这个约束确保插入的数据中，Age字段的值必须大于或等于18。如果你尝试插入18岁以下的员工，数据库会拒绝这个操作。 

4.1.2 格式约束（确保字段数据格式正确）

有时候，我们希望某个字段的数据符合特定的格式，比如，电子邮件必须符合标准格式。虽然 SQL 本身没有直接的正则表达式支持，但你可以用 CHECK 约束来大致确保格式正确。

-- 创建用户表，确保电子邮件格式正确
CREATE TABLE Users (UserID INT PRIMARY KEY,Email VARCHAR(100),CHECK (Email LIKE '%_@__%.__%')  -- 确保Email符合简单的邮箱格式
);

4.1.3 唯一性约束（确保字段数据唯一） 

有时候，我们希望某个字段的值在表中是唯一的，例如，确保每个用户的邮箱地址唯一。

-- 创建用户表，确保每个邮箱地址唯一
CREATE TABLE Users (UserID INT PRIMARY KEY,Email VARCHAR(100) UNIQUE  -- 确保邮箱地址唯一
);

4.2 元组上的约束

元组约束是对多列（多字段）数据的限制。通常用于确保某些字段的组合是唯一的或满足特定的条件。

4.2.1 组合唯一性约束（确保某些字段组合的值唯一）（组合唯一性约束也叫复合主键唯一性约束）

例如，假设你有一个“学生选课表”，每个学生可以选多门课程，但每个学生只能选一门课程一次。你可以使用组合主键来确保每个学生-课程的组合唯一。

-- 创建学生选课表，确保每个学生只能选修每门课程一次
CREATE TABLE StudentCourses (StudentID INT,CourseID INT,EnrollmentDate DATE,PRIMARY KEY (StudentID, CourseID),  -- 组合主键，确保每个学生每门课程只有一条记录FOREIGN KEY (StudentID) REFERENCES Students(StudentID),FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

在这个例子中，StudentID 和 CourseID 组成了一个复合主键，这确保了一个学生不能重复选同一门课程。 

5. 完整性约束命名句子

完整性约束的命名句子通常包括：

• 主键约束：PRIMARY KEY，确保数据行的唯一性。
• 外键约束：FOREIGN KEY，确保表之间的引用关系。
• 检查约束：CHECK，限制数据值的范围。
• 唯一性约束：UNIQUE，确保列中没有重复的值。

6. 触发器

触发器是数据库中一种特殊的对象，它可以在指定的事件发生时自动执行某些操作。换句话说，触发器是一种自动执行的数据库操作，它与某个表或视图关联，并且根据预定义的规则在插入（INSERT）、更新（UPDATE）或删除（DELETE）等数据库操作发生时自动触发执行。

6.1 定义触发器

触发器是绑定到表上的一类对象，可以在INSERT、UPDATE、DELETE等操作前后触发，自动执行相应的操作。

6.2 触发器的应用场景

• 自动审计：记录表中的数据变化。
• 数据验证：在数据修改之前，检查数据是否符合规定的规则。
• 级联操作：在删除父表记录时，自动删除子表中的相关记录（级联删除）。
• 自动更新：当某一列数据更新时，自动更新其他相关字段。

6.3 触发器的类型

1. BEFORE 触发器：在数据修改（插入、更新、删除）之前执行。
2. AFTER 触发器：在数据修改（插入、更新、删除）之后执行。

6.4 创建触发器

触发器可以在数据插入、删除或更新时执行，如日志记录、自动更新其他表等。

CREATE TRIGGER trigger_name  -- 触发器名称
[BEFORE | AFTER] operation  -- 在操作前或后触发
ON table_name  -- 触发器关联的表
[FOR EACH ROW]  -- 针对每一行数据触发
BEGIN-- 触发器中执行的 SQL 操作
END;

6.5 删除触发器

删除触发器是在数据删除时触发的操作，通常用于执行相关的清理或审计任务。

DROP TRIGGER trigger_name;

6.6 实例：创建触发器

例子 1：插入数据时记录日志

假设我们有一个Employees表，用来记录员工信息。每次向Employees表插入数据时，我们希望自动将插入的数据记录到日志表InsertLog中。

步骤：

1. 创建Employees表。
2. 创建InsertLog表，用来记录插入操作。
3. 创建触发器，确保每次插入员工信息时，都会记录到日志表。

   -- 创建员工表
   CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,EmployeeName VARCHAR(50),Position VARCHAR(50)
   );-- 创建插入日志表
   CREATE TABLE InsertLog (LogID INT PRIMARY KEY IDENTITY(1,1),EmployeeID INT,LogDate DATETIME,Action VARCHAR(50)
   );-- 创建触发器，每次插入员工数据时自动记录到日志表
   CREATE TRIGGER LogInsert
   ON Employees
   AFTER INSERT  -- 插入数据后触发
   AS
   BEGIN-- 获取插入的员工ID，并记录到InsertLog表中DECLARE @EmployeeID INT;SELECT @EmployeeID = EmployeeID FROM INSERTED;  -- INSERTED是一个虚拟表，包含插入的数据INSERT INTO InsertLog (EmployeeID, LogDate, Action)VALUES (@EmployeeID, GETDATE(), 'INSERT');
   END;
   

解释：

• 当你向Employees表插入数据时，触发器LogInsert会被自动执行。
• 触发器会从INSERTED虚拟表中获取新插入的EmployeeID，然后将这些信息插入到InsertLog表中，记录日志。

插入数据时触发器自动执行：

-- 向Employees表插入数据
INSERT INTO Employees (EmployeeID, EmployeeName, Position)
VALUES (1, 'Alice', 'Manager');

插入数据后，InsertLog表中会自动生成一条记录，记录该插入操作。

---

例子 2：删除员工时级联删除其相关数据

假设我们有一个Orders表，记录了员工的订单信息。我们希望在删除Employees表中的员工记录时，自动删除与该员工相关的订单记录。

-- 创建订单表
CREATE TABLE Orders (OrderID INT PRIMARY KEY,EmployeeID INT,OrderDate DATETIME,FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);-- 创建触发器，在删除员工时级联删除相关订单
CREATE TRIGGER CascadeDeleteOrders
ON Employees
AFTER DELETE  -- 在删除员工后触发
AS
BEGINDECLARE @EmployeeID INT;SELECT @EmployeeID = EmployeeID FROM DELETED;  -- DELETED是一个虚拟表，包含被删除的数据DELETE FROM Orders WHERE EmployeeID = @EmployeeID;
END;

解释：

• 这个触发器在删除Employees表中的员工记录时，会自动删除Orders表中与该员工相关的所有订单记录。
• DELETED是一个虚拟表，包含已删除的记录。在触发器执行时，DELETED表中会包含被删除的员工信息。