如何查看网站是否开启gzip,破天网站定制,国外网站空间 月付,wordpress分类目录html在数据库系统概论中#xff0c;数据库完整性是指确保数据库中数据的准确性、一致性和有效性的一组规则和约束。数据库完整性主要包括实体完整性、参照完整性和用户定义完整性。以下是详细的复习内容#xff1a;
1. 数据库完整性概述
数据库完整性是指一组规则#xff0c;这…在数据库系统概论中数据库完整性是指确保数据库中数据的准确性、一致性和有效性的一组规则和约束。数据库完整性主要包括实体完整性、参照完整性和用户定义完整性。以下是详细的复习内容
1. 数据库完整性概述
数据库完整性是指一组规则这些规则保证数据库中的数据是有效的、准确的并且符合预定的要求。它通过一系列的完整性约束来控制数据的有效性和一致性。常见的完整性约束包括
实体完整性确保每个实体在表中有唯一标识。参照完整性确保表与表之间的引用关系有效。用户定义完整性确保数据符合用户定义的特定规则。
2. 实体完整性
实体完整性确保每个关系表中的行元组可以通过某一列或一组列来唯一标识通常使用主键来实现。即每个关系表中必须要有一个主键这里的主键可以是单个主键也可以是复合主键
2.1 定义实体完整性
实体完整性约束一个关系中的每个元组必须有一个唯一标识符主键且主键的值不能为空NOT NULL。实体完整性通过主键来实现主键中的每个属性都必须有唯一值并且不能为空。
--实体完整性定义的语法
列名 类名数据类型 PRIMARY KEY--将该列定义为主键主码 具体的定义实体完整性的实例代码 列级完整性约束定义即只有一个主码 -- 创建学生表学号作为主键
CREATE TABLE Students (Sno CHAR(20) PRIMARY KEY,-- Sno 主键约束确保学号唯一且不为NULLSname CHAR(20) UNIQUE,--这里的UNIQUE是要保证学生名字不重复Sex CHAR(6),Smajor VARCHAR(40)
);表级完整性约束即有多个候选码构成主码 -- 创建学生选课表学号和课程号作为主键
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); -- 此操作会失败因为StudentID1跟上面那条插入语句里的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); -- 成功因为StudentID1在Students表中存在-- 尝试向Enrollments表插入无效的外键值
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseName)
VALUES (2, 2, Science); -- 失败因为StudentID2在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; -- 失败因为StudentID1在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 触发器的类型
BEFORE 触发器在数据修改插入、更新、删除之前执行。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中。
步骤
创建Employees表。创建InsertLog表用来记录插入操作。创建触发器确保每次插入员工信息时都会记录到日志表。 -- 创建员工表
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表中会包含被删除的员工信息。
