当前位置： 首页 > article >正文

Oracle 深入学习 Part 13: Maintaining Data Integrity（数据完整性维护）

article 2025/1/23 14:33:52

数据完整性

数据完整性是指在数据的整个生命周期内，确保其准确性、一致性和可靠性的一种实践。

数据完整性维护的机制

维系数据完整性有三种方法：

1.Application Code（应用程序代码）：

  • 通过应用程序代码对数据进行验证和处理。

2.Database Trigger（数据库触发器）：

  • 通过触发器在数据库层自动验证数据或执行相关操作。

3.Integrity Constraint（完整性约束）（首选）：

  • 利用数据库内置的约束规则，静态地强制数据一致性。

数据库完整性约束（Constraints）的类型及其作用

约束类型描述示例
NOT NULL指定某列不能包含 NULL 值。用户表中的 username 字段不能为空。
UNIQUE确保某列或某些列的组合值是唯一的。用户表中的 email 字段必须唯一。
PRIMARY KEY指定某列或某些列的组合为表的主键（唯一 + 非空）。用户表中的 user_id 用作主键，唯一标识每个用户。
FOREIGN KEY确保某列的值必须与另一个表中引用列的值匹配。订单表中的 customer_id 字段必须对应客户表中的有效 id。
CHECK指定表中每一行数据必须满足的条件。工资表中的 salary 字段必须大于 0。
NOT NULL(非空)

确保列中不能插入空值（NULL），常用于需要强制填写的字段，例如主键、用户名、订单日期等。

  • 定义约束位置：

    • NOT NULL 约束是在列级别定义的。

  • 创建表时添加：

    • 在创建表时可以使用 CREATE TABLE 来定义约束.

  • 修改现有表的 NOT NULL 约束：

    • ALTER TABLE ORDERS MODIFY ORDER_DATE NULL;  -- 移除 NOT NULL 约束
ALTER TABLE ORDERS MODIFY PRODUCT_ID NOT NULL; -- 添加 NOT NULL 约束
CHECK 约束

CHECK 约束用于限制列的值必须满足某些规则。

  • 定义位置：

    • CHECK 约束可以定义在列级别或表级别。

  • 条件规则：

    • CHECK 子句中指定的条件必须返回布尔值（True 或 False）。

    • 条件可以引用同一行中其他列的值，但不能使用查询。

  • 限制：

    • 环境函数（如 SYSDATE、USER、USERENV、UID 等）以及伪列（如 ROWNUM、CURRVAL、NEXTVAL、LEVEL 等）不能用于评估 CHECK 条件。

  • 列限制：

    • 一个列可以定义多个 CHECK 约束。

    • 列上的值可以为 NULL。

  • 实例：

    • CREATE TABLE ORDERS (
    ORDER_NUM   NUMBER(4),
    ORDER_DATE  DATE CHECK (ORDER_DATE > SYSDATE),
    PRODUCT_ID  NUMBER CHECK (PRODUCT_ID > 0)
);

UNIQUE 约束

UNIQUE 约束用于确保一个列或一组列中的值是唯一的，防止重复数据。

  • 定义位置：

    • 如果是单列的 UNIQUE 键，可以在列级别定义约束。

    • 如果是多列组合（复合键）的 UNIQUE 键（最多支持 32 列），约束应在表级别定义。

  • 唯一索引：

    • Oracle 数据库会在 UNIQUE 键列上自动创建一个唯一索引，以强制执行唯一性。

    • 如果表中已经存在一个唯一索引或非唯一索引，并且索引中包含相同的列，Oracle 会使用现有的非唯一索引，但表中必须没有重复键。

  • 允许 NULL 值：

    • UNIQUE 约束允许列包含 NULL 值。

