跳至主要内容

SQL 中的完整性约束：示例指南

SQL 中的完整性约束是对数据库表强制实施的规则，用于维护数据的准确性、一致性和有效性，例如确保主键唯一以及外键关系有效。
· 15分钟 阅读

使用 AI 探索

想象一个医院数据库，患者的过敏史字段不允许留空；或一个金融系统，交易金额必须为正数。在这些场景以及无数类似情形中，我们依赖完整性约束来确保数据保持准确、一致、可靠。

在 SQL 中，完整性约束是我们对数据库表施加的规则，用于维护数据质量。它们有助于防止错误、执行业务规则，并确保数据反映其所代表的真实世界实体与关系。

本文将深入讲解 SQL 中常见的完整性约束类型，并结合 PostgreSQL 数据库给出清晰解释与实用示例。尽管我们使用 PostgreSQL 语法，这些概念同样适用于其他 SQL 方言。

如果您想继续学习 SQL，请查看这份SQL 课程清单。

什么是 SQL 中的完整性约束？

设想一个为 Web 应用存储用户信息的表。某些数据（如用户年龄）可能是可选的，因为缺少它并不会妨碍用户使用应用。但每位用户必须有密码才能登录。为此，我们会在用户表的密码列上实施一个完整性约束，以确保该表中的每条记录都包含密码。

本质上，完整性约束对于以下方面至关重要：

  • 防止数据缺失。
  • 确保所有数据遵循预期的数据类型与取值范围。
  • 维护不同表之间的正确关联。

本文将探讨 SQL 中以下关键的完整性约束：

  • PRIMARY KEY：唯一标识表中的每条记录。
  • NOT NULL：确保某列不能包含 NULL 值。
  • UNIQUE：确保某列或列组中的所有值唯一。
  • DEFAULT：在未指定时为某列提供默认值。
  • CHECK：确保某列的所有值满足特定条件。
  • FOREIGN KEY：通过引用另一表的主键来建立表与表之间的关系。

案例研究：大学数据库

我们来看一个大学的关系型数据库。该数据库包含三个表：students、courses 和 enrollments。

Students 表

students 表保存所有在校学生的信息。

  • student_id：学生标识符
  • first_name：学生名。
  • last_name：学生姓。
  • email：学生的电子邮箱地址。
  • major：学生的专业。
  • enrollment_year：学生入学年份。

Courses 表

courses 表保存学校提供的课程信息。

  • course_id：课程标识符。
  • course_name：课程名称。
  • department：课程所属院系。

Enrollments 表

enrollments 表存储哪些学生选修了哪些课程的信息。

  • student_id：选修该课程的学生标识符。
  • course_id：课程标识符。
  • year：选课年度。
  • grade：学生在该课程中的成绩。
  • is_passing_grade：一个布尔值，指示该成绩是否及格。

在整篇文章中，我们将使用这个示例数据库，展示多种强制数据完整性的方法。我们的查询使用 PostgreSQL 语法，但这些概念很容易迁移到其他 SQL 变体中。

PRIMARY KEY 约束

学校希望能唯一地识别每位学生。使用名和姓并不可取，因为不同学生可能重名。同样依赖电子邮箱也不理想，因为学生可能更换邮箱。

常见的解决方案是为每位学生分配一个唯一标识，并将其存储在 student_id 列中。我们可以在 student_id 列上应用 PRIMARY KEY 约束，以保证每位学生都有唯一标识。

该约束在 CREATE TABLE 命令中定义，紧跟在列数据类型的说明之后：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    major TEXT,
    enrollment_year INT
);

上述查询创建了一个包含我们提到的六个列的学生表。

PRIMARY KEY 约束确保：

  1. 每位学生都有 student_id。
  2. 每个 student_id 都是唯一的。

多列 PRIMARY KEY 约束

在某些情况下，我们需要使用多列来唯一标识每一行。例如 enrollments 表。一个学生可以选修多门课程，导致多行共享同一个 student_id。同样一门课程可以有多个学生选修，导致多行具有相同的 course_id。

鉴于没有单一字段可以唯一标识一行，每条选课记录由 student_id、course_id 和 year 的组合来确定。

当涉及多列时，应在 CREATE TABLE 命令末尾指定 PRIMARY KEY 约束。

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT,
    grade INT,
    is_passing_grade BOOLEAN,
    PRIMARY KEY (student_id, course_id, year)
);

表创建后的完整性约束

添加完整性约束有两种方式。我们刚刚学习了如何在创建表时添加。

设想表已存在，但您忘了指定 PRIMARY KEY 约束。您也可以在表创建后使用 ALTER TABLE 命令进行定义，如下所示：

ALTER TABLE enrollments
ADD CONSTRAINT enroll_pk 
PRIMARY KEY (student_id, course_id, year);

