PostgreSQL 教程: 使用 DOMAIN 规则验证列值

一月 9, 2025

摘要：在本教程中，您将学习如何在数据进入数据库时对其进行校验，以提高数据质量。

目录

介绍

在处理数据输入时，总有一个或多个字段需要严格验证，比如：T 恤的尺码只能接受几个精确的值，鞋码应该在一段数字范围内，电话号码应该遵循精确的模式。最好在应用程序级别来执行这些检查，例如作为表单输入的一部分，但为了数据一致性，最好在将数据添加到数据库时也进行验证。

PostgreSQL 允许以多种方式执行这些检查，例如使用表约束。在本文中，我们将会讨论 DOMAIN 特性，该特性可以集中定义列的校验，以便在多个数据库表中使用。我们将展示 DOMAIN 方法的强大功能和局限性，并在一个表中，使用一些相当基本的示例，来验证意大利地址。

集中式处理对比按表处理

如前所述，DOMAIN 方法允许一次定义一个列类型的约束，并在多个表中重用它。一个独有的定义的好处是显而易见的：只有一个定义逻辑，和一种使用方式。需要在多个表中定义相同的约束，可能会导致约束演进过程中出现人为错误或失准。

另一方面，当不同的表需要以不同的方式或跨不同时间发展此类定义时，要拥有唯一的定义可能会很棘手。在这种情况下，采用唯一的一种使用方式可能会变得危险。有关更多信息，请查看下面有关更改和删除 DOMAIN 定义的示例。

定义允许的意大利街道值

出于演示的目的，假设意大利街道以三个标志开始：Via、Viale 和 Piazza。实际上，在意大利有更多方法可以表示街道，但现在让我们保持简单。

让我们使用 DOMAIN 特性，来为这三个值创建一个域类型italian_address。

CREATE DOMAIN italian_address
    text
    NOT NULL
    CONSTRAINT italian_address_check CHECK(upper(VALUE) ~ '^(VIA|VIALE|PIAZZA) [A-Z \-]+ [0-9]+[A-Z]*$');

如果我们查看上面命令的详细信息，我们可以看到：

  • 名称为italian_address

  • 数据类型为text

  • 有一个NOT NULL约束，强制列永远不会为空

  • 有一个名为italian_address_check的CONSTRAINT。它会根据一个正则表达式，执行一个检查，以确保地址：

    • 以VIA、VIALE或PIAZZA之一开头，来标识街道/广场的类型
    • 后跟一个空格和一串由A-Z、 空格或-的字符组成的字符串，用于标识街道/广场的名称
    • 后跟一个空格、一个或多个数字（+），用于标识建筑物的编号
    • 后跟零个或多个字母（*），用于标识建筑物内的街区

在表定义中使用域类型

现在我们已经定义了域类型，我们可以在创建表时使用它。让我们创建表addresses，来托管我们的客户信息，例如客户的姓名和地址：

CREATE TABLE addresses(
    id SERIAL,
    name TEXT,
    address italian_address
);

在上面的 SQL 中，address列引用了域类型italian_address。现在我们可以插入行，检查是否遵守域规则：

INSERT INTO addresses(name, address) VALUES ('UGA', 'PIAZZA DEL CORSO 8A');

上面的插入是成功的，因为它遵循域类型的定义。但是，如果我们尝试插入其他内容，例如以STRADA开头的地址呢？

INSERT INTO addresses(name, address) VALUES ('UGO', 'STRADA NASCOSTA 5');

我们收到了一个错误，因为域的检查失败了：

ERROR:  value for domain italian_address violates check constraint "italian_address_check"

我们的域类型符合预期生效了，我们现在也可以在其他表定义中使用它。例如，如果我们有一个shops表，需要包含已验证的地址，我们可以将其定义为shops italian_address。这就是 DOMAIN 特性的美妙之处：一个集中的定义，可以多处使用。

设置默认值

域类型的定义禁止列中出现空值，但我们可能希望通过设置域的默认值，来允许跳过列定义的插入：

ALTER DOMAIN italian_address SET DEFAULT 'UNKNOWN ADDRESS';

现在，如果我们尝试添加没有任何关联地址的新客户，我们可能会期望插入成功：

INSERT INTO addresses(name) VALUES ('LUIGI');

相反，我们得到了一个错误：

ERROR:  value for domain italian_address violates check constraint "italian_address_check"

该错误是由于我们的默认值UNKNOWN ADDRESS不符合域中定义的约束（它不以VIA、VIALE或PIAZZA开头）。如果我们将默认值切换为兼容的值，例如VIA UNKNOWN ADDRESS 1：

ALTER DOMAIN italian_address SET DEFAULT 'VIA UNKNOWN ADDRESS 1';

我们现在可以在没有地址的情况下，成功输入客户信息了：

INSERT INTO addresses(name) VALUES ('LUIGI');

