Oracle 约束（Constraint）详解

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

---

1. 概述

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

类型	说明
NOT NULL	非空
UNIQUE	唯一
PRIMARY KEY	主键
FOREIGN KEY	外键
CHECK	检查
DEFAULT	默认值（非严格约束）

---

2. NOT NULL

CREATE TABLE employees (
  id NUMBER NOT NULL,
  name VARCHAR2(100) NOT NULL,
  email VARCHAR2(100)  -- 允许 NULL
);

-- 添加
ALTER TABLE employees MODIFY (email NOT NULL);

-- 删除
ALTER TABLE employees MODIFY (email NULL);

---

3. UNIQUE

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

-- 命名
CREATE TABLE employees (
  id NUMBER,
  email VARCHAR2(100),
  CONSTRAINT uk_emp_email UNIQUE (email)
);

-- 多列
CREATE TABLE employees (
  id NUMBER,
  dept_id NUMBER,
  email VARCHAR2(100),
  CONSTRAINT uk_emp_dept_email UNIQUE (dept_id, email)
);

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

---

4. PRIMARY KEY

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

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

-- 复合主键
CREATE TABLE emp_projects (
  emp_id NUMBER,
  project_id NUMBER,
  CONSTRAINT pk_emp_proj PRIMARY KEY (emp_id, project_id)
);

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

---

5. FOREIGN KEY

5.1 基本语法

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  dept_id NUMBER,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) 
    REFERENCES departments(id)
);

5.2 ON DELETE 选项

-- CASCADE：删除父记录时，自动删除子记录
CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) 
  REFERENCES departments(id) ON DELETE CASCADE;

-- SET NULL：删除父记录时，子记录外键设为 NULL
CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) 
  REFERENCES departments(id) ON DELETE SET NULL;

-- NO ACTION（默认）：禁止删除有子记录的父记录

5.3 索引

-- 外键列建议加索引（避免锁问题）
CREATE INDEX idx_emp_dept ON employees(dept_id);

---

6. CHECK

6.1 基本语法

CREATE TABLE employees (
  id NUMBER,
  salary NUMBER,
  age NUMBER,
  CONSTRAINT chk_salary CHECK (salary > 0),
  CONSTRAINT chk_age CHECK (age BETWEEN 18 AND 65)
);

6.2 复杂条件

CREATE TABLE orders (
  id NUMBER,
  status VARCHAR2(20),
  order_date DATE,
  ship_date DATE,
  CONSTRAINT chk_status CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED')),
  CONSTRAINT chk_dates CHECK (ship_date >= order_date)
);

6.3 限制

• 不能引用其他行
• 不能使用 SYSDATE/USER 等
• 不能使用子查询

---

7. DEFAULT

CREATE TABLE employees (
  id NUMBER,
  status VARCHAR2(20) DEFAULT 'ACTIVE',
  create_date DATE DEFAULT SYSDATE,
  salary NUMBER DEFAULT 0
);

-- 12c+：DEFAULT ON NULL
CREATE TABLE employees (
  id NUMBER,
  status VARCHAR2(20) DEFAULT ON NULL 'ACTIVE'
);
-- 插入 NULL 时使用默认值

-- 12c+：序列默认值
CREATE TABLE employees (
  id NUMBER DEFAULT seq_emp.NEXTVAL,
  ...
);

-- 12c+：IDENTITY
CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  ...
);

---

8. 约束状态

8.1 状态选项

状态	说明
ENABLE	启用（验证）
DISABLE	禁用
VALIDATE	验证已有数据
NOVALIDATE	不验证已有数据

8.2 组合

-- 启用且验证（默认）
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT fk_emp_dept;

-- 启用但不验证（已有数据不检查）
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT fk_emp_dept;

-- 禁用
ALTER TABLE employees DISABLE CONSTRAINT fk_emp_dept;

8.3 应用场景

• 数据加载：DISABLE → 加载 → ENABLE
• 历史数据：ENABLE NOVALIDATE

---

9. DEFERRABLE 约束

9.1 延迟检查

-- 创建 DEFERRABLE
CREATE TABLE employees (
  id NUMBER,
  dept_id NUMBER,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) 
    REFERENCES departments(id) 
    DEFERRABLE INITIALLY DEFERRED
);

-- 事务中设置
SET CONSTRAINTS fk_emp_dept DEFERRED;
INSERT INTO employees VALUES (1, 999);  -- 部门 999 暂不存在
INSERT INTO departments VALUES (999, 'New Dept');
COMMIT;  -- 提交时检查

9.2 INITIALLY 选项

• INITIALLY IMMEDIATE：默认立即检查
• INITIALLY DEFERRED：默认延迟检查

---

10. 约束管理

10.1 查看

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

10.2 查看列

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

10.3 删除

ALTER TABLE employees DROP CONSTRAINT fk_emp_dept;

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

10.4 重命名

ALTER TABLE employees RENAME CONSTRAINT fk_emp_dept TO fk_emp_department;

---

11. 异常处理

11.1 查看违反

-- 创建 EXCEPTIONS 表
@?/rdbms/admin/utlexcpt.sql

-- 启用约束并记录异常
ALTER TABLE employees ENABLE CONSTRAINT chk_salary EXCEPTIONS INTO exceptions;

-- 查看违反
SELECT * FROM exceptions;

11.2 处理违反

-- 查找违反数据
SELECT * FROM employees 
WHERE rowid IN (SELECT row_id FROM exceptions);

-- 修复数据
UPDATE employees SET salary = 0 WHERE ...;

-- 重新启用
ALTER TABLE employees ENABLE CONSTRAINT chk_salary;

---

12. 常见坑与排错

12.1 ORA-00001: 唯一约束冲突

-- 检查重复值
SELECT email, COUNT(*) FROM employees GROUP BY email HAVING COUNT(*) > 1;

-- 删除重复
DELETE FROM employees WHERE rowid NOT IN (
  SELECT MIN(rowid) FROM employees GROUP BY email
);

12.2 ORA-02292: 子记录存在

-- 不能删除有子记录的父记录
-- 1. 先删除子记录
-- 2. 或 ON DELETE CASCADE
-- 3. 或 ON DELETE SET NULL

12.3 ORA-02291: 父键不存在

-- 外键值在父表中不存在
-- 先插入父记录

12.4 ORA-02290: CHECK 约束违反

-- 检查数据是否符合条件
-- 修改数据或约束

12.5 性能问题

-- 外键无索引导致锁
-- 加索引
CREATE INDEX idx_emp_dept ON employees(dept_id);

---

13. 最佳实践

1. 主键必有：每表
2. 外键加索引：避免锁
3. CHECK 数据完整性：业务规则
4. DEFAULT 减少空值：易用
5. 命名规范：uk_/pk_/fk_/chk_
6. 批量加载先禁用：性能
7. DEFERRABLE 复杂场景：灵活
8. EXCEPTIONS 分析：定位问题
9. ENABLE NOVALIDATE 历史数据：兼容
10. 定期检查：数据完整性

---

14. 参考资料

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