【数据库】【Oracle】事务与约束详解

Oracle 事务与约束详解

事务和约束是 Oracle 数据库数据完整性和一致性的两大基石。事务确保操作的原子性，约束确保数据的业务规则。

一、事务（Transaction）基础

1.1 ACID 特性

1.2 事务生命周期

-- 1. 开始事务（Oracle 自动开始）UPDATEemployeesSETsalary=8000WHEREemployee_id=101;-- 2. 执行 DML 操作INSERTINTOdepartmentsVALUES(280,'Cloud Engineering',1700);-- 3. 保存点（可选）SAVEPOINTsp1;-- 4. 继续操作DELETEFROMemployeesWHEREemployee_id=999;-- 5. 回滚到保存点ROLLBACKTOsp1;-- 仅撤销 DELETE-- 6. 提交事务COMMIT;-- 所有更改永久生效-- 或回滚整个事务ROLLBACK;-- 撤销所有更改

二、Oracle 事务控制语句

2.1 显式控制

-- COMMIT：提交事务COMMIT[WORK][COMMENT'注释'];-- COMMENT 用于分布式事务-- ROLLBACK：回滚事务ROLLBACK[WORK];ROLLBACK[WORK]TO[SAVEPOINT]保存点名称;-- 部分回滚-- SAVEPOINT：设置保存点SAVEPOINT保存点名称;-- SET TRANSACTION：设置事务属性SETTRANSACTIONREADONLY;-- 只读事务SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;-- 设置隔离级别SETTRANSACTIONNAME'订单处理';-- 命名事务

2.2 隐式提交

以下操作会自动提交当前事务：

• DDL 语句：CREATE,ALTER,DROP,TRUNCATE
• DCL 语句：GRANT,REVOKE
• 退出 SQL*Plus（正常退出）

陷阱示例：

UPDATEemployeesSETsalary=9000WHEREemployee_id=101;CREATETABLEtemp_table(id NUMBER);-- 自动提交 UPDATE！ROLLBACK;-- 已无法回滚 salary 的修改

2.3 自治事务

在触发器或存储过程中独立提交，不影响主事务。

CREATEORREPLACEPROCEDURElog_error(err_msg VARCHAR2)ISPRAGMA AUTONOMOUS_TRANSACTION;-- 声明自治事务BEGININSERTINTOerror_log(message,log_date)VALUES(err_msg,SYSDATE);COMMIT;-- 独立提交END;/

三、约束（Constraint）类型

3.1 约束分类总览

CREATETABLEemployees(employee_id NUMBER(6)CONSTRAINTemp_pkPRIMARYKEY,-- 主键约束email VARCHAR2(25)CONSTRAINTemp_email_ukUNIQUE,-- 唯一约束first_name VARCHAR2(20)NOTNULL,-- 非空约束last_name VARCHAR2(25)NOTNULL,salary NUMBER(8,2)CONSTRAINTemp_salary_ckCHECK(salary>0),-- 检查约束hire_dateDATEDEFAULTSYSDATE,department_id NUMBER(4)CONSTRAINTemp_dept_fkREFERENCESdepartments(department_id)-- 外键约束);

3.2 主键约束（PRIMARY KEY）

作用：唯一标识一行，非空且唯一

-- 建表时创建CREATETABLEproducts(product_id NUMBERPRIMARYKEY,name VARCHAR2(100));-- 表级约束（推荐，可命名）CREATETABLEproducts(product_id NUMBER,name VARCHAR2(100),CONSTRAINTprod_pkPRIMARYKEY(product_id));-- 组合主键CREATETABLEorder_items(order_id NUMBER,item_id NUMBER,quantity NUMBER,CONSTRAINTpk_order_itemsPRIMARYKEY(order_id,item_id));-- 修改表添加主键ALTERTABLEproductsADDCONSTRAINTprod_pkPRIMARYKEY(product_id);-- 禁用主键（数据仓库批量加载时）ALTERTABLEproductsDISABLECONSTRAINTprod_pk;ALTERTABLEproductsENABLECONSTRAINTprod_pk;

3.3 外键约束（FOREIGN KEY）

作用：维护表间引用完整性

-- 基础外键CREATETABLEemployees(employee_id NUMBERPRIMARYKEY,department_id NUMBERCONSTRAINTemp_dept_fkREFERENCESdepartments(department_id));-- 级联删除ALTERTABLEemployeesADDCONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id)ONDELETECASCADE;-- 删除部门时，员工自动删除-- 级联置空ALTERTABLEemployeesADDCONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id)ONDELETESETNULL;-- 删除部门时，员工 dept_id 设为 NULL-- 外键限制-外键列必须是主键或唯一键-外键列类型必须与引用列一致-外键可以引用同一表（自引用）

