Using Mview over tables having FKs - Consideration

主外键在数据库的应用当中比较常见，它就像一把双刃剑，用得好的话会给你的系统带来坚固的约束关系。用得不好或者滥用的话会给维护和排错带来非常大的麻烦。

最近刚好接到一起数据复制的需求，主库和MV库面向的应用类型差不多，MV库上面连接的应用需要访问主库的一张表，现状是有专门的人员负责对这份数据进行写入，比较繁琐，所以考虑使用MV的方式来进行数据的共享。

其中要求同步的是一张表，但是这张表在备库有被其他表的FK关联到，经过调查，相关联的几个表在主库也是存在的，并且具有同样的约束条件。

考虑到主外键的约束关系如果单独同步可能导致违反约束，所以的话有主外键约束的表必须放在一个事务进行同步。

经过了解，实际上这几个表的数据都是从主库来的，并且可以放在一起同步。总算可以继续做下去。

那么在MV节点需要对这几个关联到的约束进行属性调整：

1. 初始约束校验，后续约束校验。

2. 开启允许延缓检查属性。

3. 调整为事务结束校验。

（从非延缓到延缓需要重建约束）

联机文档原文:

DEFERRABLE Clause The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

• Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use theSET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

  If you declare a new constraint NOT DEFERRABLE, then it must be valid at the time theCREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

• Specify DEFERRABLE to indicate that in subsequent transactions you can use the SETCONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

You cannot alter the deferrability of a constraint. That is, whether you specify either of these parameters, or make the constraint NOT DEFERRABLE implicitly by specifying neither of them, you cannot specify this clause in an ALTER TABLE statement. You must drop the constraint and re-create it.

INITIALLY Clause The INITIALLY clause establishes the default checking behavior for constraints that are DEFERRABLE. The INITIALLY setting can be overridden by a SETCONSTRAINT(S) statement in a subsequent transaction.

• Specify INITIALLY IMMEDIATE to indicate that Oracle should check this constraint at the end of each subsequent SQL statement. If you do not specify INITIALLY at all, then the default is INITIALLY IMMEDIATE.

  If you declare a new constraint INITIALLY IMMEDIATE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

• Specify INITIALLY DEFERRED to indicate that Oracle should check this constraint at the end of subsequent transactions.

This clause is not valid if you have declared the constraint to be NOT DEFERRABLE, because aNOT DEFERRABLE constraint is automatically INITIALLY IMMEDIATE and cannot ever beINITIALLY DEFERRED.

ENABLE Clause Specify ENABLE if you want the constraint to be applied to the data in the table.

If you enable a unique or primary key constraint, and if no index exists on the key, then Oracle Database creates a unique index. Unless you specify KEEP INDEX when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.

You can also avoid rebuilding the index and eliminate redundant indexes by creating new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.

• ENABLE VALIDATE specifies that all old and new data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.

  If any row in the table violates the integrity constraint, the constraint remains disabled and Oracle returns an error. If all rows comply with the constraint, Oracle enables the constraint. Subsequently, if new data violates the constraint, Oracle does not execute the statement and returns an error indicating the integrity constraint violation.

  If you place a primary key constraint in ENABLE VALIDATE mode, the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key NOT NULL before entering data into the column and before enabling the primary key constraint of the table.

• ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint and therefore does not require a table lock.

If you specify neither VALIDATE nor NOVALIDATE, the default is VALIDATE.

If you change the state of any single constraint from ENABLE NOVALIDATE to ENABLE VALIDATE, the operation can be performed in parallel, and does not block reads, writes, or other DDL operations.

Restriction on the ENABLE Clause You cannot enable a foreign key that references a disabled unique or primary key.

MV的操作就不用说了，把关联的MV放到一个刷新组搞定。