使用脚本确保Oracle中的数据完整性

我认为您可以通过使用一个物化视图来解决这个问题,该视图是主表ID上表A、表B和表C+Grob的联合。您必须创建一个物化视图日志,使其成为一个快速可刷新的物化视图。您还添加了一个检查约束,当每个主表ID的物化视图中有多行时,该约束会抛出一个错误。

Rob van Wijk在这里解释 http://rwijk.blogspot.com/2009/07/fast-refreshable-materialized-view.html 很多关于快速可恢复的MV。Rob van Wijk也经常出现在StackOverflow。

在这里,您可以阅读对物化视图使用检查约束的内容: http://technology.amis.nl/blog/475/introducing-materialized-views-as-mechanism-for-business-rule-implementation-complex-declarative-constraints

使用fast refresizable mv意味着完整性检查是在提交期间完成的,而不是在插入或更新数据期间。

我很累,我不能自己测试它,也不能提供一个真正的例子。

edit1:下面是示例:

当您创建一个具有检查约束和基于函数的唯一索引的快速刷新mv时,它就会工作。

首先,我们创建表:

SQL> create table mastertable (id number(10) not null primary key);

SQL> create table tablea
(id number(10) not null primary key
, master_id number(10) not null references mastertable (id));

SQL> create table tableb
(id number(10) not null primary key
, master_id number(10) not null references mastertable (id));

SQL> create table tablec
(id number(10) not null primary key
, master_id number(10) not null references mastertable (id));

然后我们创建mv日志:

SQL> create materialized view log on tablea with rowid (master_id) 
     including new values;

SQL> create materialized view log on tableb with rowid (master_id) 
     including new values;

SQL> create materialized view log on tablec with rowid (master_id) 
     including new values;

mv(真的需要umarker列!):

SQL> create materialized view table_abc
     refresh fast with rowid on commit
     as
     select master_id,count(*) master_count, 'A' umarker
     from   tablea
     group by master_id
     union all
     select master_id,count(*) master_count, 'B' umarker
     from   tableb
     group by master_id
     union all
     select master_id,count(*) master_count, 'C' umarker
     from   tablec
     group by master_id
     /

现在,我们为这个mv添加了一个检查约束,以确保不能在每个master_id的同一详细信息表中插入两次:

SQL> alter table table_abc add check (master_count in (0,1) );

我们为这个mv添加了一个唯一的基于函数的索引,以确保您不能用相同的主目录ID插入表A和表B:

SQL> create unique index table_abc_ufbi1 on table_abc
     (case when master_count = 1 then master_id else null end);

测试1(快乐之路):

SQL>插入主表值(1);

1 RIJ为Aangemaakt。

SQL>插入表A值(1,1);

1 RIJ为Aangemaakt。

SQL & GT;提交;

承诺是自愿的。

测试2(在表A中插入一个,在表B中插入一个,具有相同的主\u id)

SQL>插入主表值(2);

1 RIJ为Aangemaakt。

SQL>插入表A值(2,2);

1 RIJ为Aangemaakt。

SQL>插入到表B值中(3,2);

1 RIJ为Aangemaakt。

SQL & GT;提交; 犯罪 * 第1段中的FOUT: .ora-12008:fout in pad voor vernieuwen van快照。 ORA-00001:schending van unique beperking(testt.table_abc_ufbi1)。

测试3(在表A中插入两次,主控形状相同)

SQL>插入主表值(3);

1 RIJ为Aangemaakt。

SQL>插入表A值(4,3);

1 RIJ为Aangemaakt。

SQL>插入表A值(5,3);

1 RIJ为Aangemaakt。

SQL & GT;提交; 犯罪 * 第1段中的FOUT: .ora-12008:fout in pad voor vernieuwen van快照。 ORA-02290:检查BEPerking(testt.sys_C0015406)是否为Geschonden。

假设您的“主”表称为tablea,它的主键称为“id”。用名为“id”的主键创建第二个表,比如tableb。现在将表B(ID)定义为表A(A)的外键。

将表B(ID)作为外键意味着它只有在表A(ID)中存在时才具有值,而将其作为主键意味着它不能有一个以上的值。

如果数据只被您自己的存储过程修改过,那么我就不必检查这个约束了。

事实上,现在我想到了,没有必要签入insert case。你要插入一个样本和一个 CONCRETE_1 同一事务中的行。不能有一个 CONCRETE_2 与以前不存在的示例行具有相同pk的行。

不能依赖触发器来强制实现完整性。

Tom Kyte解释了原因: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:599808600346047256

问题是事务无法查看其他未限制的事务正在执行的操作。

举个简单的例子,事务A和事务B都可以将相同的值“x”插入到表中,并且都可以检查这样的值是否已经存在。事务A提交 之后 事务B检查“X”的现有值。事务B找不到X,因此它也插入自己的X并提交。现在唯一性要求被否决了。

