Oracle 主鍵約束設置指南：自增序列與唯一約束區別詳解

Oracle 主鍵約束設置指南：自增序列與唯一約束區別詳解

一、主鍵 vs 唯一約束 核心差異

二、外鍵 (FK) 關鍵規則

• FK 必須引用父表 PK 或 UK。
• 插入子表記錄時，父表必須存在對應值，否則報錯。
• 刪除父表記錄時，若子表有關聯數據報錯；除非 FK 定義 ON DELETE CASCADE (級聯刪除) 或 ON DELETE SET NULL。
• 刪除父表需 DROP TABLE parent CASCADE CONSTRAINTS，子表 FK 自動移除，數據保留。

三、自增主鍵實現：Sequence + Trigger 實戰

-- 1. 創建表
CREATE TABLE pk_increase_test (
  id   NUMBER(10) PRIMARY KEY,
  name VARCHAR2(30)
);

-- 2. 創建序列
CREATE SEQUENCE pk_incre_sequence
  MINVALUE 1
  NOMAXVALUE
  START WITH 1
  INCREMENT BY 1
  NOCYCLE
  NOCACHE;

-- 3. 創建觸發器
CREATE OR REPLACE TRIGGER pk_trigger
BEFORE INSERT ON pk_increase_test
FOR EACH ROW
BEGIN
  SELECT pk_incre_sequence.NEXTVAL INTO :NEW.id FROM dual;
END;
/

-- 4. 測試
INSERT INTO pk_increase_test (id) VALUES (NULL);  -- 觸發器填充 1
INSERT INTO pk_increase_test (id) VALUES (NULL);  -- 2
INSERT INTO pk_increase_test (id) VALUES (NULL);  -- 3
SELECT * FROM pk_increase_test;
-- ID  NAME
-- 1
-- 2
-- 3

注意：即使 INSERT 顯式傳入 VALUES (181)，觸發器仍會覆蓋為序列值 (除非加 WHEN (NEW.id IS NULL) 判斷)。

四、唯一序列生成替代方案 (無觸發器)

應用層直接調用序列：

-- Java/JDBC
String sql = "INSERT INTO pk_increase_test (id, name) VALUES (pk_incre_sequence.NEXTVAL, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "Test");
ps.executeUpdate();

優勢：無觸發器開銷、事務邊界清晰。劣勢：需應用層統一規範、多應用接入易不一致。

五、12c+ Identity 列 (現代標準)

CREATE TABLE pk_identity_test (
  id   NUMBER(10) GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(30)
);

六、最佳實踐建議

1. **業務主鍵優先**：若業務有天然唯一鍵 (如身份證號、訂單號)，直接作 PK，避免無意義代理鍵。
2. **代理鍵用 Sequence/Identity**：無業務鍵時，12c+ 用 Identity，舊版本用 Sequence+Trigger (加 WHEN (NEW.id IS NULL))。
3. **複合主鍵謹慎用**：僅用於關聯表、弱實體，注意索引前綴性與外鍵引用複雜度。
4. **命名規範**：PK_表名、UK_表名_列名、FK_子表_父表、SEQ_表名、TRG_表名_BIR。