在这条 ALTER TABLE 查询中，我们将该约束命名为 enroll_pk（意为 enrollment primary key）。这个名称可以是您选择的任意标识符，但建议选择能简洁传达约束用途的名称。

为完整性约束命名是一种最佳实践，原因包括：

  • 便于引用，尤其当您需要在将来修改或删除该约束时。
  • 有助于管理与组织约束，特别是在存在大量约束的数据库中。

NOT NULL 约束

学校希望确保每位学生的姓名和邮箱都被记录在数据库中。他们不希望工作人员不小心漏填这些字段。

为此，我们可以在创建表时为这三列添加 NOT NULL 约束：

CREATE TABLE students (
    student_id INT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    major TEXT,
    enrollment_year INT
);

上述查询通过 NOT NULL 约束确保 first_name、last_name 和 email 三列不能为 NULL（未定义）。

在已有表上添加 NOT NULL 约束可使用 ALTER TABLE 命令。其语法如下：

ALTER TABLE students
ALTER COLUMN first_name SET NOT NULL,
ALTER COLUMN last_name SET NOT NULL,
ALTER COLUMN email SET NOT NULL;

UNIQUE 约束

邮箱地址对每个人来说天然是唯一的。在我们确信某个字段不应出现重复值的情况下，最好在数据库层面强制执行。这有助于防止错误并确保数据完整性。

添加该约束的方式与其他约束相同。

CREATE TABLE students (
    ...
    email TEXT UNIQUE,
    ...
);

不过，在我们的例子中，我们还希望同时强制 NOT NULL 约束。我们可以在同一列上通过空格分隔的方式同时声明多个约束：

CREATE TABLE students (
    ...
    email TEXT UNIQUE NOT NULL,
    ...
);

请注意顺序无关紧要，我们也可以写成 NOT NULL UNIQUE。

在已有表上添加 UNIQUE 约束的方式如下：

ALTER TABLE students 
ADD CONSTRAINT unique_emails UNIQUE (email);

多列 UNIQUE 约束

假设我们希望确保在 courses 表中，每个院系下的课程名称唯一。在这种情况下，course_name 与 department 两列的组合应当唯一。

当涉及多列时，应在 CREATE TABLE 命令末尾添加该约束：

CREATE TABLE courses (
    course_id INT,
    course_name TEXT,
    department TEXT,
    UNIQUE (course_name, department)
);

或者，我们也可以通过修改已有表来添加该约束。在这种情况下，我们提供一个包含列名的元组：

ALTER TABLE courses 
ADD CONSTRAINT unique_course_name_department 
UNIQUE (course_name, department);

NOT NULL UNIQUE 与 PRIMARY KEY 约束

我们已经知道，PRIMARY KEY 约束同时强制唯一性和非空。那么您可能会好奇，以下两者有何区别：

course_id INT PRIMARY KEY
course_id INT UNIQUE NOT NULL

NOT NULL UNIQUE 与 PRIMARY KEY 在表中的区别在于其设计目的与用法。

二者都对列（或列组）强制唯一性和非空，但一个表只能有一个 PRIMARY KEY，其用于唯一标识表中的每条记录。

另一方面，NOT NULL UNIQUE 的组合可以应用于其他列，以在不允许空值的前提下强制每行具有唯一取值，从而满足特定业务规则来维护数据完整性。一个表可以有任意数量的 NOT NULL UNIQUE 约束。

两者并存提供了更灵活的数据库设计方式，使我们能够以多种方式强制实施唯一性和数据完整性，同时区分记录的主标识符与表中其他重要且唯一的属性。

DEFAULT 约束

学生在注册入学后可能需要一段时间来选择专业。学校希望对于尚未选择专业的学生，major 列的值为字符串 “Undeclared”。

为此，我们可以使用 DEFAULT 约束为该列设置默认值。我们可以如下修改 students 表：

ALTER TABLE students
ALTER COLUMN major SET DEFAULT 'Undeclared';

如果我们希望在创建表时就设置 DEFAULT 约束，可以在列数据类型之后声明：

CREATE TABLE students (
    ...
    major TEXT DEFAULT 'Undeclared',
    ...
);

CHECK 约束

在这所大学，成绩范围为 0 到 100。如果没有任何约束，enrollment 表中的 grade 列可以接受任意整数。我们可以通过 CHECK 约束将取值限制在 0 到 100 之间。

ALTER TABLE enrollments
ADD CONSTRAINT grade_range CHECK (grade BETWEEN 0 AND 100);

一般而言，CHECK 约束使我们能够验证数据需满足的特定条件。这对于确保数据一致性与完整性非常重要。

CHECK 约束可以涉及多个列。我们用它来确保 grade 与 is_passing_grade 的取值一致。假设成绩至少为 60 才算及格。那么我们可以确保仅当成绩至少为 60 时，is_passing_grade 为 TRUE。下面在创建表时展示如何在 CREATE TABLE 命令中声明 CHECK 约束：

