您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 完整性約束條件全攻略：從 CHECK 到外鍵的建立、啟用與效能

編輯：Oracle 資料庫基礎

五大約束類型速覽

約束	用途	索引支援	可延遲
NOT NULL	欄位非空	無	否
CHECK	商業規則驗證	無	是
UNIQUE	唯一值	B-tree 索引	是
PRIMARY KEY	主鍵 = UNIQUE + NOT NULL	B-tree 索引	是
FOREIGN KEY	參考完整性	不自動建索引	是

建立語法與範例

1. NOT NULL

CREATE TABLE policies (
  policy_id   NUMBER,
  holder_name VARCHAR2(40) NOT NULL,
  gender      VARCHAR2(1),
  dob         DATE NOT NULL
);

-- ALTER 修改
ALTER TABLE policies MODIFY holder_name NOT NULL;

2. CHECK

CREATE TABLE policies (
  policy_id     NUMBER,
  gender        VARCHAR2(1) CONSTRAINT chk_gender CHECK (gender IN ('M','F')),
  marital_status VARCHAR2(1) CONSTRAINT chk_marital CHECK (marital_status IN ('S','M','D','W'))
);

-- 多欄 CHECK
ALTER TABLE policies ADD CONSTRAINT chk_dates 
  CHECK (coverage_end > coverage_begin);

限制：不可跨列、不可呼叫 SYSDATE/UID/USERENV、不支援 LOB/巢狀表/REF

3. UNIQUE

CREATE TABLE insured_autos (
  policy_id NUMBER,
  vin       VARCHAR2(10),
  CONSTRAINT uk_auto UNIQUE (policy_id, vin)
  USING INDEX TABLESPACE idx_ts STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)
);

複合唯一鍵需用表級約束
USING INDEX 可指定索引表空間/儲存參數

4. PRIMARY KEY

-- 單欄
CREATE TABLE policies (
  policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY
);

-- 複合主鍵
CREATE TABLE insured_autos (
  policy_id NUMBER,
  vin       VARCHAR2(40),
  CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id, vin)
  USING INDEX TABLESPACE idx_ts STORAGE (INITIAL 1M NEXT 10M)
);

5. FOREIGN KEY

CREATE TABLE insured_autos (
  policy_id NUMBER CONSTRAINT fk_policy REFERENCES policies(policy_id) ON DELETE CASCADE,
  make      VARCHAR2(30),
  model     VARCHAR2(30),
  year      NUMBER,
  CONSTRAINT fk_auto REFERENCES automobiles(make, model, year) ON DELETE SET NULL
);

ON DELETE 選項	行為
CASCADE	級聯刪除子列
SET NULL	子列外鍵設 NULL
無 (預設)	若有子列則禁止刪除父列

約束管理操作

操作	語法
啟用	`ALTER TABLE t ENABLE CONSTRAINT c;`
停用	`ALTER TABLE t DISABLE CONSTRAINT c;`
刪除	`ALTER TABLE t DROP CONSTRAINT c;`
主鍵專用	`ALTER TABLE t DROP PRIMARY KEY;` / `DISABLE PRIMARY KEY;`

注意：被外鍵參考的 UNIQUE/PK 不可直接刪除，需先處理外鍵。

效能陷阱與解法

問題：頻繁 DISABLE/ENABLE 導致索引重建

停用 UNIQUE/PK 會隱含刪除索引，啟用時重建，大表極慢。

解法：非唯一索引支援

先建非唯一索引：CREATE INDEX idx_policy_id ON policies(policy_id);
再加約束：ALTER TABLE policies ADD CONSTRAINT pk_policies PRIMARY KEY (policy_id) USING INDEX idx_policy_id;
停用約束時索引保留，啟用秒級完成

延遲約束檢查

-- Session 級設定
SET CONSTRAINT ALL DEFERRED;  -- 交易結束才檢查
SET CONSTRAINT ALL IMMEDIATE; -- 立即檢查 (預設)

-- 約束建立時指定
CREATE TABLE t (id NUMBER CONSTRAINT pk_t PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);

適用場景：批次匯入、多表同步、暫時違反約束的中介狀態。

序列替代自增

CREATE SEQUENCE seq_policy 
  START WITH 1 INCREMENT BY 1 
  MAXVALUE 9999999999 NOCYCLE CACHE 20;

-- 使用
INSERT INTO policies VALUES (seq_policy.NEXTVAL, 'John', 'M', 'S', SYSDATE);