MySQL 課程 – 外鍵與約束 (Constraint)

主題：CHECK 與 NOT NULL

---

簡介

在關聯式資料庫中，資料完整性 是系統穩定運作的根本。若資料表允許不合理的值寫入，後續的查詢、報表甚至商業邏輯都可能出錯。MySQL 提供了多種 約束 (Constraint)，其中最常見且最基礎的就是 NOT NULL 與 CHECK。

• NOT NULL 直接保證欄位一定要有值，避免因為遺漏或空值 (NULL) 而產生的程式錯誤。
• CHECK 則允許開發者定義更細緻的規則，例如「年齡必須在 0~120 之間」或「訂單金額不能為負」。

掌握這兩個約束的使用方式與最佳實踐，能讓資料庫在寫入階段即完成第一層資料驗證，減少應用程式端的防呆程式碼，提升系統的可維護性與可靠度。

---

核心概念

1. NOT NULL 約束

NOT NULL 是最直觀的限制：欄位不可為 NULL。在建立或修改表格時，只要在欄位定義後加上 NOT NULL，MySQL 就會在 INSERT / UPDATE 時檢查此欄位是否有提供實際值。

為什麼要使用 NOT NULL？

• 避免空值帶來的邏輯錯誤：例如計算總金額時，NULL 會自動傳遞成 NULL，導致結果不正確。
• 提升索引效能：大多數索引在包含 NULL 的情況下會有額外的儲存與比較成本。
• 明確資料模型：讓資料庫設計者與使用者一目了然哪些欄位是必填的。

基本語法

CREATE TABLE employee (
    emp_id   INT PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    salary   DECIMAL(10,2)   -- 允許 NULL，若未給予則預設為 NULL
);

2. CHECK 約束

在 MySQL 8.0 之後，CHECK 約束正式支援（早期版本僅是語法層面的偽約束）。CHECK 允許在欄位或整個表格層級上加入 布林表達式，只有當表達式結果為 TRUE 時，資料才會被寫入。

CHECK 的適用情境

• 數值範圍限制（如年齡、分數）
• 文字長度或格式驗證（如電話號碼、郵遞區號）
• 多欄位之間的相依關係（如開始日期必須早於結束日期）

基本語法

CREATE TABLE product (
    product_id   INT PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    price        DECIMAL(8,2) NOT NULL,
    discount_pct TINYINT      NOT NULL,
    CHECK (price >= 0),
    CHECK (discount_pct BETWEEN 0 AND 100)
);

注意：CHECK 表達式必須是 可確定 的布林式，不能使用子查詢或不可預測的函式。

---

程式碼範例

以下提供 5 個實務中常見的 NOT NULL 與 CHECK 組合範例，並附上說明。

範例 1：使用者帳號表 – 不允許空的使用者名稱與密碼

CREATE TABLE users (
    user_id   INT AUTO_INCREMENT PRIMARY KEY,
    username  VARCHAR(30) NOT NULL,   -- 必填
    password  VARCHAR(255) NOT NULL,  -- 必填，通常儲存雜湊值
    email     VARCHAR(100)            -- 允許 NULL，若未提供則為 NULL
);

說明：username 與 password 加上 NOT NULL，確保每筆資料都有可辨識的帳號與驗證資訊。

範例 2：訂單金額不得為負數

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL,
    order_total   DECIMAL(10,2) NOT NULL,
    CHECK (order_total >= 0)               -- 金額不能為負
);

說明：使用 CHECK 防止錯誤的負值寫入，避免後續統計或退款流程出現異常。

範例 3：生日欄位必須是過去的日期，且不可為 NULL

CREATE TABLE members (
    member_id   INT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    birthday    DATE NOT NULL,
    CHECK (birthday <= CURDATE())          -- 生日只能是今天或更早的日期
);

說明：birthday 同時使用 NOT NULL 與 CHECK，保證資料完整且邏輯正確。

範例 4：產品庫存必須是非負整數，且庫存為 0 時必須提供下次補貨日期

CREATE TABLE inventory (
    product_id      INT PRIMARY KEY,
    stock_qty       INT NOT NULL,
    restock_date    DATE,
    CHECK (stock_qty >= 0),                              -- 庫存不可為負
    CHECK (stock_qty > 0 OR restock_date IS NOT NULL)   -- 庫存為 0 時必須有補貨日期
);

