PostgreSQL – 数组定义的表列上的检查约束

在本文中，我们将介绍如何在 PostgreSQL 数据库中为数组类型的表列定义检查约束。

什么是数组？

数组是一种可以存储多个值的数据结构。在 PostgreSQL 中，数组是一种复合类型，可以存储同一类型的多个值。我们可以将数组作为单个列的值存储在表中。

数组的检查约束

在 PostgreSQL 中，我们可以在表的列上定义检查约束，以限制该列接受的值的范围。检查约束允许我们对列中的值进行条件验证。当对数组列使用检查约束时，我们可以在约束表达式中使用数组函数来处理数组的元素。

创建表并定义数组列

让我们首先创建一个名为 “employees” 的表，该表包含一个名为 “languages” 的数组列，该列用于存储每个雇员所掌握的语言。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    languages VARCHAR(20)[]
);

在上面的示例中，我们使用了 VARCHAR 类型和 [] 运算符定义了一个 VARCHAR 数组列 “languages”。

使用检查约束验证数组的值

假设我们要确保 “languages” 数组列中的值仅限于 “英语”、”法语” 和 “西班牙语” 这些选项。为了实现这个约束，我们可以在创建表的同时定义一个检查约束。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    languages VARCHAR(20)[] CHECK(languages <@ ARRAY['英语', '法语', '西班牙语'])
);

在上面的例子中，我们使用 <@ 操作符来验证 “languages” 数组列的值是否包含在数组 [‘英语’, ‘法语’, ‘西班牙语’] 中。若值不在数组中，则检查约束会阻止插入或更新操作。

示例

让我们插入一些数据并测试检查约束。

INSERT INTO employees (name, languages)
VALUES ('张三', ARRAY['英语', '法语', '西班牙语']);

INSERT INTO employees (name, languages)
VALUES ('李四', ARRAY['英语', '德语']);

-- 以下插入将违反检查约束
INSERT INTO employees (name, languages)
VALUES ('王五', ARRAY['荷兰语', '日语']);

在上面的示例中，我们首先插入了一个 “languages” 列包含合法值的记录（英语、法语和西班牙语）。然后，我们插入了另一个记录，其中 “languages” 列包含一个非法值（德语）。最后，我们尝试插入另一个记录，其中 “languages” 列包含两个非法值（荷兰语和日语）。由于检查约束的存在，最后一条插入将失败并产生错误。