SQL 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)
);

-- 學生選課表：學生ID + 課程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';

主鍵設計最佳實踐

1. 主鍵類型選擇

2. InnoDB 主鍵特別注意事項

• 聚簇索引：InnoDB 表數據按主鍵順序物理存儲，主鍵即聚簇索引
• 順序遞增：自增主鍵保證順序寫入，減少頁分裂，提升寫入性能
• 二級索引包含主鍵：所有二級索引葉子節點存儲主鍵值，主鍵越短二級索引越小
• 避免更新主鍵：主鍵更新等價於 DELETE + INSERT，極其昂貴

3. 組合主鍵列順序原則

• 高選擇性列優先（基數大的列在前）
• 符合最左前綴原則，支持常用查詢模式
• 範圍查詢列放最後
• 組合主鍵列數建議 ≤ 3 個

常見問題與解決方案

問題 1：主鍵衝突錯誤 (Duplicate entry for key 'PRIMARY')

原因：插入重複主鍵值、自增計數器異常、主從複製衝突

解決：檢查業務邏輯、重置自增值 ALTER TABLE t AUTO_INCREMENT = 1000、使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE

問題 2：無主鍵表的隱患

風險：InnoDB 自動生成 6 字節隱藏主鍵 (GEN_CLUST_INDEX)，無法控制、不利於複製、不可作為外鍵引用

建議：所有表顯式定義主鍵，哪怕是 id BIGINT AUTO_INCREMENT PRIMARY KEY

問題 3：主鍵與外鍵關係

-- 父表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

-- 子表引用父表主鍵
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_id INT,
    emp_name VARCHAR(100),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

總結

主鍵是關係型數據庫表的核心標識。正確設計主鍵（類型選擇、單列/組合、命名規範）、理解存儲引擎特性（特別是 InnoDB 聚簇索引機制）、掌握 DDL 操作語法，是數據庫設計與維護的基本功。遵循「短小、順序、業務無關」原則選擇主鍵，能為系統長期穩定運行奠定基礎。