CREATE TABLE enrollments (
    ...
    grade INT,
    is_passing_grade BOOLEAN,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    )
);

上述约束存在一个问题。学生在选课时往往还没有成绩。因此我们应允许 grade 为 NULL。在这样做的同时，还需要更新及格约束：当成绩未定义时，及格标记也应为 NULL。我们可以这样更新约束以纳入这些情况：

CREATE TABLE enrollments (
    ...    
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    ...
);

请注意，在 grade 和 is_passing_grade 两列上，我们在数据类型处添加了 NULL 并设置了默认值为 NULL。这样做的目的只是为了提升可读性。

接下来看看我们可以用 CHECK 约束强制哪些条件。

范围条件

我们可以确保某列的取值落在特定范围内。

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name BETWEEN min_value AND max_value);

列表条件

我们可以验证某列的取值是否匹配特定值列表中的某一个。

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name IN ('Value1', 'Value2', 'Value3'));

比较条件

我们可以比较列值以确保其满足指定条件（大于、小于等）。

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name > some_value);

模式匹配条件

我们可以使用模式匹配（例如使用 LIKE 或 SIMILAR TO）来验证文本数据。

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name LIKE 'pattern');

逻辑条件

我们可以使用逻辑运算符（AND、OR、NOT）组合多个条件。

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (condition1 AND condition2 OR condition3);

复合条件

对多列施加一个条件。

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column1 + column2 < some_value);

FOREIGN KEY 约束

外键约束用于将两个表的列关联起来，确保数据的参照完整性。简而言之，一个表中的外键指向另一个表中的主键，表明这两个表的行彼此相关。这确保了您不能在含有外键的表中插入一行，而该外键在被引用的主键表中不存在对应行。

在我们的示例中，enrollments 表中的每条记录通过 student_id 和 course_id 分别指向一个学生和一门课程。如果没有任何约束，就无法确保选课表中的这些标识符值与 students 和 courses 表中已有的记录相匹配。

下面是在创建 enrollments 表时确保这一点的方法：

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    ...
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

请注意，要使它们成为外键，students 与 courses 表中的对应列必须是主键。

与其他约束一样，我们也可以在创建表之后，通过 ALTER TABLE 命令声明这些约束：

ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(student_id);

ALTER TABLE enrollments
ADD CONSTRAINT fk_course_id
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

综合示例

在本文中，我们展示了多种完整性约束，以及如何利用它们改进大学数据库。以下是整合我们所学内容后的 CREATE TABLE 最终版本。

首先，定义 students 表：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    major TEXT DEFAULT 'Undeclared',
    enrollment_year INT,
    CONSTRAINT year_check CHECK (enrollment_year >= 1900),
    CHECK (major IN (
        'Undeclared',
        'Computer Science',
        'Mathematics',
        'Biology',
        'Physics',
        'Chemistry',
        'Biochemistry'
    ))
);

接下来，定义 courses 表：

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name TEXT NOT NULL,
    department TEXT NOT NULL,
    UNIQUE (course_name, department),
    CHECK (department IN (
        'Physics & Mathematics',
        'Sciences'
    ))
);

最后，定义 enrollments 表，建立学生与课程之间的关系：

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT CHECK (year >= 1900),
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id, year)
);

我们在这个最终示例中添加了几个约束。大学的院系与专业是已知的一组集合。因此，major 与 department 列的取值是有限且可预知的。在这种情况下，建议使用 CHECK 约束，确保这些列只能取该已知集合中的值。

结语

本文探讨了 SQL 中不同类型的完整性约束以及如何在 PostgreSQL 中实现它们。我们涵盖了主键、NOT NULL 约束、UNIQUE 约束、DEFAULT 约束、CHECK 约束和 FOREIGN KEY 约束，并为每种约束提供了实际示例。

通过理解这些概念，我们可以确保数据的准确性、一致性与可靠性。

如果您想进一步学习如何高效组织数据，请查看这门数据库设计课程。

François Aubry

作者

François Aubry

LinkedIn
全栈工程师，CheapGPT 创始人。我一直热爱教学。从学生时代起，我就积极寻找机会为同学提供辅导与帮助。这份热情促使我攻读博士学位，并在此期间担任助教，以支持我的学术追求。那些年里，我在传统课堂环境中收获了极大的成就感，既建立联系也促进学习。随着在线学习平台的兴起，我也看到了数字教育的变革潜力。事实上，我曾参与我们大学一款此类平台的建设。我致力于将传统教学理念与创新的数字方法相融合。我的热情在于打造既有吸引力、信息量充足，又能让当代学习者轻松获取的课程。

主题

SQL数据科学