說明：第二個 CHECK 示範了 跨欄位 條件，確保缺貨時仍保有相關資訊。

範例 5：員工等級只能是 A、B、C 三種，且等級欄位不可為 NULL

CREATE TABLE staff (
    staff_id   INT PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    level      CHAR(1) NOT NULL,
    CHECK (level IN ('A','B','C'))    -- 限制等級取值範圍
);

說明：IN 內建於 CHECK 表達式，可直接列舉允許的值集合。

---

常見陷阱與最佳實踐

陷阱	可能原因	解決方案 / 最佳實踐
CHECK 沒有生效	使用 MySQL 5.7 或更舊版本	升級至 MySQL 8.0+，或改用觸發器 (TRIGGER) 進行驗證
INSERT 時忘記提供 NOT NULL 欄位	應用程式未檢查輸入	在 API 層加入參數驗證，並在資料庫層使用 NOT NULL 作最後防線
CHECK 表達式過於複雜	使用子查詢、不可預測函式或自訂函式	保持 CHECK 為簡單布林式，複雜驗證可改用 BEFORE INSERT/UPDATE 觸發器
NULL 與 NOT NULL 混用導致統計錯誤	直接在 SELECT 中使用 SUM(col) 而未處理 NULL	使用 COALESCE(col,0) 或在欄位上加 DEFAULT 0 並設定 NOT NULL
多欄位 CHECK 失效	MySQL 8.0 仍不支援 CHECK 直接引用同一表的其他列（舊版 bug）	確認 MySQL 版本，必要時改寫為觸發器或使用 GENERATED 欄位配合驗證

最佳實踐

1. 先在資料庫層加 NOT NULL，再根據業務需求逐步加入 CHECK。
2. 為欄位設定合理的 DEFAULT 值（如 0、''），減少 INSERT 時遺漏欄位的機會。
3. 使用明確的錯誤訊息：在應用程式捕捉 SQLIntegrityConstraintViolationException，回傳使用者友善的提示。
4. 定期檢查約束：使用 SHOW CREATE TABLE 或 INFORMATION_SCHEMA.CHECK_CONSTRAINTS 確認約束仍符合需求。
5. 測試邊界值：寫單元測試驗證 CHECK 條件的上下限、NULL 行為與跨欄位邏輯。

---

實際應用場景

1. 電子商務 – 訂單金額與折扣

在訂單表中，order_total 必須大於等於 0，同時 discount_pct 必須在 0~100 之間。若折扣後金額仍為負，系統會直接阻止寫入，避免財務報表出現異常。

2. 人力資源 – 員工年齡與入職日期

公司政策規定員工年齡必須在 18~65 歲之間，且入職日期不能早於出生日期。透過 CHECK (age BETWEEN 18 AND 65) 以及 CHECK (hire_date >= birth_date)，即在資料寫入階段即完成驗證。

3. 金融系統 – 交易金額與餘額

交易表的 amount 必須是正數，且 balance_after 必須等於 balance_before - amount（或加上存款）。這類跨欄位的關係可用 CHECK 或觸發器保證，防止錯誤的負餘額產生。

4. 設備管理 – 庫存與維護日期

當設備庫存為 0 時，必須提供下一次維護或補貨日期。使用 CHECK (stock_qty > 0 OR next_maintenance IS NOT NULL)，確保缺貨時仍有追蹤資訊。

---

總結

• NOT NULL 為最基礎的資料完整性保證，能防止欄位出現未定義的空值。
• CHECK 讓開發者能在資料庫層定義更細緻的業務規則，從數值範圍到跨欄位邏輯皆可涵蓋。
• 在 MySQL 8.0 之後，CHECK 已正式生效，建議在新專案中即時採用；舊版則可考慮觸發器作為備選方案。
• 結合 DEFAULT、UNIQUE、FOREIGN KEY 等其他約束，能打造出 自我驗證、高可靠性 的資料模型。

透過正確運用 NOT NULL 與 CHECK，不僅能減少程式碼中的防呆邏輯，更能在資料寫入的第一步就捕捉錯誤，提升系統的穩定性與維護效率。希望本篇文章能幫助你在 MySQL 中建立更健全的資料庫結構，讓資料完整性成為你的系統最堅實的基石。祝你開發順利！