【数据库原理】SQL 用户自定义完整性

用户自定义完整性是指用户根据具体业务需求，在数据库中对数据定义的一些特定约束，用于保证数据的正确性、完整性和一致性。用户自定义完整性通常是应用在字段（属性）、记录（元组）或表之间的一些特定约束条件，是对实体完整性和参照完整性的补充。

 

1. 用户自定义完整性的特点

    •    灵活性：根据具体业务需求进行定义。

    •    多样性：可以针对单个字段、多个字段或记录之间的关系设定。

    •    实现方式：

    •    约束（Constraints）：如 CHECK 、DEFAULT、UNIQUE。

    •    触发器（Triggers）：实现更复杂的逻辑。

 

2. 用户自定义完整性的实现方式

2.1 使用 CHECK 约束

CHECK 约束可以限制列或记录的值必须满足某些条件。

例1：限制员工工资必须大于 0

CREATE TABLE Employees (

    EmpID INT PRIMARY KEY,

    EmpName VARCHAR(50),

    Salary DECIMAL(10, 2) CHECK (Salary > 0)  -- 自定义约束：工资必须大于 0

);

 

例2：限制学生年龄在 18 到 30 岁之间

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    StudentName VARCHAR(50),

    Age INT CHECK (Age BETWEEN 18 AND 30)  -- 自定义约束：年龄范围限制

);

 

2.2 使用 DEFAULT 约束

DEFAULT 用于为列设置默认值，当插入数据时，如果未指定该列的值，则自动填充默认值。

例1：默认订单状态为 “未支付”

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    OrderAmount DECIMAL(10, 2),

    OrderStatus VARCHAR(20) DEFAULT '未支付'  -- 自定义约束：默认值

);

 

-- 插入数据时未指定 OrderStatus，则默认为 '未支付'

INSERT INTO Orders (OrderID, OrderAmount) VALUES (1, 100.00);

 

2.3 使用 UNIQUE 约束

UNIQUE 限制某列的值在整个表中唯一。

例1：限制用户名唯一

CREATE TABLE Users (

    UserID INT PRIMARY KEY,

    UserName VARCHAR(50) UNIQUE  -- 自定义约束：用户名必须唯一

);

 

2.4 使用触发器（Triggers）

触发器用于实现更复杂的用户自定义完整性约束，如跨表检查或动态限制。

例1：禁止插入年龄小于 18 的学生

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    StudentName VARCHAR(50),

    Age INT

);

 

-- 创建触发器

CREATE TRIGGER AgeCheck

BEFORE INSERT ON Students

FOR EACH ROW

BEGIN

    IF NEW.Age < 18 THEN

        SIGNAL SQLSTATE '45000' 

        SET MESSAGE_TEXT = '学生年龄必须大于或等于 18';

    END IF;

END;

 

-- 测试插入数据

INSERT INTO Students (StudentID, StudentName, Age) VALUES (1, '张三', 17);  -- 会触发错误

 

例2：当订单总金额超过 10,000 时记录到日志表

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    OrderAmount DECIMAL(10, 2)

);

 

CREATE TABLE OrderLogs (

    LogID INT AUTO_INCREMENT PRIMARY KEY,

    OrderID INT,

    LogMessage VARCHAR(255)

);

 

-- 创建触发器

CREATE TRIGGER OrderAmountCheck

AFTER INSERT ON Orders

FOR EACH ROW

BEGIN

    IF NEW.OrderAmount > 10000 THEN

        INSERT INTO OrderLogs (OrderID, LogMessage)

        VALUES (NEW.OrderID, '订单金额超过 10,000');

    END IF;

END;

 

-- 测试插入数据

INSERT INTO Orders (OrderID, OrderAmount) VALUES (1, 15000);  -- 会自动生成日志记录

 

2.5 组合多种约束

可以通过组合多种约束来满足更复杂的用户自定义完整性需求。

例1：完整的员工表

    •    工资必须大于 0。

    •    年龄必须在 18 到 60 之间。

    •    每个部门的员工编号必须唯一。

 

CREATE TABLE Employees (

    EmpID INT PRIMARY KEY,

    EmpName VARCHAR(50) NOT NULL,

    Age INT CHECK (Age BETWEEN 18 AND 60),          -- 自定义约束：年龄限制

    Salary DECIMAL(10, 2) CHECK (Salary > 0),      -- 自定义约束：工资限制

    DeptID INT NOT NULL,

    UNIQUE (EmpID, DeptID)                         -- 自定义约束：部门内编号唯一

);

 

3. 用户自定义完整性的典型应用场景

    1.    电商系统：

    •    订单金额不能为负数。

    •    订单状态默认值为 “待付款”。

    2.    学生管理系统：

    •    学生年龄必须符合合法范围。

    •    学生成绩必须在 0 到 100 之间。

    3.    工资系统：

    •    工资不能为负。

    •    部门编号和员工编号的组合必须唯一。

 

4. 练习题目

练习1：检查学生成绩范围

创建一个学生成绩表，要求学生的成绩范围在 0 到 100 之间。

CREATE TABLE StudentGrades (

    StudentID INT PRIMARY KEY,

    StudentName VARCHAR(50),

    Grade DECIMAL(5, 2) CHECK (Grade BETWEEN 0 AND 100)

);

 

测试以下语句：

INSERT INTO StudentGrades VALUES (1, '张三', 95);   -- 应成功

INSERT INTO StudentGrades VALUES (2, '李四', 105); -- 应失败

 

练习2：动态检查员工年龄

创建一个员工表，并使用触发器动态检查插入的员工年龄。

CREATE TABLE Employees (

    EmpID INT PRIMARY KEY,

    EmpName VARCHAR(50),

    Age INT

);

 

-- 创建触发器

CREATE TRIGGER EmployeeAgeCheck

BEFORE INSERT ON Employees

FOR EACH ROW

BEGIN

    IF NEW.Age < 18 OR NEW.Age > 60 THEN

        SIGNAL SQLSTATE '45000'

        SET MESSAGE_TEXT = '年龄必须在 18 到 60 之间';

    END IF;

END;

 

-- 测试插入

INSERT INTO Employees (EmpID, EmpName, Age) VALUES (1, '赵强', 17);  -- 应失败

INSERT INTO Employees (EmpID, EmpName, Age) VALUES (2, '王丽', 25); -- 应成功

 

通过以上内容和练习，可以更好地理解用户自定义完整性的定义、实现方式及应用场景。