当前位置： 首页 > article >正文

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

article 2025/1/8 3:28:22

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

1. 数据库完整性概述

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

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

2. 实体完整性

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

2.1 定义实体完整性

  • 实体完整性约束：一个关系中的每个元组必须有一个唯一标识符（主键），且主键的值不能为空（NOT NULL）。
  • 实体完整性通过主键来实现，主键中的每个属性都必须有唯一值，并且不能为空。 
    ​
--实体完整性定义的语法
列名 类名数据类型 PRIMARY KEY，--将该列定义为主键（主码）

​

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

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

    -- 创建学生表，学号作为主键
CREATE TABLE Students (
    Sno CHAR(20) PRIMARY KEY,-- Sno 主键约束，确保学号唯一且不为NULL
    Sname 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是外码，被参照表是Student

    FOREIGN 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
BEGIN
    DECLARE @EmployeeID INT;
    SELECT @EmployeeID = EmployeeID FROM DELETED;  -- DELETED是一个虚拟表，包含被删除的数据
    DELETE FROM Orders WHERE EmployeeID = @EmployeeID;
END;

解释：

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

http://www.kler.cn/a/471006.html

相关文章：

  • pytorch中nn.Conv2d详解及参数设置原则
  • 【游戏设计原理】53 - 解决问题的障碍
  • WebSocket 实现指南
  • 微服务保护—Sentinel快速入门+微服务整合 示例: 黑马商城
  • 服务器Ubuntu22.04系统下 ollama的详细部署安装和搭配open_webui使用
  • 66.基于SpringBoot + Vue实现的前后端分离-律师事务所案件管理系统（项目 + 论文）
  • Go Ebiten游戏库入门教程
  • 【NLP高频面题 - Transformer篇】什么是缩放点积注意力，为什么要除以根号d？
  • 开源人工智能模型框架：探索与实践
  • Leetcode打卡：不含特殊楼层的最大连续楼层数
  • 一文讲清楚PostgreSQL分区表
  • [openGauss 学废系列]-用户和模式的关系以及访问方式
  • Scala语言的语法
  • java实验6 J.U.C并发编程
  • jEasyUI 创建页脚摘要
  • Linux驱动开发 gpio_get_value读取输出io的电平返回值一直为0的问题
  • 咖啡馆系统｜Java｜SSM｜JSP｜
  • [Unity Shader] 【游戏开发】Unity基础光照1-光照模型原理
  • QT中如何限制 限制QLineEdit只能输入字母，或数字，或某个范围内数字等限制约束？
  • 26考研资料分享 百度网盘
  • Chrome 浏览器下载安装教程，保姆级教程
  • linux系统（ubuntu,uos等)连接鸿蒙next(mate60)设备
  • 【prometheus】Pushgateway安装和使用
  • Devart dotConnect发布全新版本，支持EF Core 9、完全兼容 .NET 9 等！
  • Ubuntu24.04.1 LTS+Win11双系统安装记录
  • node.js之---内置模块