[譯]PostgreSQL 新手學習之約束

原創 tracy 云和恩墨交付戰隊 2022-02-26

2886

原文作者：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
;