[译]PostgreSQL 新手学习之约束

原文作者：ELIZABETH CHRISTENSEN
翻译：Tracy
原文链接：https://blog.crunchydata.com/blog/postgres-constraints-for-newbies

让Postgres在软件开发中如此出色的一个原因是它具有非常有用约束功能。约束在postgres中是一种限制哪些数据可以插入表、列或行中的方法。

作为应用程序开发人员，你也可以在你的应用程序中构建这个约束逻辑，这很好。然而，将此约束逻辑添加到数据库中，可以长期保护数据不受坏数据、空语句或工作不太正常且不符合数据需求的应用程序代码的影响。

在介绍主要的约束类型前，我将向您展示一个数据库模式示例，您正在构建一个房间预订系统，其中包含一个用户表、一个房间表，以及引用用户表和房间表并且包含一个开始和结束时间的预订表。

我们可以先创建没有任何约束的用户表和房间表：

CREATE TABLE users (
    id serial PRIMARY KEY,
    name text,
    email text
);

CREATE TABLE rooms (
    id serial PRIMARY KEY,
    number text 
);

外键约束

接下来创建需要引用前两张表中的主键的第三张表，这里我们会使用到外键约束。外键约束语法：references table(column)。

CREATE TABLE reservations (
    user_id int references users(id),
    room_id int references rooms(id),
    Start_time timestamp,
    end_time timestamp,
    event_title text
 );

在此处添加外键约束是一种可以将我们的预订表与其他数据表的主键始终绑定在一起的非常简单的方法。
您还可以通过下面的ALTER TABLE方式在已创建的表上增加外键约束：

ALTER TABLE public.reservations
    ADD CONSTRAINT reservations_room_id_fkey FOREIGN KEY (room_id) REFERENCES public.rooms(id);
ALTER TABLE public.reservations
    ADD CONSTRAINT reservations_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);

可以给外键约束指定名称，例如reservations_user_id_fkey。如果不提供名字，Postgres会为外键约束自动命名。

唯一约束

唯一约束是要求数据在某一列或行中是唯一的。这在创建用户名、唯一标识或任何主键时特别有用。例如，我们希望对房间号设置唯一的约束，这样您就不会意外地得到重复的房间号：

ALTER TABLE ONLY public.rooms
    ADD CONSTRAINT room_number_unique UNIQUE (number);

可以给唯一约束命名，如：room_number_unique。如果不提供名字，Postgres 会为自动生成约束名称。

级联和外键

使用外键约束时，我还应该提到级联更新和删除语句。外键可以可以通过ON DELETE和ON UPDATE修饰符来定义对主表数据进行更改时的影响。如果出于GDPR或其他隐私需求需要删除用户数据，级联删除尤其重要。例如，假设在我们的模式中，我们想要在一定时间后删除用户，并且我们也想要擦除他们的预订历史。如果设置为级联删除，当用户表中的行被删除是会删除字表中的相关行。

ALTER TABLE public.reservations
    ADD CONSTRAINT reservations_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;

如果您不提供ON DELETE CASCADE，Postgres 将阻止您删除 users 表中的记录，除非首先删除该用户的所有预订记录。

注意：级联语句必须在添加外键约束时添加。事后无法通过ALTER TABLE修改.

非空约束

在查看数据时，您会希望某些地方不要出现空值。添加非空约束是一种很好的方法，可以确保不会添加不完整的数据行。

下面的一个例子是确保所有的预定都会有一个房间号。

ALTER TABLE public.reservations ALTER COLUMN room_id SET NOT NULL;

下面是确保所有预订都有开始和结束时间：

ALTER TABLE public.reservations ALTER COLUMN start_time SET NOT NULL;
ALTER TABLE public.reservations ALTER COLUMN end_time SET NOT NULL;

查询约束列表时，非空约束不会被命名，也不会出现在pg_constraints系统表中。

检查约束

