討論關於Constraint states

討論關於Constraint states:

<>P649

 

最佳實踐：

Integrity Constraint States: Procedures and Benefits

Using integrity constraint states in the following order can ensure the best benefits:

1. Disable state.

2. Perform. the operation (load, export, import).

3. Enable NOVALIDATE state.

4. Enable state.

Some benefits of using constraints in this order are:

 No locks are held.

 All constraints can go to enable state concurrently.

 Constraint enabling is done in parallel.

 Concurrent activity on table is permitted.

 

Constraint states include:

 

ENABLE ensures that all incoming data conforms to the constraint

 

DISABLE allows incoming data, regardless of whether it conforms to the constraint

 

VALIDATE ensures that existing data conforms to the constraint

 

NOVALIDATE means that some existing data may not conform. to the constraint

 

In addition:

 

ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.

 

ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.

 

In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table.

 

DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked and is not necessarily true.

 

DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.

 

Transitions between these states are governed by the following rules:

 

l         ENABLE implies VALIDATE, unless NOVALIDATE is specified.

 

l         DISABLE implies NOVALIDATE, unless VALIDATE is specified.

 

l         VALIDATE and NOVALIDATE do not have any default implications for the ENABLE and DISABLE states.

 

When a unique or primary key moves from the DISABLE state to the ENABLE state, if there is no existing index, a unique index is automatically created. Similarly, when a unique or primary key move from ENABLE to DISABLE and it is enabled with a unique index, the unique index is dropped.

When any constraint is moved from the NOVALIDATE state to the VALIDATE state, all data must be checked. (This can be very slow.) However, moving from VALIDATE to NOVALIDATE simply forgets that the data was ever checked.

 

Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE VALIDATE state does not block reads, writes, or other DDL statements. It can be done in parallel.

 

test example:

 

create table t1(id number primary key  );

 

col OWNER format a20;

col CONSTRAINT_NAME format a20;

col Table_NAME format a20;

idle> select TABLE_NAME, constraint_name, status, validated from user_constraints where table_name='T1';

T1                   SYS_C002055          ENABLED  VALIDATED

 

 

insert into t1 values(1);

insert into t1 values(2);

commit;

 

idle> insert into t1 values(1);

insert into t1 values(1)

*

ERROR at line 1:

ORA-00001: unique constraint (TEST.SYS_C002055) violated

 

alter table t1 rename constraint SYS_C002055  to t1_pk;

idle> alter table t1 rename constraint SYS_C002055  to t1_pk;

 

Table altered.

 

alter table t1 disable constraint t1_pk;

idle> alter table t1 disable constraint t1_pk;

 

Table altered.

 

 

idle> select TABLE_NAME, constraint_name, status, validated from user_constraints where table_name='T1';

T1                   T1_PK                DISABLED NOT VALIDATED

一旦disable, 隨之也NOVALITDATED,除非Validate is specifed.

 

 

insert into t1 values(1);

commit;

 

 

idle> ALTER TABLE t1 ENABLE  CONSTRAINT t1_pk;

ALTER TABLE t1 ENABLE  CONSTRAINT t1_pk

*

ERROR at line 1:

ORA-02437: cannot validate (TEST.T1_PK) - primary key violated

因為t1中已存在有重複的key.所以不能enable validate

 

 

idle> ALTER TABLE t1 ENABLE NOVALIDATE CONSTRAINT t1_pk;

ALTER TABLE t1 ENABLE NOVALIDATE CONSTRAINT t1_pk

*

ERROR at line 1:

ORA-02437: cannot validate (TEST.T1_PK) - primary key violated

 

idle> ALTER TABLE t1  NOVALIDATE CONSTRAINT t1_pk;

ALTER TABLE t1  NOVALIDATE CONSTRAINT t1_pk

                *

ERROR at line 1:

ORA-01735: invalid ALTER TABLE option

 

idle> drop table t1;

 

Table dropped.

 

 

idle> create table t1(id number primary key disable );

 

Table created.

idle> select TABLE_NAME, constraint_name, status, validated from user_constraints where table_name='T1';

T1                   SYS_C002056          DISABLED NOT VALIDATED

此時的狀態為 Disabled NOVALIDATED

 

insert into t1 values(1);

insert into t1 values(2);

commit;

 

 

idle> insert into t1 values(1);

 

1 row created.

 

 

再次插入

idle> insert into t1 values(1);

 

1 row created.

 

這時並沒有報錯。因為狀態為Disabled NOVALIDATED

 

 

idle> ALTER TABLE T1 ENABLE CONSTRAINT SYS_C002056;

ALTER TABLE T1 ENABLE CONSTRAINT SYS_C002056

*

ERROR at line 1:

ORA-02437: cannot validate (TEST.SYS_C002056) - primary key violated

不能變為Validate,因為existing rows viloate the primary rule.

idle> select * from t1;

         1

         1

 

idle> select rowid from t1;

AAAHCLAAGAAAAAMAAA

AAAHCLAAGAAAAAMAAB

idle> delete t1 where rowid='AAAHCLAAGAAAAAMAAA';

 

1 row deleted.

 

idle> commit;

 

Commit complete.

刪除重複行

 

idle>  ALTER TABLE T1 ENABLE CONSTRAINT SYS_C002056;

 

Table altered.

恢復正常。