RIMARY KEY (主键)

  1. 与 UNIQUE 的异同：

    • 相同点：PRIMARY KEY 的所有特性都与 UNIQUE 约束一致。

    • 不同点：PRIMARY KEY 列不允许包含 NULL 值。

  2. 唯一性：

    • 一个表中只能有一个 PRIMARY KEY。

  3. 索引和约束：

    • Oracle 会为 PRIMARY KEY 的每个列自动创建一个唯一索引，并为这些列添加 NOT NULL 约束。

    • 如果表中已经存在一个索引，并且该索引包含了所有 PRIMARY KEY 列，Oracle 可以复用该索引。

  4. 存储参数：

    • PRIMARY KEY 定义时，可以为表和主键索引指定存储参数。

  5. 索引的管理：

    • 用于强制 UNIQUE 和 PRIMARY KEY 的索引与普通索引一样可以被管理。

    • 但这些索引不能显式删除。

FOREIGN KEY(外键)

外键是在创建约束时指定的表（子表）中的列或列组合；

  • 定义位置：

    • 列级别或表级别。在表级别定义多列外键。

  • 定义外键：

    • 外键用来建立表之间的关系，确保子表中的值必须出现在父表中。

    • 定义外键时，需要引用父表的 PRIMARY KEY 或 UNIQUE 列。

  • 允许 NULL 值：

    • 外键列中允许有空值（NULL）

  • 级联操作：

    • 外键可以设置级联操作，当父表记录被更新或删除时，决定子表记录的行为：

      • ON DELETE CASCADE：父表记录删除时，自动删除子表中对应的记录。

      • ON DELETE SET NULL：父表记录删除时，将子表中对应外键列的值设置为 NULL。

Cheating disabled constraints(创建禁止型约束)

约束有ENABLE (启用)和DISABLE (禁用)两个状态。

ENABLE (启用)

启用状态时，约束会对插入、更新、删除操作进行检查，确保数据符合约束条件。

DISABLE (禁用)

禁用状态时，约束不会对数据操作进行验证。

在创建约束时，直接在语句末尾加上 DISABLE 选项，就可以将该约束设置为禁用状态。

状态数据验证数据操作主要用途
ENABLE数据操作受约束插入/更新可能失败确保数据完整性，约束规则生效
DISABLE数据操作不验证任意数据都可操作暂时忽略约束，例如批量数据导入时关闭

删除约束

ALTER TABLE 表名 DROP 约束类型 约束名;

约束状态

对于未来数据(New data)：

  • ENABLE:启用约束，数据库会检查新插入或更新的数据是否符合约束条件。

  • DISABLE:禁用约束，数据库不会检查新插入或更新的数据是否符合约束条件。

对与已有数据(Existing data):

  • VALIDATE:验证已有数据是否符合约束条件。

    • 如果已有数据不符合约束条件，操作会失败。

    • 在此模式下，约束适用于新数据和旧数据。

  • NOVAIDATE:不验证已有数据是否符合约束条件。

    • 已有数据不会被检查，即使不符合约束条件也不会报错。

    • 仅对新插入或更新的数据启用约束。

状态组合对未来数据对已有数据描述
ENABLE VALIDATE检查检查启用约束，验证所有数据（包括现有数据和新数据）。如果现有数据不符合约束，则转换失败。
ENABLE NOVALIDATE检查不检查启用约束，仅验证新数据，现有数据不验证，即使不符合约束也保留。
DISABLE VALIDATE不检查检查禁用约束，不验证新数据，但要求现有数据必须符合约束。
DISABLE NOVALIDATE不检查不检查禁用约束，新数据和现有数据都不进行验证，约束完全失效。

当状态向ENABLE VALIDATE转换时：

  • ENABLE VALIDATE 会扫描整个表的数据，可能会耗费大量时间和资源。

  • 如果现有数据不符合约束条件，ENABLE VALIDATE 会失败。

约束检查(Constraint Checking)

在Oracle数据库中，执行DML（数据操纵语言）语句，如INSERT、UPDATE或DELETE时，数据库会对涉及的表进行约束检查。

约束检查流程

流程

  1. 执行 DML 语句。

  2. 检查非延迟约束：

    • 若成功，则继续下一步。

    • 若失败，则语句回滚并报错。

  3. 提交事务：

    • 检查所有延迟约束。

    • 若所有约束都通过，事务成功提交。

    • 若有任何延迟约束失败，事务回滚。