外键检查时机：

-- IMMEDIATE（默认）：每行 DML 后立即检查-- DEFERRED：事务提交时检查（用于循环依赖）ALTERTABLEemployeesADDCONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id)DEFERRABLE INITIALLY DEFERRED;-- 可延迟约束

3.4 唯一约束（UNIQUE）

作用：确保列值唯一，但允许多个 NULL

-- 列级唯一约束CREATETABLEusers(user_id NUMBERPRIMARYKEY,email VARCHAR2(100)UNIQUE);-- 表级唯一约束（可命名）CREATETABLEusers(user_id NUMBERPRIMARYKEY,email VARCHAR2(100),CONSTRAINTusers_email_ukUNIQUE(email));-- 组合唯一约束CREATETABLEuser_roles(user_id NUMBER,role_id NUMBER,CONSTRAINTuk_user_rolesUNIQUE(user_id,role_id));

3.5 非空约束（NOT NULL）

作用：列不能为 NULL

-- 建表时定义CREATETABLEproducts(product_id NUMBERNOTNULL,name VARCHAR2(100)NOTNULL);-- 修改表添加非空约束ALTERTABLEproductsMODIFY(nameNOTNULL);-- 删除非空约束（通过 MODIFY）ALTERTABLEproductsMODIFY(nameNULL);

3.6 检查约束（CHECK）

作用：强制列值满足条件

-- 检查 salary 范围CREATETABLEemployees(employee_id NUMBERPRIMARYKEY,salary NUMBER(8,2)CHECK(salaryBETWEEN1000AND50000));-- 复杂检查约束CREATETABLEemployees(employee_id NUMBERPRIMARYKEY,salary NUMBER(8,2),commission_pct NUMBER(2,2),CONSTRAINTchk_salary_commissionCHECK((salary>0ANDcommission_pctISNULL)OR(salary>5000ANDcommission_pctBETWEEN0AND0.5)));-- 修改表添加检查约束ALTERTABLEemployeesADDCONSTRAINTchk_hire_dateCHECK(hire_date<=SYSDATE);

3.7 默认值（DEFAULT）

作用：插入时未指定值则使用默认值

CREATETABLEorders(order_id NUMBERPRIMARYKEY,order_dateDATEDEFAULTSYSDATE,-- 当前日期statusVARCHAR2(20)DEFAULT'NEW',created_by VARCHAR2(50)DEFAULTUSER-- 当前用户);-- 插入时使用默认值INSERTINTOorders(order_id)VALUES(101);-- order_date=SYSDATE, status='NEW'

四、约束状态管理

4.1 约束状态组合

每个约束有四种状态组合：

• ENABLE VALIDATE：默认状态，启用且验证现有数据（最严格）
• ENABLE NOVALIDATE：启用但不验证现有数据（仅对新数据生效）
• DISABLE VALIDATE：禁用但验证现有数据（阻止新DML）
• DISABLE NOVALIDATE：禁用且不验证（数据仓库场景）

-- 查看约束状态SELECTconstraint_name,status,validatedFROMuser_constraintsWHEREtable_name='EMPLOYEES';-- 禁用约束（数据加载时提升性能）ALTERTABLEemployeesDISABLECONSTRAINTemp_salary_ck;-- 启用并验证（加载后重新启用）ALTERTABLEemployeesENABLEVALIDATECONSTRAINTemp_salary_ck;-- 启用但不验证（存在脏数据时）ALTERTABLEemployeesENABLENOVALIDATECONSTRAINTemp_salary_ck;-- 延迟约束检查（事务级）ALTERTABLEemployeesMODIFYCONSTRAINTemp_dept_fk DEFERRABLE INITIALLY IMMEDIATE;-- 在事务中临时延迟SETCONSTRAINTemp_dept_fk DEFERRED;INSERTINTOemployees(...)VALUES(...);-- 暂时违反外键UPDATEdepartmentsSETdepartment_id=...;-- 使引用有效COMMIT;-- 提交时检查

五、事务与约束的交互

5.1 约束检查时机

IMMEDIATE 约束（默认）：

-- 每行 DML 后立即检查UPDATEemployeesSETdepartment_id=999WHEREemployee_id=101;-- 报错：ORA-02291: 违反外键约束

DEFERRED 约束：

-- 事务提交时检查SETCONSTRAINTemp_dept_fk DEFERRED;INSERTINTOemployees(employee_id,department_id)VALUES(999,999);-- 成功-- 在提交前插入被引用的部门INSERTINTOdepartments(department_id)VALUES(999);COMMIT;-- 检查通过，成功提交

5.2 违反约束的事务回滚

