跳至主要內容
日沐 SunBathe

Appearance

資料表設計與約束 ​

良好的資料表設計是系統穩定的基石。本篇將介紹 PostgreSQL 的各種約束條件，以及如何設計出符合正規化原則的資料庫結構。

一、 主鍵 (PRIMARY KEY) ​

主鍵是資料表中唯一識別每一列的欄位，必須滿足：

  1. 唯一性：不能有重複值
  2. 非空：不能是 NULL

基本語法 ​

sql
-- 單一欄位主鍵
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- 複合主鍵
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

SERIAL vs IDENTITY ​

sql
-- 傳統寫法
id SERIAL PRIMARY KEY

-- 現代寫法（PostgreSQL 10+，推薦）
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- 允許手動插入值的 IDENTITY
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

GENERATED ALWAYS 會禁止手動插入 ID，更能確保資料一致性。

二、 外鍵 (FOREIGN KEY) ​

外鍵用於建立資料表之間的關聯，確保參照完整性。

基本語法 ​

sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 更明確的寫法
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT fk_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
);

刪除與更新行為 ​

當父表的資料被刪除或更新時，子表該如何處理？

sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)
        ON DELETE CASCADE      -- 父表刪除時，子表一起刪除
        ON UPDATE CASCADE      -- 父表 ID 更新時，子表跟著更新
);
選項說明
CASCADE連動刪除/更新子表
SET NULL將外鍵欄位設為 NULL
SET DEFAULT將外鍵欄位設為預設值
RESTRICT禁止刪除/更新（預設）
NO ACTION同 RESTRICT，但檢查時機不同

> **CASCADE 要小心**

在生產環境使用 ON DELETE CASCADE 前要三思，避免誤刪造成連鎖刪除。

三、 UNIQUE 約束 ​

確保欄位值在整張表中唯一：

sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE,
    username TEXT
);

-- 複合唯一約束
CREATE TABLE user_roles (
    user_id INTEGER,
    role_name TEXT,
    UNIQUE (user_id, role_name)
);

UNIQUE vs PRIMARY KEY ​

特性PRIMARY KEYUNIQUE
允許 NULL否是（且 NULL 不算重複）
數量限制每表只能有一個可以有多個
自動建立索引是是

四、 NOT NULL 約束 ​

禁止欄位為空值：

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT  -- 可為 NULL
);

事後新增 NOT NULL ​

sql
-- 欄位已有 NULL 值時會失敗
ALTER TABLE products ALTER COLUMN name SET NOT NULL;

-- 先填補 NULL 值
UPDATE products SET name = '未命名' WHERE name IS NULL;
ALTER TABLE products ALTER COLUMN name SET NOT NULL;

五、 CHECK 約束 ​

自訂驗證條件：

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) CHECK (price > 0),
    discount NUMERIC(3, 2) CHECK (discount >= 0 AND discount <= 1)
);

-- 具名約束（方便除錯）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),
    cost NUMERIC(10, 2),
    CONSTRAINT price_above_cost CHECK (price >= cost)
);

插入違反約束的資料會報錯：

sql
INSERT INTO products (name, price) VALUES ('Test', -100);
-- ERROR: new row for relation "products" violates check constraint "products_price_check"

六、 DEFAULT 預設值 ​

sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT DEFAULT 'draft',
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入時不指定這些欄位，會自動填入預設值
INSERT INTO posts (title) VALUES ('Hello World');

GENERATED 欄位 ​

PostgreSQL 12+ 支援計算欄位：

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),
    quantity INTEGER,
    total NUMERIC(10, 2) GENERATED ALWAYS AS (price * quantity) STORED
);

-- total 會自動計算，無法手動插入
INSERT INTO products (price, quantity) VALUES (100, 5);
SELECT total FROM products;  -- 結果：500.00

七、 資料表繼承 ​

PostgreSQL 獨特的功能，允許資料表繼承另一張表的結構：

