Oracle 约束管理详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

约束保证数据完整性[1]：

详细见：Oracle 约束管理。

---

2. 约束类型

2.1 NOT NULL

CREATE TABLE employees (
  id NUMBER NOT NULL,
  name VARCHAR2(100) NOT NULL
);

ALTER TABLE employees MODIFY name NOT NULL;

2.2 UNIQUE

CREATE TABLE employees (
  id NUMBER,
  email VARCHAR2(100) UNIQUE
);

-- 复合
CREATE TABLE t (
  a NUMBER,
  b NUMBER,
  CONSTRAINT uk_ab UNIQUE (a, b)
);

2.3 PRIMARY KEY

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  ...
);

-- 复合
CREATE TABLE order_items (
  order_id NUMBER,
  item_id NUMBER,
  CONSTRAINT pk_oi PRIMARY KEY (order_id, item_id)
);

2.4 FOREIGN KEY

CREATE TABLE orders (
  id NUMBER PRIMARY KEY,
  emp_id NUMBER,
  CONSTRAINT fk_order_emp FOREIGN KEY (emp_id) REFERENCES employees(id)
);

-- ON DELETE
CONSTRAINT fk_order_emp FOREIGN KEY (emp_id) REFERENCES employees(id) ON DELETE CASCADE
CONSTRAINT fk_order_emp FOREIGN KEY (emp_id) REFERENCES employees(id) ON DELETE SET NULL

2.5 CHECK

CREATE TABLE employees (
  id NUMBER,
  salary NUMBER CHECK (salary > 0),
  age NUMBER CHECK (age >= 18 AND age <= 65),
  gender CHAR(1) CHECK (gender IN ('M', 'F'))
);

-- 23c+ Domain
CREATE DOMAIN salary_domain AS NUMBER 
  CONSTRAINT sal_check CHECK (salary_domain > 0);

2.6 DEFAULT

CREATE TABLE employees (
  id NUMBER,
  status VARCHAR2(20) DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

2.7 IDENTITY（12c+）

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

详细见：Oracle 序列与自增列详解。

---

3. 约束状态

3.1 状态

- ENABLE VALIDATE（默认）
- ENABLE NOVALIDATE
- DISABLE VALIDATE
- DISABLE NOVALIDATE

3.2 操作

-- 启用验证
ALTER TABLE employees ENABLE CONSTRAINT ck_salary;

-- 启用不验证（数据已加载）
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT ck_salary;

-- 禁用
ALTER TABLE employees DISABLE CONSTRAINT ck_salary;

-- 验证
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT ck_salary;

3.3 场景

状态	用途
ENABLE VALIDATE	新数据 + 旧数据
ENABLE NOVALIDATE	仅新数据
DISABLE VALIDATE	仅约束（数据加载）
DISABLE NOVALIDATE	无约束

---

4. 命名

4.1 规范

- PK_表：主键
- UK_表_列：唯一
- FK_子_父：外键
- CK_表_列：检查
- NN_表_列：非空

4.2 示例

CREATE TABLE employees (
  id NUMBER,
  email VARCHAR2(100),
  dept_id NUMBER,
  salary NUMBER,
  
  CONSTRAINT pk_employees PRIMARY KEY (id),
  CONSTRAINT uk_employees_email UNIQUE (email),
  CONSTRAINT fk_employees_dept FOREIGN KEY (dept_id) REFERENCES departments(id),
  CONSTRAINT ck_employees_salary CHECK (salary > 0)
);

---

5. 延迟约束

5.1 DEFERRABLE

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  mgr_id NUMBER REFERENCES employees(id) DEFERRABLE INITIALLY DEFERRED
);

-- 或
ALTER TABLE employees ADD CONSTRAINT fk_emp_mgr 
  FOREIGN KEY (mgr_id) REFERENCES employees(id) 
  DEFERRABLE INITIALLY DEFERRED;

5.2 立即/延迟

-- 会话级
SET CONSTRAINT fk_emp_mgr DEFERRED;
SET CONSTRAINT fk_emp_mgr IMMEDIATE;
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS ALL IMMEDIATE;

-- 提交时检查
COMMIT;  -- 验证

5.3 场景

- 循环外键
- 批量操作
- 复杂事务

---

6. 索引

6.1 自动索引

- PRIMARY KEY：自动唯一索引
- UNIQUE：自动唯一索引

6.2 外键

-- FK 应该索引
CREATE INDEX idx_emp_dept ON employees(dept_id);

-- 不索引可能导致：
- 锁
- 性能

---

7. 数据加载

7.1 禁用约束

-- 大批量加载
ALTER TABLE employees DISABLE CONSTRAINT ck_salary;
ALTER TABLE employees DISABLE CONSTRAINT fk_emp_dept;

-- 加载数据
INSERT INTO employees ...;

-- 重新启用
ALTER TABLE employees ENABLE CONSTRAINT ck_salary;
ALTER TABLE employees ENABLE CONSTRAINT fk_emp_dept;

7.2 NOVALIDATE

ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT ck_salary;
-- 不检查历史数据

---

8. 异常处理

8.1 EXCEPTIONS 表

-- 创建
@?/rdbms/admin/utlexpt1.sql

-- 验证
ALTER TABLE employees ENABLE CONSTRAINT ck_salary EXCEPTIONS INTO exceptions;

-- 查看
SELECT * FROM exceptions;
-- row_id, owner, table_name, constraint

8.2 修复

SELECT row_id, ... FROM employees WHERE ROWID IN (SELECT row_id FROM exceptions);
-- 修复数据

---

9. 查看

9.1 约束

SELECT constraint_name, constraint_type, table_name, status, deferrable, deferred
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

9.2 列

SELECT constraint_name, column_name, position
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';

9.3 类型

- C：CHECK / NOT NULL
- P：PRIMARY KEY
- U：UNIQUE
- R：FOREIGN KEY（Referential）
- V：WITH CHECK OPTION（视图）
- O：WITH READ ONLY（视图）

---

10. 删除

ALTER TABLE employees DROP CONSTRAINT ck_salary;
ALTER TABLE employees DROP PRIMARY KEY;
ALTER TABLE employees DROP UNIQUE (email);

-- 级联
ALTER TABLE employees DROP PRIMARY KEY CASCADE;

---

11. 重命名

ALTER TABLE employees RENAME CONSTRAINT ck_salary TO ck_emp_salary;

---

12. 依赖

12.1 视图

SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE referenced_name = 'EMPLOYEES';

12.2 级联

- DROP TABLE CASCADE CONSTRAINTS
- 删除表 + 约束

---

13. 性能

13.1 DML 开销

- 约束检查
- 适当
- 平衡

13.2 加载优化

- DISABLE + LOAD + ENABLE
- NOVALIDATE
- 批量

13.3 索引

- PK/UK：自动索引
- FK：手动索引

---

14. 常见坑与排错

14.1 ORA-02292

- FK 违反（子记录存在）
- 删除子记录或 CASCADE

14.2 ORA-02291

- FK 违反（父不存在）
- 插入父记录

14.3 ORA-00001

- UK 违反
- 检查数据

14.4 ORA-02290

- CHECK 违反
- 数据值

---

15. 最佳实践

1. 命名规范：清晰
2. PK 必须：完整性
3. FK 索引：性能
4. CHECK：业务规则
5. DEFERRABLE：复杂
6. 数据加载 DISABLE：性能
7. EXCEPTIONS：诊断
8. NOVALIDATE 谨慎：质量
9. 测试：验证
10. 文档化：设计

---

16. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “Constraints” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/