SQL PRIMARY KEY 主鍵實戰教程：創建、修改、刪除與最佳實踐

主鍵約束核心概念

主鍵 (PRIMARY KEY) 是關係型數據庫表的唯一標識符，具有三大核心特性：

• 唯一性：值在表中不可重複
• 非空性：不允許 NULL 值
• 單一性：每個表僅能定義一個主鍵約束 (但可包含多列組合主鍵)

創建表時定義主鍵

單列主鍵 - 最常用模式

-- MySQL / MariaDB (推薦：自增整數主鍵)
CREATE TABLE persons (
    p_id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255),
    PRIMARY KEY (p_id)
);

-- SQL Server / Oracle / PostgreSQL / SQLite (標準寫法)
CREATE TABLE persons (
    p_id INT NOT NULL PRIMARY KEY,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255)
);

-- 通用帶約束名寫法 (便於管理)
CREATE TABLE persons (
    p_id INT NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255),
    CONSTRAINT pk_persons PRIMARY KEY (p_id)
);

多列組合主鍵 - 關係表/無業務主鍵場景

-- 訂單明細表：訂單ID + 產品ID 唯一標識一行
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    unit_price DECIMAL(10,2),
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);

-- 學生選課表：學生 + 課程 + 學期 三列組合
CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    semester VARCHAR(20) NOT NULL,
    grade DECIMAL(3,1),
    CONSTRAINT pk_student_courses PRIMARY KEY (student_id, course_id, semester)
);

修改現有表結構 (ALTER TABLE)

添加主鍵約束

-- 前提：目標列必須已定義為 NOT NULL
ALTER TABLE persons ADD PRIMARY KEY (p_id);

-- 多列組合主鍵帶約束名
ALTER TABLE persons ADD CONSTRAINT pk_persons PRIMARY KEY (p_id, last_name);

-- MySQL 特有：一步添加自增主鍵列並設為主鍵
ALTER TABLE persons 
ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

刪除主鍵約束

-- MySQL / MariaDB (無需約束名)
ALTER TABLE persons DROP PRIMARY KEY;

-- SQL Server / Oracle / PostgreSQL (需指定約束名)
ALTER TABLE persons DROP CONSTRAINT pk_persons;

-- 查詢約束名方法
-- MySQL: SHOW CREATE TABLE persons;
-- SQL Server: SELECT name FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('persons');
-- Oracle: SELECT constraint_name FROM user_constraints WHERE table_name = 'PERSONS' AND constraint_type = 'P';
-- PostgreSQL: SELECT conname FROM pg_constraint WHERE contype = 'p' AND conrelid = 'persons'::regclass;

MySQL 主鍵特有操作示例

-- 查看表結構確認主鍵
DESCRIBE persons;
SHOW CREATE TABLE persons;

-- 修改自增起始值
ALTER TABLE persons AUTO_INCREMENT = 10000;

-- 獲取最後插入的自增 ID
INSERT INTO persons (last_name, first_name) VALUES ('Wang', 'Wu');
SELECT LAST_INSERT_ID();  -- 返回剛插入行的 p_id

-- 重置自增計數器 (慎用，生產環境需極度小心)
ALTER TABLE persons AUTO_INCREMENT = 1;

主鍵設計最佳實踐指南

1. 類型選擇決策矩陣

場景	推薦類型	理由
單機/主從架構業務表	BIGINT UNSIGNED AUTO_INCREMENT	性能最優、空間小、順序寫入、64位足夠大
分庫分表/分布式系統	雪花算法 ID (BIGINT) / UUID v7	全局唯一、趨勢遞減、含時間戳
數據遷移合併/異構同步	UUID (CHAR(36)) / UUID v7	無協調全局唯一、避免衝突
字典/配置/關係表	業務自然鍵 / 組合主鍵	語義清晰、無額外存儲、符合范式

2. InnoDB 聚簇索引特性深度影響

• 數據物理順序 = 主鍵順序：自增主鍵保證順序追加寫入，無頁分裂
• 二級索引葉子存主鍵值：主鍵越短，所有二級索引越小，緩存命中率越高
• 主鍵查詢最快：直接定位聚簇索引葉子節點，無需回表
• 避免更新主鍵：等價於 DELETE + INSERT，極其昂貴

3. 組合主鍵列順序黃金法則

1. 高選擇性列優先 (基數大、區分度高)
2. 符合最左前綴原則，覆蓋常用查詢模式
3. 範圍查詢列放最後
4. 列數建議 ≤ 3，過多影響性能與維護

常見反模式與避坑指南

| 反模式 | 問題 | 正確做法 | |---------|------|----------| | 無主鍵表 | InnoDB 生成 6 字節隱藏主鍵，不可控、不可引用、複製隱患 | 顯式定義 `id BIGINT AUTO_INCREMENT PRIMARY KEY` | | UUID 作為主鍵 (無序) | 頁分裂嚴重、索引碎片、緩存效率低、主鍵長 36 字節 | 使用有序 UUID (v7/有序 COMB) 或雪花算法 | | 業務字段作主鍵 (如手機號/身份證) | 業務變更導致主鍵修改災難、長度大、非順序 | 代理鍵 + 唯一索引約束業務字段 | | 組合主鍵列順序錯誤 | 索引失效、查詢掃描範圍大 | 高選擇性在前、最左前綴匹配查詢 | | 自增 ID 耗盡 (INT 上限 21 億) | 插入失敗、業務中斷 | 使用 BIGINT (922 京)，實際永不耗盡 |

總結

主鍵是表的「身份證」，設計原則是 短小、順序、業務無關、穩定不變。MySQL InnoDB 下自增 BIGINT 是 99% 場景的最優選。掌握創建/修改/刪除 DDL 語法、理解聚簇索引機制、避開常見反模式，能為系統性能與可維護性打下堅實基礎。