BEGININSERTINTOemployees(employee_id,salary)VALUES(999,-100);-- 违反检查约束EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('违反约束: '||SQLERRM);ROLLBACK;-- 回滚整个事务END;/

5.3 约束与性能

-- 批量插入时禁用约束可提升性能ALTERTABLEemployeesDISABLECONSTRAINTemp_salary_ck;-- 批量插入INSERTINTOemployeesSELECT*FROMemployees_staging;-- 启用并验证ALTERTABLEemployeesENABLEVALIDATECONSTRAINTemp_salary_ck;

六、高级主题

6.1 约束异常处理

-- 创建异常表EXECDBMS_ERRLOG.CREATE_ERROR_LOG('EMPLOYEES','EMPLOYEES_ERRLOG');-- 批量插入时记录异常INSERTINTOemployeesSELECT*FROMemployees_temp LOGERRORSINTOEMPLOYEES_ERRLOG('Batch Load')REJECTLIMITUNLIMITED;-- 查看异常记录SELECTora_err_number$,ora_err_mesg$,first_nameFROMEMPLOYEES_ERRLOG;

6.2 视图上的约束

-- WITH CHECK OPTION：防止通过视图插入不符合视图条件的数据CREATEVIEWhigh_empASSELECT*FROMemployeesWHEREsalary>10000WITHCHECKOPTION;-- 尝试插入 low salary 将失败INSERTINTOhigh_empVALUES(...,5000);-- ORA-01402: 违反 CHECK OPTION

6.3 物化视图约束

-- 物化视图可添加约束CREATEMATERIALIZEDVIEWemp_mv BUILD IMMEDIATE REFRESH FASTONCOMMITENABLEQUERY REWRITEASSELECTemployee_id,salaryFROMemployees;-- 添加主键约束ALTERMATERIALIZEDVIEWemp_mvADDCONSTRAINTemp_mv_pkPRIMARYKEY(employee_id);

七、最佳实践与避坑指南

7.1 约束设计最佳实践

✅命名规范：表名_列名_约束类型（如emp_salary_ck）
✅尽早定义：建表时定义约束，避免后期数据污染
✅优先使用 NOT NULL：约束开销最小，性能影响最低
✅外键加索引：外键列必须创建索引，避免全表锁
✅检查约束简单：避免复杂函数，影响 DML 性能
✅文档化约束：记录业务含义，便于维护

7.2 常见陷阱

❌外键无索引：导致子表全表锁，并发性能差
❌循环外键：A→B→C→A，设计缺陷
❌延迟约束滥用：增加复杂性，难以调试
❌CHECK 中使用函数：导致无法创建函数索引
❌大批量 DML 不禁用约束：性能慢且产生大量 UNDO

7.3 外键索引示例

-- 外键列必须创建索引CREATETABLEorder_items(order_id NUMBER,item_id NUMBER,CONSTRAINTfk_order_itemsFOREIGNKEY(order_id)REFERENCESorders(order_id));-- 创建索引（否则删除父表记录时会锁整个子表）CREATEINDEXidx_order_items_order_idONorder_items(order_id);

八、查询与维护

8.1 查询约束信息

-- 查询表的所有约束SELECTconstraint_name,constraint_type,search_condition,status,validatedFROMuser_constraintsWHEREtable_name='EMPLOYEES'ORDERBYconstraint_type;-- 约束类型说明：-- P: Primary Key, U: Unique, R: Foreign Key, C: Check-- 查询外键引用关系SELECTa.constraint_name,a.table_name,b.column_name,a.r_constraint_name,c.table_nameASref_table,d.column_nameASref_columnFROMuser_constraints aJOINuser_cons_columns bONa.constraint_name=b.constraint_nameJOINuser_constraints cONa.r_constraint_name=c.constraint_nameJOINuser_cons_columns dONc.constraint_name=d.constraint_nameWHEREa.constraint_type='R'ANDa.table_name='EMPLOYEES';

8.2 批量操作约束

-- 导出约束定义（数据迁移时使用）SELECTDBMS_METADATA.GET_DDL('CONSTRAINT',constraint_name)FROMuser_constraintsWHEREtable_name='EMPLOYEES';-- 禁用表的所有约束BEGINFORcIN(SELECTconstraint_nameFROMuser_constraintsWHEREtable_name='EMPLOYEES')LOOPEXECUTEIMMEDIATE'ALTER TABLE employees DISABLE CONSTRAINT '||c.constraint_name;ENDLOOP;END;/

九、总结

事务 vs 约束对比

设计原则

• 事务要短：避免长事务占用资源
• 约束要早：建表时定义，数据入库前校验
• 外键要索引：避免性能陷阱
• 延迟要谨慎：仅用于循环依赖场景

掌握事务和约束，是构建健壮、可靠、高性能Oracle 应用的基石。