主外键在数据库的应用当中比较常见,它就像一把双刃剑,用得好的话会给你的系统带来坚固的约束关系。用得不好或者滥用的话会给维护和排错带来非常大的麻烦。
最近刚好接到一起数据复制的需求,主库和mv库面向的应用类型差不多,mv库上面连接的应用需要访问主库的一张表,现状是有专门的人员负责对这份数据进行写入,比较繁琐,所以考虑使用mv的方式来进行数据的共享。
其中要求同步的是一张表,但是这张表在备库有被其他表的fk关联到,经过调查,相关联的几个表在主库也是存在的,并且具有同样的约束条件。
考虑到主外键的约束关系如果单独同步可能导致违反约束,所以的话有主外键约束的表必须放在一个事务进行同步。
经过了解,实际上这几个表的数据都是从主库来的,并且可以放在一起同步。总算可以继续做下去。
那么在mv节点需要对这几个关联到的约束进行属性调整:
1. 初始约束校验,后续约束校验。
2. 开启允许延缓检查属性。
3. 调整为事务结束校验。
(从非延缓到延缓需要重建约束)
联机文档原文:
deferrable clause the <code>deferrable</code> and <code>not</code> <code>deferrable</code> parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the <code>set</code> <code>constraint</code>(<code>s</code>) statement. if you omit this clause, then the default is <code>not</code> <code>deferrable</code>.
specify <code>not</code> <code>deferrable</code> to indicate that in subsequent transactions you cannot use the<code>set</code> <code>constraint</code>[<code>s</code>] clause to defer checking of this constraint until the transaction is committed. the checking of a <code>not</code> <code>deferrable</code> constraint can never be deferred to the end of the transaction.
if you declare a new constraint <code>not</code> <code>deferrable</code>, then it must be valid at the time the<code>create</code> <code>table</code> or <code>alter</code> <code>table</code> statement is committed or the statement will fail.
specify <code>deferrable</code> to indicate that in subsequent transactions you can use the <code>set</code><code>constraint</code>[<code>s</code>] 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 <code>not</code> <code>deferrable</code> implicitly by specifying neither of them, you cannot specify this clause in an <code>alter</code> <code>table</code> statement. you must drop the constraint and re-create it.
initially clause the <code>initially</code> clause establishes the default checking behavior for constraints that are <code>deferrable</code>. the <code>initially</code> setting can be overridden by a <code>set</code><code>constraint</code>(<code>s</code>) statement in a subsequent transaction.
specify <code>initially</code> <code>immediate</code> to indicate that oracle should check this constraint at the end of each subsequent sql statement. if you do not specify <code>initially</code> at all, then the default is <code>initially</code> <code>immediate</code>.
if you declare a new constraint <code>initially</code> <code>immediate</code>, then it must be valid at the time the <code>create</code> <code>table</code> or <code>alter</code> <code>table</code> statement is committed or the statement will fail.
specify <code>initially</code> <code>deferred</code> 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 <code>not</code> <code>deferrable</code>, because a<code>not</code> <code>deferrable</code> constraint is automatically <code>initially</code> <code>immediate</code> and cannot ever be<code>initially</code> <code>deferred</code>.
enable clause specify <code>enable</code> 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 <code>keep index</code> 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 <code>enable</code> operations are facilitated.
<code>enable</code> <code>validate</code> 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 <code>enable</code> <code>validate</code> 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 <code>not</code> <code>null</code> before entering data into the column and before enabling the primary key constraint of the table.
<code>enable</code> <code>novalidate</code> 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 <code>validate</code> nor <code>novalidate</code>, the default is <code>validate</code>.
if you change the state of any single constraint from <code>enable</code> <code>novalidate</code> to <code>enable</code> <code>validate</code>, 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放到一个刷新组搞定。