避免这种情况的唯一方法是在检查现有值、插入该值以及提交要序列化的插入的整个过程中。

另外一个问题是,触发器无法看到它所触发的表的内容,因为它正在变化…

我认为在这种特殊的情况下,更改您的数据库模型,而不是创建脚本或触发器,就是答案。

你在之前的评论中提到:

好吧,我有table,然后是table,table,和table,table。table中的每个条目在table,table,table,或者table,table中都必须只有一个条目。a,b,c上的fk约束只做了一半。表A和表B可以有相同的父级(我不想要)。

我建议这样做:

1. 创建一个 ISABC 列在 表 它将类型指定为a、b或c。更好的做法是,使用a的pk(主键)和新列创建一个新表 ISABC (具有相同的pk和fk PK )
2. 设置(PK) ISABC )作为唯一的约束。
3. 添加(PK) ISABC 列到 表A , 表B 和 表列 . 使其成为对中相同列的FK(外键)约束 表 (或新桌子)。
4. 在每一个 表A , 表B 和 表列 ,在列上设置检查约束 ISABC 检查值分别为“A”、“B”和“C”。

这种设计增加了 ISABC 列在 表A , 表B 和 表列 作为强制执行这个约束的代价,但是您可以避免使用脚本、触发器或过程进行复杂的实现。

嗯,这取决于你所追求的诚信。

数据库是为引用完整性而设计的。因此,如果这就是您要寻找的,那么使用数据库的结构。不要自己动手。

如果您试图维护其他类型的完整性(如行的macs),那么触发器是完全可以接受的。

在这里评论了几个答案之后,我觉得有必要展示一个例子,说明如何使用触发器来强制执行超出RDBMS提供的基本RI的规则。

在我们自己的系统中,有几个表都有指向同一个表的外键(“mastertable”)。随着时间的推移,这些表中的行将被删除;删除最后一个子行后,我们希望删除父行并采取一些操作。我们通过在父表上创建一个列“childCount”来强制执行此规则,该列指示引用此列的行数(换句话说,引用计数)。

在所有子表的插入触发器中,我们有如下代码:

      SELECT ChildCount
        INTO numrows
        FROM MasterTable mt
       WHERE :new.MasterTableId = MasterTable.MasterTableId
         FOR UPDATE;

      UPDATE MasterTable
         SET ChildCount = ChildCount + 1
       WHERE :new.MasterTableId = MasterTable.MasterTableId;

在子表的删除触发器中,我们有:

      SELECT ChildCount
        INTO numrows
        FROM MasterTable
       WHERE :old.MasterTableId = MasterTable.MasterTableId
         FOR UPDATE;

      DELETE MasterTable
       WHERE ChildCount = 1
         AND :old.MasterTableId = MasterTable.MasterTableId;

    IF Sql%RowCount = 0 THEN
         UPDATE MasterTable
            SET ChildCount = ChildCount - 1
          WHERE :old.MasterTableId = MasterTable.MasterTableId;
    END IF;

更新触发器包含两个代码位。

此逻辑中的关键位是使用带有for update子句的单独的select语句,而不是只使用单个语句更新列。它确保同时进行的事务将被正确地序列化。

由于mastertable已经向所有子表声明了delete cascade规则,因此当上面的代码在删除具有现有子表的mastertable行的上下文中执行时,将导致ORA-04091(可变表)错误,因此这些语句是在捕获并忽略此错误的异常块的上下文中执行的。

最后,上面的代码是根据我们用于数据建模(erwin)的case工具自动生成的。erwin允许您创建“用户定义的属性”(udp),它有一种宏语言,可以用来根据您的模式生成几乎任何您需要的代码,因此我们需要做的就是将childcount列添加到适当的父表中,并将udp设置为“ref counted relationship”为true。

正如我在上面的注释中指出的,触发器不能完全替换声明的RI,因为您不能使用for update使delete cascade规则正常工作。但对于像这样的补充规则来说,这很好。

注意:这个代码已经在生产中11年了——它是在我们使用Oracle7时设计的。如果有人使用内置的Oracle功能以更现代的方式完成这项工作,我会很感兴趣的。

确保没有子记录就不能插入父记录的最佳方法是使用“全部插入”语法。这允许我们将记录插入到多个表中—它是同一条语句。

    INSERT ALL
        INTO parent
             (pk_col, val1, val2)
        INTO child1 
             (pk_col, val3, val4)
    SELECT some_seq.nextval as pk_col
           , val1
           , val2
           , val3
           , val4
    FROM where_ever

可以作为临时表的表(可能是外部表)。在您的情况下,它将双重化,VAL列是存储过程签名的参数。

您将无法阻止恶意开发人员编写插入没有子记录的父记录的代码。 n 记录。同样,不能使用已具有child1记录的父级的主键停止向child2中插入记录。为此,恐怕您需要代码审查。