sql
-- 父表
CREATE TABLE vehicles (
    id SERIAL PRIMARY KEY,
    brand TEXT,
    model TEXT,
    year INTEGER
);

-- 子表繼承父表
CREATE TABLE cars (
    doors INTEGER,
    fuel_type TEXT
) INHERITS (vehicles);

CREATE TABLE motorcycles (
    engine_cc INTEGER
) INHERITS (vehicles);
sql
-- 查詢所有車輛（包含子表）
SELECT * FROM vehicles;

-- 只查詢父表本身
SELECT * FROM ONLY vehicles;

> **實務建議**

資料表繼承在現代設計中較少使用，通常改用 分區表 或 JSON 欄位 來達成類似目的。

八、 正規化原則 ​

正規化是消除資料冗餘、避免更新異常的設計方法。

第一正規化 (1NF) ​

規則：每個欄位只存單一值，不能是陣列或重複群組。

sql
-- 違反 1NF
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    products TEXT  -- '蘋果,香蕉,橘子'（逗號分隔）
);

-- 符合 1NF（使用關聯表）
CREATE TABLE order_items (
    order_id INTEGER,
    product_name TEXT
);

TIP

PostgreSQL 的陣列型別是例外，某些場景下使用陣列反而更有效率。

第二正規化 (2NF) ​

規則：非主鍵欄位必須完全依賴於整個主鍵。

sql
-- 違反 2NF（product_name 只依賴 product_id，不依賴 order_id）
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    product_name TEXT,  -- 冗餘！
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- 符合 2NF（拆成兩張表）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

第三正規化 (3NF) ​

規則：非主鍵欄位不能依賴於其他非主鍵欄位。

sql
-- 違反 3NF（city 依賴 zip_code，而非直接依賴 id）
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    zip_code TEXT,
    city TEXT  -- 冗餘！可從 zip_code 推導
);

-- 符合 3NF
CREATE TABLE zip_codes (
    code TEXT PRIMARY KEY,
    city TEXT
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    zip_code TEXT REFERENCES zip_codes(code)
);

反正規化 ​

有時候為了查詢效能，會刻意違反正規化原則：

sql
-- 反正規化範例：在訂單表儲存當時的產品名稱和價格
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    product_name TEXT,      -- 快照，避免 JOIN
    unit_price NUMERIC,     -- 快照，鎖定當時價格
    quantity INTEGER
);

九、 約束管理 ​

新增約束 ​

sql
-- 新增 NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- 新增 UNIQUE
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

-- 新增 CHECK
ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price > 0);

-- 新增外鍵
ALTER TABLE orders ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id);

移除約束 ​

sql
-- 移除具名約束
ALTER TABLE users DROP CONSTRAINT unique_email;

-- 移除 NOT NULL
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

停用約束（外鍵） ​

sql
-- 暫時停用觸發器（包含外鍵檢查）
ALTER TABLE orders DISABLE TRIGGER ALL;

-- 大量匯入資料...

-- 重新啟用
ALTER TABLE orders ENABLE TRIGGER ALL;

總結 ​

約束用途
PRIMARY KEY唯一識別每一列
FOREIGN KEY建立表間關聯
UNIQUE確保欄位值唯一
NOT NULL禁止空值
CHECK自訂驗證條件
DEFAULT設定預設值

設計原則：

  • 遵循正規化避免冗餘
  • 適當反正規化提升效能
  • 善用約束確保資料完整性

進階挑戰 ​

  1. 設計一個電商資料庫，包含 users、products、orders、order_items 四張表，並設定正確的外鍵關聯
  2. 為 orders 表建立一個 CHECK 約束，確保 total_amount 始終為正數
  3. 嘗試建立一個複合主鍵的表，並思考何時該用複合主鍵、何時該用單一主鍵

延伸閱讀與資源 ​

← 上一章：資料型別 | 返回專題首頁 | 下一章：SELECT 深入解析 →