MySQL 完整性約束詳解：主鍵、外鍵、非空、唯一、自增、預設值六大約束建表實戰

MySQL 六大完整性約束：定義、語法、行為與最佳實踐

完整性約束在**資料庫層**強制保證資料品質，防止髒資料入庫。六大約束：主鍵 (PK)、外鍵 (FK)、非空 (NOT NULL)、唯一 (UNIQUE)、自增 (AUTO_INCREMENT)、預設值 (DEFAULT)。

1. 主鍵 (PRIMARY KEY)

• 定義：唯一標識每行，不可重複、不可為 NULL，一表僅一個。
• 單欄主鍵：CREATE TABLE t (id INT PRIMARY KEY, ...);
• 複合主鍵：CREATE TABLE t (id INT, course_id INT, PRIMARY KEY (id, course_id));
• InnoDB：主鍵即聚簇索引，葉節點存完整行，**務必短小、單調遞增** (自增整數/有序 UUID)，避免頁分裂。

2. 外鍵 (FOREIGN KEY)

• 定義：建立父子表關聯，子表 FK 必須引用父表 PK/UK，維護參考完整性。
• 語法：
  CREATE TABLE child (id INT PRIMARY KEY, pid INT, CONSTRAINT fk_pid FOREIGN KEY (pid) REFERENCES parent(id));
• 級聯動作：ON DELETE CASCADE (刪父刪子)、ON UPDATE CASCADE、SET NULL、RESTRICT (預設)、NO ACTION。
• 生產建議：高併發核心表禁用外鍵，鎖爭用嚴重、死鎖頻發、擴展性差。改由應用層/中間件維護一致性，或異步校驗修復。

3. 非空 (NOT NULL)

• 定義：欄位不允許存入 NULL。
• 語法：CREATE TABLE t (id INT NOT NULL, name VARCHAR(20) NOT NULL);
• 效能：NULL 需額外 1 bit 標誌位，NOT NULL 節省空間、簡化優化器判斷、利於索引 (索引欄建議均 NOT NULL)。

4. 唯一 (UNIQUE)

• 定義：欄位值在表中唯一，允許多個 NULL (標準 SQL)，一表可多個。
• 語法：CREATE TABLE t (id INT PRIMARY KEY, email VARCHAR(100) UNIQUE);
• 索引本質：UNIQUE 即唯一索引，查詢走 eq_ref/const 極快。
• 複合唯一：UNIQUE KEY uk_user_course (user_id, course_id)。

5. 自增 (AUTO_INCREMENT)

• 定義：整數型欄位自動分配遞增值，插入時不指定或指定 NULL/0 觸發。
• 限制：一表僅一個、必須為索引鍵一部分 (通常是 PK)、整數型 (TINYINT~BIGINT)。
• 語法：CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, ...);
• 行為：重啟後最大值 +1 (5.7 前)、持久化最大值 (8.0+)、刪除最大行不回收、事務回滾消耗值 (間隙)。
• 分片應用：auto_increment_offset + auto_increment_increment 錯開多節點 ID。

6. 預設值 (DEFAULT)

• 定義：插入不指定欄位時自動填充的值。
• 支援：常數、CURRENT_TIMESTAMP (TIMESTAMP/DATETIME 8.0.13+)、UUID() (8.0.13+)、運算式 (8.0.13+)。不支援：任意函數 (NOW、RAND 等)、子查詢。
• 語法：CREATE TABLE t (status TINYINT DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

綜合建表範例

CREATE TABLE orders (
  order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  order_no VARCHAR(32) NOT NULL UNIQUE,
  status TINYINT NOT NULL DEFAULT 1 COMMENT '1待付款 2已付款 3已發貨 4完成 5取消',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單表';

遷移自 SQL Server / Oracle 注意