使用下面命令，检查addresses表中的数据：

SELECT * FROM addresses;

将会显示出设置了默认地址的LUIGI行。

 id | name  |        address
----+-------+-----------------------
  1 | UGA   | PIAZZA DEL CORSO 8A
  4 | LUIGI | VIA UNKNOWN ADDRESS 1
(2 rows)

请注意，默认值有一个层次结构：数据类型默认值 -> 域默认值 -> 表列默认值。

数据类型默认值可被域默认值覆盖，域默认值可被表列默认值覆盖。

更改域时要注意

要更改已在使用的域，可能会很棘手。例如，假设明天意大利决定将所有PIAZZA地址重命名为PIZZA，因此我们需要更新我们的域，以反映这一点。首先，我们删除旧的约束：

ALTER DOMAIN italian_address
    DROP CONSTRAINT italian_address_check;

然后添加新的检查，更改PIAZZA为PIZZA：

ALTER DOMAIN italian_address
    ADD CONSTRAINT italian_address_new_check CHECK(UPPER(VALUE) ~ '^(VIA|VIALE|PIZZA) [A-Z \-]+ [0-9]+[A-Z]*$');

但是上面的命令会产生一个错误：

ERROR:  column "address" of table "addresses" contains values that violate the new constraint

为什么我们不能启用新的约束？让我们来看一下不满足新约束的数据，执行：

SELECT *
from addresses
where NOT address ~ '^(VIA|VIALE|PIZZA) [A-Z \-]+ [0-9]+[A-Z]*$';

该查询中的WHERE子句，会根据域中定义的相同正则表达式检查地址。输出为：

 id | name |       address
----+------+---------------------
  1 | UGA  | PIAZZA DEL CORSO 8A
(1 row)

我们可以看到PIAZZA DEL CORSO 8A的行不满足我们的新约束（它应该以PIZZA开头）。现在，我们先将约束定义为NOT VALID：

ALTER DOMAIN italian_address
    ADD CONSTRAINT italian_address_new_check CHECK(UPPER(VALUE) ~ '^(VIA|VIALE|PIZZA) [A-Z \-]+ [0-9]+[A-Z]*$')  NOT VALID;

该NOT VALID子句允许我们添加约束时，不去验证表中的行。如果我们检查有效性：

ALTER DOMAIN italian_address
    VALIDATE CONSTRAINT italian_address_new_check;

由于我们没有更新数据，我们仍然收到了相同的错误：

ERROR:  column "address" of table "addresses" contains values that violate the new constraint

我们需要更新表中的数据：

UPDATE addresses SET address = replace(address, 'PIAZZA', 'PIZZA') WHERE address LIKE 'PIAZZA%';

然后执行SELECT * FROM ADDRESSES检查数据：

 id | name  |        address
----+-------+-----------------------
  1 | UGA   | PIZZA DEL CORSO 8A
  4 | LUIGI | VIA UNKNOWN ADDRESS 1
(2 rows)

该UGA行现在应该与新约束一致了，因此我们可以对其进行验证：

ALTER DOMAIN italian_address
    VALIDATE CONSTRAINT italian_address_new_check;

成功了！现在，该检查已启用，因此会禁止尝试插入PIAZZA地址。

INSERT INTO addresses (name, address) VALUES ('CARLA', 'PIAZZA MAGGIORE 33');

上面的 SQL 抛出了错误：

ERROR:  value for domain italian_street violates check constraint "italian_street_new_check"

除了这个例子之外，ALTER DOMAIN命令的另一个问题是，我们在域的创建过程中添加的任何表达式或函数，都应该是不可变的。如果我们使用了一个用户定义的函数作为CREATE DOMAIN表达式的一部分，并且我们能够在创建域之后修改表达式/函数，那么在重新加载该数据库中的数据时，我们会收到错误。

删除域时要注意

当一个域被一个或多个表使用的时候，删除该域会发生什么情况？

DROP DOMAIN italian_address;

我们收到了下面的错误，表明存在依赖关系

ERROR:  cannot drop type italian_address because other objects depend on it
DETAIL:  column address of table addresses depends on type italian_address
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

如果我们真的想要删除它，我们可以使用CASCADE选项：

DROP DOMAIN italian_address CASCADE;

检查表：

SELECT * FROM addresses;

不再有address列了！

 id | name
----+-------
  1 | UGA
  4 | LUIGI
(2 rows)

很难在不影响其余数据库表的情况下，去删除正在使用的域。在这种情况下，集中管理的列定义的方法，会暴露出它的短板。由于定义是唯一的，因此如果相关定义被删除，任何使用它的表的列都将消失。

结论

PostgreSQL 的域类型是一项强大的特性，它允许集中定义列的验证规则，然后可以跨多个表应用这些规则。定义的演进可能会很棘手，但对于标准的、不会演变的列来说，它是一种集中式定义和检查的好方法。

搜索

分类

标签