检查约束是在向表中插入数据之前让数据库对数据进行检验的一些简单的逻辑。检查约束应用于表中的单行。例如，在我们的模式中，我们需要为预订时间添加一些逻辑。开始时间应小于结束时间。开始时间应大于上午 8 点且小于下午 5 点。并且开始时间和结束时间的间隔要大于30分钟。

检查约束的语法：
开始时间小于结束时间

ALTER TABLE public.reservations ADD CONSTRAINT start_before_end check (start_time < end_time )

开始时间必须大于上午 8 点，结束时间必须小于下午 5 点。

ALTER TABLE public.reservations ADD CONSTRAINT daytime_check check (start_time::time >= '08:00:00' AND end_time::time <= '17:00:00')

开始时间和结束时间的间隔要大于 30 分钟。

ALTER TABLE public.reservations ADD CONSTRAINT interval_check check (end_time - start_time >= interval '30 minutes')

排除约束

检查约束非常适合比较单个记录的各个字段并确保这些字段有效。如果您想根据同一张表中的其他行检查一行的值，您需要使用一种更复杂的方法，称为排除约束。排除约束用于定义返回真/假结果的表达式，并且仅在收到错误响应时插入数据。正确的响应将意味着该数据已经存在，因此您无法插入。错误响应将意味着数据尚不存在，您可以插入。约束排除的常见用途是为只能拥有一个角色的用户添加角色，或为已经预订了该时间的用户添加日历预订。

因此，在我深入讨论排除约束之前，您需要考虑使用空间数据。为什么呢?排除约束通常适用于这样的想法，即我们有一个矩形，这个矩形要么被填满，要么没有。矩形的大小是基于数据点的，比如时间范围。我知道，现在进入空间数据听起来有点疯狂，但这只是Postgres的技巧之一，已经成为了常见的实践。您经常会看到使用GIST索引的排除约束。GIST索引将帮助Postgres查询该矩形是否被填满。我们不必单独创建底层索引，创建约束将为我们完成这一工作。

排除约束可能具有：

• EXCLUDE声明
• GIST 声明
• 用点定义的矩形
• 使用&&运算符，来判断一个矩形是否与另一个矩形相交
• 在我们这里的例子中，矩形之间有一些空间，所以它们不会完全重叠。在我的示例中，我在这里为添加了一个 0.5 的减数。任何大于 0 且小于 1 的数字都可以使用。

下面是为预留时间创建排除约束的语法，这样就不会插入与现有预留重叠的预留。

ALTER TABLE public.reservations ADD CONSTRAINT reservation_overlap
EXCLUDE USING GIST (
        box (
            point(
                extract(epoch from start_time),
                room_id
            ),
            point(
                extract(epoch from end_time) - 0.5,
                room_id + 0.5
            )
        )
        WITH &&
    );

在数据库中查看约束

如果您需要查找数据库中已存在的约束，下面这个查询语句会展示到目前为止本文中已经讨论过的所有类型的约束：

SELECT * FROM (
    SELECT
       c.connamespace::regnamespace::text as table_schema,
       c.conrelid::regclass::text as table_name,
       con.column_name,
       c.conname as constraint_name,
       pg_get_constraintdef(c.oid)
    FROM
        pg_constraint c
    JOIN
        pg_namespace ON pg_namespace.oid = c.connamespace
    JOIN
        pg_class ON c.conrelid = pg_class.oid
    LEFT JOIN
        information_schema.constraint_column_usage con ON
        c.conname = con.constraint_name AND pg_namespace.nspname = con.constraint_schema   
    UNION ALL
    SELECT
        table_schema, table_name, column_name, NULL, 'NOT NULL'
    FROM information_schema.columns
    WHERE
        is_nullable = 'NO'
) all_constraints
WHERE
    table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name, column_name, constraint_name
;

创建约束的总结

• 在设计模式时，请花些时间考虑是否需要使用Postgres的约束功能。
• 您几乎总是需要可以与主键绑定在一起的外键约束。
• 在创建外键的时候，请一并设置好级联删除规则。
• 如果您遇到了错误的数据，可以考虑添加约束以避免再次产生错误数据。