Oracle 约束管理

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

1. 概述

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

类型：

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

2. NOT NULL

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

-- 修改
ALTER TABLE employees MODIFY (salary NOT NULL);
ALTER TABLE employees MODIFY (salary NULL);

3. UNIQUE

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

-- 复合
CREATE TABLE employees (
  dept_id NUMBER,
  emp_no NUMBER,
  CONSTRAINT uk_emp UNIQUE (dept_id, emp_no)
);

-- 添加
ALTER TABLE employees ADD CONSTRAINT uk_email UNIQUE (email);

4. PRIMARY KEY

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100)
);

-- 命名
CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(100),
  CONSTRAINT pk_emp PRIMARY KEY (id)
);

-- 添加
ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (id);

5. FOREIGN KEY

5.1 创建

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

5.2 ON DELETE

-- CASCADE：级联删除
CONSTRAINT fk_... FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE

-- SET NULL：置空
CONSTRAINT fk_... FOREIGN KEY ... REFERENCES ... ON DELETE SET NULL

5.3 索引

-- FK 建议加索引
CREATE INDEX idx_orders_emp ON orders(emp_id);

6. CHECK

6.1 简单

CREATE TABLE employees (
  salary NUMBER CHECK (salary > 0),
  age NUMBER CHECK (age >= 18 AND age <= 65)
);

6.2 命名

CREATE TABLE employees (
  salary NUMBER,
  CONSTRAINT ck_salary CHECK (salary > 0)
);

6.3 复杂

CREATE TABLE employees (
  hire_date DATE,
  terminate_date DATE,
  CONSTRAINT ck_dates CHECK (terminate_date IS NULL OR terminate_date > hire_date)
);

7. 约束状态

7.1 ENABLE / DISABLE

ALTER TABLE employees DISABLE CONSTRAINT uk_email;
ALTER TABLE employees ENABLE CONSTRAINT uk_email;

7.2 VALIDATE / NOVALIDATE

-- 启用，但不验证已有数据
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT uk_email;

-- 启用，验证
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT uk_email;

7.3 RELY / NORELY

-- 查询重写依赖
ALTER TABLE employees MODIFY CONSTRAINT uk_email RELY;

7.4 组合

状态	含义
ENABLE VALIDATE	启用，验证
ENABLE NOVALIDATE	启用，不验证
DISABLE VALIDATE	禁用，但优化器考虑
DISABLE NOVALIDATE	禁用，不考虑

8. DEFERRABLE

8.1 创建

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

8.2 事务级

SET CONSTRAINTS ALL DEFERRED;
INSERT ...
COMMIT;  -- 提交时验证

9. 约束管理

9.1 查看

SELECT 
  constraint_name, constraint_type, 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 违反

-- 查违反数据
EXCEPTIONS INTO exceptions;
-- 或
SET CONSTRAINTS ALL IMMEDIATE;

10. 数据导入优化

10.1 禁用约束

-- 批量加载前
ALTER TABLE employees DISABLE CONSTRAINT ck_salary;
ALTER TABLE employees DISABLE CONSTRAINT uk_email;
-- 加载
-- 启用
ALTER TABLE employees ENABLE CONSTRAINT ck_salary;
ALTER TABLE employees ENABLE CONSTRAINT uk_email;

10.2 NOVALIDATE

-- 已知数据正确，不验证
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT uk_email;

11. 异常处理

11.1 EXCEPTIONS 表

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

11.2 查找违反

ALTER TABLE employees ENABLE VALIDATE CONSTRAINT uk_email 
  EXCEPTIONS INTO exceptions;

SELECT * FROM exceptions;
SELECT * FROM employees WHERE rowid IN (SELECT row_id FROM exceptions);

11.3 修复

-- 修改违反数据
UPDATE employees SET email = ... WHERE rowid = ...;

12. 约束与性能

12.1 验证开销

- ENABLE VALIDATE：慢
- ENABLE NOVALIDATE：快

12.2 FK 索引

- FK 无索引：DELETE 父表全锁
- FK 有索引：行锁

12.3 NOT NULL

- NOT NULL 优化器考虑
- 提升查询

13. 常见坑与排错

13.1 ORA-02292

-- 违反 FK
-- 子记录存在
-- ON DELETE CASCADE 或先删子

13.2 ORA-02291

-- 违反 FK
-- 父记录不存在
-- 先插父

13.3 ORA-00001

-- 违反 UNIQUE
-- 检查数据

13.4 ORA-02290

-- 违反 CHECK
-- 检查数据

14. 最佳实践

命名约束：可维护
PK 必备：标识
FK 加索引：性能
NOT NULL 业务：完整
CHECK 业务：完整
DEFERRABLE：复杂事务
批量加载禁用：性能
EXCEPTIONS：诊断
监控违反：及时
文档化：业务规则

15. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Constraints” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/constraints.html