昨天我們介紹了SQL 的約束(Constraint)中的

1. Primary Key
2. Unique
3. Not Null
4. Default

約束確保資料表中的資料的準確性和可靠性。

今天我們將深入探討 FOREIGN KEY、CHECK、EXCLUSION 約束以及如何自定義約束的名稱

4. FOREIGN KEY 約束

Foreign Key: Foreign Key(外部鍵) 指的是某個欄位或某一群欄位的內容，必須在另一個資料表相對欄位之中，存在相同內容的資料。我們會說這樣的行為是在維護兩個表格之間的關連性。

範例：

-- 先建立一個 products 資料表

CREATE TABLE products (
    id integer PRIMARY KEY,
    name text,
    price numeric
);

讓我們假設我們需要一個資料表用來儲存這些產品的訂單，我們要確保這些訂單內的產品確實存在

所以我們定義一個外部鍵(Foreign Key) 來關聯訂單的表格和產品的表格：

-- 建立一個 orders 資料表，並且和 products 資料表做關聯

CREATE TABLE orders (
    id integer PRIMARY KEY,
    product_id integer REFERENCES products (id),
    quantity integer
);

-- 這樣的話，如果 product_id 沒有出現在產品資料表的 id 欄位的話，就無法建立資料了。

-- 有就是 product 一定要存在 products 資料表中，訂單才能被建立 ！！

我們寫入兩筆 product 資料

INSERT INTO products (id, name, price) VALUES (1, '商品 1 號', '1000'), (2, '商品 2 號', '2000') RETURNING *;

INSERT 0 2

 id |   name    | price
----+-----------+-------
  1 | 商品 1 號 |  1000
  2 | 商品 2 號 |  2000
(2 rows)

在來寫入 order ，但是我們將 product_id 帶入不存在的商品 id

INSERT INTO orders (id, product_id, quantity) VALUES (1, 3, 99);

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_product_id_fkey"
DETAIL:  Key (product_id)=(3) is not present in table "products".

我們看到了會直接去產生錯誤，我們來詳細的看看發生什麼錯誤

 當我們寫入或是更新 orders 資料表時，我們違反了外部鍵的約束(constraint)
 product_id = 3 商品，並不存在於 products 資料表 
 (也就是 products 資料表中沒有 id = ３ 的資料)

你也可以用多個欄位組成外部鍵

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,

FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

5. CHECK 約束

Check: 用於限制欄位中的值的範圍，它可以允許你指定某個欄位必須符合某個布林條件式的判斷

像是下面的例子，我們確保 salary 的值必須 > 0

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    salary DECIMAL CHECK (salary > 0)
);

嘗試寫入 salary = 0 的話會報錯，而錯誤在於 employees_salary_check 沒有過

INSERT INTO employees (emp_id, salary) VALUES (1, 0);

ERROR:  new row for relation "employees" violates check constraint "employees_salary_check"
DETAIL:  Failing row contains (1, 0).

重新嘗試寫入 salary = 1000，就會成功了

INSERT INTO employees (emp_id, salary) VALUES (1, 1000) RETURNING *;
 emp_id | salary
--------+--------
      1 |   1000
(1 row)

INSERT 0 1

7. EXCLUSION 約束

EXCLUSION: 用於保證資料表中的所有行都滿足一個給定的條件，通常用於排他控制

用於確保如果任何兩行都被同一個 EXCLUDE 約束所約束，則一個指定的操作將評估為 FALSE

例如，確保同一時間範圍內沒有兩個預約

CREATE TABLE reservation (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    EXCLUDE USING gist (tsrange(start_time, end_time) WITH &&)
);

在這裡，EXCLUDE 約束防止在相同的時間範圍內進行重疊的預約

這邊我們一樣來驗證一下

--  先看看資料庫中的所有訂單

SELECT * FROM reservation;
 id |     start_time      |      end_time
----+---------------------+---------------------
  1 | 2023-09-24 15:00:00 | 2023-09-24 17:00:00
  2 | 2023-09-24 17:00:00 | 2023-09-24 18:00:00
(2 rows)

嘗試寫入一樣的時間訂單

INSERT INTO reservation (start_time, end_time) VALUES ('2023-09-24 17:00:00', '2023-09-24 18:00:00');

ERROR:  conflicting key value violates exclusion constraint "reservation_tsrange_excl"
DETAIL:  Key (tsrange(start_time, end_time))=(["2023-09-24 17:00:00","2023-09-24 18:00:00")) conflicts with existing key (tsrange(start_time, end_time))=(["2023-09-24 17:00:00","2023-09-24 18:00:00")).

上面顯示 你已經有一個一樣 existing key 存在了

8. 自定義你的 CONSTRAINT 名稱

在建立約束(CONSTRAINT)時，你可以選擇為約束指定一個名稱

這會讓後續的修改或刪除約束變得更容易，因為你可以直接使用這個名稱來引用約束

剛剛上面得情況我們都是將約束直些寫在欄位後面，當然我們也可以先建立欄位之後，再去新增我們的 CONSTRAINT 以及給它自訂的名稱

用法大致如下

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    
    
CONSTRAINT pd_pk PRIMARY KEY (product_no),
CONSTRAINT unique_name UNIQUE (name),
CONSTRAINT positive_price CHECK (price > 0)
);

demo_db=# =\d products

                Table "public.products"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 product_no | integer |           | not null |
 name       | text    |           |          |
 price      | numeric |           |          |
 
Indexes:
    "pd_pk" PRIMARY KEY, btree (product_no)  -- 我們剛剛自定義的約束名都有出現
    "unique_name" UNIQUE CONSTRAINT, btree (name) -- 但是還是要取名讓人易懂
Check constraints:
    "positive_price" CHECK (price > 0::numeric)

結語

花了兩天總算把約束 (CONSTRAINT) 介紹完了，約束這東西就很想驗證器能確保寫入資料庫的資料會符合自己設定的約束，
但是使用約束還是要看實際的案例去設定才會比較好

感謝大家收看