主題: PostgreSQL

資料庫不是大型 JSON：先讓 constraint 擋住不可能的資料

table、key 與 constraint 是所有寫入路徑共用的資料契約。前端驗證負責體驗，資料庫必須拒絕不可能的資料。

很多專案剛開始時，資料看起來都能先塞進一個 JSON 欄位：使用者資料一包、訂單資料一包、設定又一包。前幾天很快，過幾週後就開始出現難題：誰能寫這些資料？email 可以重複嗎？不存在的使用者能有訂單嗎？價格可以是負數嗎？

這些不是 ORM、表單或 API 的小細節，而是資料模型的工作。關聯式資料庫最實際的價值，不只是能寫 SQL；它能把「哪些資料不可能成立」放在離資料最近的地方，讓 API、後台、排程與日後的新程式都遵守同一份規則。

這篇從最基本的 table、row、column 開始。目標不是背 DDL 關鍵字，而是建立一個判斷順序：先描述資料的事實，再把不能違反的事實交給 database。

table、row、column：先把資料說清楚

把「客戶」當成一種 entity。一個 customers table 記錄所有客戶；每一個 row 是一位具體客戶；每一個 column 是那位客戶的一項事實，例如姓名、email 或建立時間。

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

這段 SQL 看起來比一個 JSON object 囉嗦，但它回答了幾個很重要的問題：

• id 是每列穩定的身份，不因顯示名稱或 email 更動而改變。
• email 不可缺少，也不能重複。
• name 不可缺少；是否要限制長度或格式，則是另一條明確的業務規則。
• created_at 即使 application 忘了填，資料庫也會給出建立時間。

schema 不是把未來鎖死，而是把今天已知的規則說出來。規則改變時可以做 migration；一開始完全不說，則只是把決定延後到資料已經混亂的時候。

primary key 是身分；UNIQUE 是另一個業務規則

primary key 的核心是「每一列都有唯一且不可為 NULL 的識別值」。它讓其他 table 能可靠地指向這列，也讓更新、刪除與除錯有穩定的目標。

email 常常也值得 UNIQUE，但它不一定適合當 primary key：使用者可能換 email、大小寫與驗證政策可能改變，而內部的 id 不需要跟著漂移。這是很常見、也很健康的分工：用一個穩定 key 辨識資料，再用 UNIQUE 表達「這項商業資料不能撞號」。

不要為每個欄位都加 unique。電話號碼、姓名、公司名稱是否可重複，應由真正的業務語意決定；constraint 是規則，不是裝飾。

foreign key：讓訂單不能指向不存在的客戶

訂單屬於客戶，這是一條資料關係。把它寫成 customer_id 欄位，再加 foreign key：

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

現在資料庫不會接受一筆 customer_id 指向不存在客戶的訂單；total_cents 也不能是負數。它不是「讓後端少寫兩個 if」而已：只要有人從管理工具、批次工作或另一個 service 寫入，規則仍在。

PRIMARY KEY 與 UNIQUE 在 PostgreSQL 會建立對應的 index；但 foreign key 的參照端欄位，例如 orders.customer_id，不會因為宣告 foreign key 自動得到 index。當你常用它 join、查詢或刪除客戶時，再依真實 query 加 index；這是之後要量測的效能決定，不是今天先猜的儀式。

NOT NULL、CHECK 與 DEFAULT：把不能含糊的地方寫出來

NULL 不是空字串，也不是零；它代表「沒有值」或「未知」。若 email 必須存在，就用 NOT NULL。若真的要找沒有填值的資料，SQL 要寫 IS NULL，不是 = NULL。

CHECK 讓值符合一個條件，例如金額不可小於零、評分只能是 1 到 5。DEFAULT 則為合理的預設事實提供值，例如建立時間。這三種約束各做不同工作：

先把這些留在 table 定義裡，比把規則散在 controller、表單與 cron job 更容易審查。也不必追求一次設計到完美：只為已經確認的資料事實加 rule，之後隨產品需求演進。

前端驗證與 database constraint 不衝突

前端驗證仍很重要。它能即時告訴使用者 email 格式不對、金額缺漏，避免按送出後才看到錯誤；這是使用體驗。

但前端不是資料完整性的最後防線。有人可以直接呼叫 API、舊版 app 可能仍在使用、後台匯入與排程也可能略過表單。database constraint 負責的是：不論寫入路徑從哪裡來，不能成立的資料都不該進去。

最順的分工是：前端友善地提早提示，API 把錯誤轉成清楚回應，資料庫最後保證事實。 三層可以重複檢查，但各自有不同責任。

一個足夠實用的建模順序

下次要建立新 table 時，可以照這六步走：

1. 找出名詞：客戶、訂單、文章、留言，通常是 candidate table。
2. 列出事實：每個 entity 真正需要哪些 column，以及合理型別。
3. 選穩定身分：每列用 primary key 辨識，不把容易變動的顯示資料塞進身分。
4. 寫不變條件：用 NOT NULL、UNIQUE、CHECK 與 DEFAULT 表達已確認的規則。
5. 接上關係：用 foreign key 表示誰屬於誰，或誰引用誰。
6. 再做 UI 與 API validation，讓錯誤更早、更容易理解。

這不是大型資料庫設計方法論，只是一個讓基本規則不被遺漏的起點。當 table 變多，再談正規化、transaction、index 與 query plan，會有更清楚的落點。

我學到什麼

• 我會先把資料的身分、必填條件與關係說清楚，再急著寫 application code。
• primary key 是穩定識別；UNIQUE、NOT NULL 與 CHECK 則分別表達不同的不變條件。
• 前端 validation 能減少挫折，但只有 database constraint 能讓所有寫入路徑共用資料完整性。
• index 是根據實際查詢模式做的效能選擇，不是每看到 foreign key 就盲目追加的預設動作。

結論：先讓錯的資料沒有機會變成歷史

資料庫 schema 不需要一開始就很複雜。一張清楚的 table，加上穩定的 primary key、必要的 NOT NULL、適當的 UNIQUE、CHECK 與 foreign key，就已經替未來省掉大量猜測。

把 JSON 當成傳輸格式沒有問題；把 database 當成沒有規則的大型 JSON 收集桶，才會讓每一段程式都得重新猜規則。先讓 constraint 擋住不可能的資料，之後寫功能才有可靠的地基。

外部參考連結

• PostgreSQL Tutorial
• PostgreSQL：Data Definition 與 Constraints
• PostgreSQL：CREATE TABLE
• daily.dev：How a SQL database works
• daily.dev：Creating Foreign Keys — SQL Fundamentals with PostgreSQL
• Reddit：DBMS: What’s the use of defining a primary key?
• Reddit：How senior engineers design production databases

延伸學習

• CS50 SQL Week 1：Relating：有影片的關聯與 key 入門，適合把 table、primary key、foreign key 與 join 接成完整畫面。
• CS50 SQL Lecture 2 notes：用 schema、constraints 與 normalization 的脈絡，複習本篇每個 rule 為什麼存在。