含义
  1. DML语句执行

    • 操作：当用户执行数据操作语句（如 INSERT、UPDATE 或 DELETE）时，会修改数据库中的数据。

    • 触发检查：DML 操作会触发与目标表相关的约束检查。

  2. 检查非递归/非延迟约束（Check Nondeferred Constraints）

    • 定义：非延迟约束是指在 DML 语句执行后立即进行检查的约束。

    • 特点：

      • 这些约束的验证是即时的。

      • 如果违反约束，语句会立即失败，数据不会被写入数据库。

    • 常见非延迟约束：

      • 主键约束（PRIMARY KEY）

      • 唯一约束（UNIQUE）

      • 非空约束（NOT NULL）

      • 检查约束（CHECK）

  3. 提交操作（COMMIT）

    • 操作：当事务完成后，用户执行 COMMIT 来保存更改。

    • 约束验证：

      • 如果事务中包含延迟检查的约束，这些约束会在 COMMIT 时统一检查。

      • 如果任何延迟约束失败，整个事务会回滚，所有更改都不会保存。

  4. 检查延迟约束（Check Deferred Constraints）

    • 定义：延迟约束是指在事务提交时才进行检查的约束。

    • 特点：

      • 延迟检查允许事务内的一些中间状态不符合约束规则，只要最终状态满足约束即可。

      • 默认情况下，约束是非延迟的，但可以通过声明或 SET CONSTRAINT 语句将其设置为延迟。

延迟检查（Deferred）和立即检查（Immediate）

使用 SET CONSTRAINTS 语句设置约束状态
SET CONSTRAINTS {约束名 | ALL} {DEFERRED | IMMEDIATE};

参数说明：

  • 约束名：指定要设置状态的约束名。

  • ALL：将当前事务中所有可延迟的约束设置为指定状态。

  • DEFERRED：将约束设置为延迟检查状态，在事务提交时检查。

  • IMMEDIATE：将约束设置为立即检查状态，在每条 DML 语句执行后检查。

使用 ALTER SESSION 设置约束状态
ALTER SESSION SET CONSTRAINTS = {DEFERRED | IMMEDIATE};

参数说明：

  • DEFERRED：会话中的所有事务默认使用延迟检查约束。

  • IMMEDIATE：会话中的所有事务默认使用立即检查约束。

两者的对比
方法范围优点
SET CONSTRAINTS仅作用于当前事务灵活，可以针对特定约束进行设置。
ALTER SESSION整个会话（当前用户的所有事务）适用于需要统一设置约束检查模式的场景。

PK和 UK 的 强制执行（Enforcement）


http://www.kler.cn/a/515010.html

相关文章：

  • docker Ubuntu实战
  • Android SystemUI——通知栏构建流程（十六）
  • 每日一题洛谷P1423 小玉在游泳c++
  • 实施工程师：面试基础宝典
  • windows git bash 使用zsh 并集成 oh my zsh
  • 【Qt 常用控件】显示类控件——QLabel
  • 如何使用 findIndex() 方法查找数组中的第一个匹配元素的索引？
  • 国产编辑器EverEdit - 快捷目录
  • 盲道人行道分割YOLOV8SEG
  • 期刊论文左下角添加通讯作者和横线的方法
  • GeoJSON 数据
  • 人源化抗体的改造方式及其优势【卡梅德生物】
  • 可以免费使用的电子杂志制作平台
  • OFD、PDF 电子签章系统处理流程
  • 大模型GUI系列论文阅读 DAY3：《GPT-4V(ision) is a Generalist Web Agent, if Grounded》
  • matlab绘图——彩色螺旋图
  • 数据结构——实验六·散列表
  • Android SystemUI——通知栏构建流程（十六）
  • GA-CNN-LSTM-Attention、CNN-LSTM-Attention、GA-CNN-LSTM、CNN-LSTM四模型多变量时序预测一键对比
  • Java菜鸟养成计划（java基础）--- java中的变量
  • C语言--数据在内存中的存储
  • Android中关于View的几种属性赋值方式
  • JVM面试题解，垃圾回收之“对象存活判断”剖析
  • Haskell语言的数据可视化
  • C++17 新特性深入解析：constexpr 扩展、if constexpr 和 constexpr lambda
  • adb 命令使用大全