【数据库】用户自定义完整性-属性和元组操作

5.4 用户定义的完整性

用户定义的完整性是数据库完整性的重要组成部分，是用户根据具体业务需求对数据定义的约束规则。这些约束补充了系统自带的实体完整性和参照完整性，通过限制属性值和元组之间的逻辑关系，确保数据的合法性和一致性。

 

5.4.1 属性上的约束

属性上的约束是对表中单个字段（列）定义的规则，用于限制其值的范围、格式或默认值。

这些约束适用于字段的基本属性，例如不能为空、必须唯一、符合某种条件等。

 

常见的属性约束

约束类型    作用    SQL 关键字

非空约束    确保字段值不能为 NULL。    NOT NULL

唯一约束    确保字段值唯一，不允许重复。    UNIQUE

默认值约束    为字段设置一个默认值，当插入数据时若未提供值，则使用默认值。    DEFAULT

检查约束    对字段值添加逻辑条件限制。    CHECK

主键约束    确保字段唯一且非空，是表中每条记录的唯一标识。    PRIMARY KEY

外键约束    确保字段值必须在另一个表的指定字段中存在（参照完整性的一部分）。    FOREIGN KEY

 

属性约束的应用示例

    1.    定义学生表，确保学号不能为空且唯一，年龄在18-30岁之间：

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,                 -- 主键约束

    StudentName VARCHAR(50) NOT NULL,          -- 非空约束

    Age INT CHECK (Age BETWEEN 18 AND 30)      -- 检查约束

);

 

    2.    定义员工表，工资默认值为 5000，且必须为正数：

CREATE TABLE Employees (

    EmpID INT PRIMARY KEY,                     -- 主键约束

    EmpName VARCHAR(50) NOT NULL,              -- 非空约束

    Salary DECIMAL(10, 2) DEFAULT 5000 CHECK (Salary > 0)  -- 默认值和检查约束

);

 

    3.    限制电子邮件字段值唯一：

CREATE TABLE Users (

    UserID INT PRIMARY KEY,

    UserName VARCHAR(50),

    Email VARCHAR(100) UNIQUE                  -- 唯一约束

);

 

    4.    限制属性必须在一个特定集合中：

CREATE TABLE Products (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(50),

    Category VARCHAR(20) CHECK (Category IN ('Electronics', 'Clothing', 'Food')) -- 检查约束

);

 

5.4.2 元组上的约束

元组上的约束是针对一条记录（行）中多个字段之间关系的逻辑限制。

它用于确保多个字段之间的值具有一致性或满足特定业务规则。

 

元组约束的特性

    •    涉及多列组合。

    •    通过逻辑表达式，定义字段之间的关系。

    •    常见方式是通过 CHECK 和 UNIQUE 定义。

 

元组约束的应用场景

场景    约束类型    示例

限制多字段组合唯一性    UNIQUE    确保部门编号和员工编号的组合唯一。

定义字段间的逻辑关系    CHECK    确保售价必须大于成本价；总分不能超过一定值。

复合主键    PRIMARY KEY    确保订单表中的订单编号和产品编号的组合唯一，作为主键。

 

元组约束的应用示例

    1.    限制部门编号和员工编号的组合唯一：

CREATE TABLE Employees (

    EmpID INT,

    DeptID INT,

    EmpName VARCHAR(50),

    UNIQUE (EmpID, DeptID)                      -- 多列组合唯一

);

 

    2.    限制产品的售价必须高于成本价：

CREATE TABLE Products (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(50),

    Cost DECIMAL(10, 2) NOT NULL,

    Price DECIMAL(10, 2) NOT NULL,

    CHECK (Price > Cost)                        -- 跨字段检查

);

 

    3.    限制学生的总分不超过200分：

CREATE TABLE StudentScores (

    StudentID INT PRIMARY KEY,

    StudentName VARCHAR(50),

    MathScore INT,

    EnglishScore INT,

    CHECK (MathScore >= 0 AND EnglishScore >= 0 AND MathScore + EnglishScore <= 200) -- 跨字段检查

);

 

    4.    复合主键的使用：

CREATE TABLE Orders (

    OrderID INT,

    ProductID INT,

    Quantity INT,

    PRIMARY KEY (OrderID, ProductID)           -- 复合主键

);

 

属性约束与元组约束的对比

特点    属性上的约束    元组上的约束

作用范围    单个字段    多个字段（跨字段）

实现方式    NOT NULL, DEFAULT, CHECK, UNIQUE    CHECK, UNIQUE, PRIMARY KEY

逻辑复杂度    逻辑简单，约束单一字段值    逻辑复杂，需要定义字段间的关系

典型场景    确保单字段值范围、默认值或唯一性    确保多字段组合关系或字段间的一致性

 

练习题

练习1：为学生表定义属性约束

创建一个表，要求学生的年龄在18到25之间，成绩不能为负，且学生编号唯一。

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    StudentName VARCHAR(50) NOT NULL,

    Age INT CHECK (Age BETWEEN 18 AND 25),

    Score INT CHECK (Score >= 0)

);

测试：

INSERT INTO Students VALUES (1, '张三', 20, 85);  -- 应成功

INSERT INTO Students VALUES (2, '李四', 26, 90);  -- 应失败

 

练习2：为订单表定义元组约束

创建一个订单表，要求订单数量必须大于0，且单价乘以数量不得超过1000。

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    ProductID INT,

    Quantity INT CHECK (Quantity > 0),

    Price DECIMAL(10, 2),

    CHECK (Price * Quantity <= 1000)           -- 跨字段检查

);

测试：

INSERT INTO Orders VALUES (1, 101, 2, 500.00);  -- 应成功

INSERT INTO Orders VALUES (2, 102, 5, 250.00);  -- 应失败

 

总结

    •    属性上的约束保证单个字段的有效性，常用于基本规则检查。

    •    元组上的约束确保记录内多字段值的逻辑关系正确，适用于复杂场景。

    •    用户定义的完整性是保证数据库质量和业务逻辑一致性的核心，通过 SQL 提供的多种约束语法，用户可以高效实现数据的精确控制。