** It is amazing how complicated things can get with a simple integrity constraint
like foreign key and unique key. Yet knowing how to manipulate them are
absolutely critical in a data warehouse environment, for performance reason.
Here are some scenarios on the state of UK nad FK...
** Further explaination on integrity constraints maintainence in the data warehouse.
SQL>
SQL> truncate table test4;
Table truncated.
SQL> truncate table test3;
Table truncated.
SQL> insert into test3 values('a',1);
1 row created.
SQL> insert into test3 values('b',2);
1 row created.
SQL> insert into test3 values('c',3);
1 row created.
SQL> insert into test3 values('e',4);
1 row created.
SQL> insert into test4 values('a',5);
1 row created.
SQL> insert into test4 values('b',2);
1 row created.
SQL> insert into test4 values('c',3);
1 row created.
SQL> insert into test4 values('e',4);
1 row created.
SQL> select * from test4;
C1 C2
-- ----------
a 5
b 2
c 3
e 4
SQL> select index_name from user_indexes where table_name='TEST4';
no rows selected
SQL> select constraint_name,constraint_type,status,invalid from user_constraints
2 where table_name='TEST4';
no rows selected
SQL>
SQL> -- FK constraint does not create indexes
SQL> alter table test4 add constraint test4_fk foreign key (c1)
2 references test3(c1);
Table altered.
SQL> select index_name from user_indexes where table_name='TEST4';
no rows selected
SQL> select constraint_name,constraint_type,status,validated,invalid from user_constraints
2 where table_name='TEST4';
CONSTRAINT_NAME C STATUS VALIDATED INVALID
------------------------------ - -------- ------------- -------
TEST4_FK R ENABLED VALIDATED
SQL>
SQL> alter table test4 drop constraint test4_fk;
Table altered.
SQL>
SQL> -- Unique constraint creates index
SQL> alter table test4 add constraint test4_u unique (c2);
Table altered.
SQL> select index_name from user_indexes where table_name='TEST4';
INDEX_NAME
------------------------------
TEST4_U
SQL> select constraint_name,constraint_type,status,validated,invalid from user_constraints
2 where table_name='TEST4';
CONSTRAINT_NAME C STATUS VALIDATED INVALID
------------------------------ - -------- ------------- -------
TEST4_U U ENABLED VALIDATED
SQL>
SQL> -- Disable unqiue constraint will remove the index, duplicates CAN be inserted.
SQL> alter table test4 disable constraint test4_u;
Table altered.
SQL> select index_name from user_indexes where table_name='TEST4';
no rows selected
SQL> select constraint_name,constraint_type,status,validated,invalid from user_constraints
2 where table_name='TEST4';
CONSTRAINT_NAME C STATUS VALIDATED INVALID
------------------------------ - -------- ------------- -------
TEST4_U U DISABLED NOT VALIDATED
SQL> insert into test4 values('b',2);
1 row created.
SQL> rollback;
Rollback complete.
SQL>
SQL> -- Reenable unqiue constraint will create the index with the same name.
SQL> alter table test4 enable constraint test4_u;
Table altered.
SQL> select index_name from user_indexes where table_name='TEST4';
INDEX_NAME
------------------------------
TEST4_U
SQL> select constraint_name,constraint_type,status,validated,invalid from user_constraints
2 where table_name='TEST4';
CONSTRAINT_NAME C STATUS VALIDATED INVALID
------------------------------ - -------- ------------- -------
TEST4_U U ENABLED VALIDATED
SQL>
SQL> -- Modify: disable validate -> index dropped, no DML is allowed.
SQL> alter table test4 modify constraint test4_u disable validate;
Table altered.
SQL> select index_name from user_indexes where table_name='TEST4';
no rows selected
SQL> select constraint_name,constraint_type,status,validated,invalid from user_constraints
2 where table_name='TEST4';
CONSTRAINT_NAME C STATUS VALIDATED INVALID
------------------------------ - -------- ------------- -------
TEST4_U U DISABLED VALIDATED
SQL> insert into test4 values('b',2);
insert into test4 values('b',2)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (JYANG.TEST4_U)
disabled and validated
SQL> update test4 set c2=7 where c1='a';
update test4 set c2=7 where c1='a'
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (JYANG.TEST4_U)
disabled and validated
SQL> delete from test4 where c1='a';
delete from test4 where c1='a'
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (JYANG.TEST4_U)
disabled and validated
SQL> rollback;
Rollback complete.
SQL>
SQL> -- Modify: enable validate -> index recreated.
SQL> alter table test4 modify constraint test4_u enable validate;
Table altered.
SQL> select index_name from user_indexes where table_name='TEST4';
INDEX_NAME
------------------------------
TEST4_U
SQL> select constraint_name,constraint_type,status,validated,invalid from user_constraints
2 where table_name='TEST4';
CONSTRAINT_NAME C STATUS VALIDATED INVALID
------------------------------ - -------- ------------- -------
TEST4_U U ENABLED VALIDATED
SQL>
SQL> -- Modify: enable novalidate -> DML is allowed, constraint is being enforced.
SQL> alter table test4 modify constraint test4_u enable novalidate;
Table altered.
SQL> select index_name from user_indexes where table_name='TEST4';
INDEX_NAME
------------------------------
TEST4_U
SQL> select constraint_name,constraint_type,status,validated,invalid from user_constraints
2 where table_name='TEST4';
CONSTRAINT_NAME C STATUS VALIDATED INVALID
------------------------------ - -------- ------------- -------
TEST4_U U ENABLED NOT VALIDATED
SQL> insert into test4 values('b',2);
insert into test4 values('b',2)
*
ERROR at line 1:
ORA-00001: unique constraint (JYANG.TEST4_U) violated
SQL> update test4 set c2=7 where c1='a';
1 row updated.
SQL> delete from test4 where c1='a';
1 row deleted.
SQL> rollback;
Rollback complete.
SQL>
SQL> -- Remove the unique constraint and mess up the data.
SQL> alter table test4 drop constraint test4_u;
Table altered.
SQL> insert into test4 values('a',5);
1 row created.
SQL>
SQL> -- Can not enable novalidate the UK.
SQL> alter table test4 add constraint test4_u unique (c2) enable novalidate;
alter table test4 add constraint test4_u unique (c2) enable novalidate
*
ERROR at line 1:
ORA-02299: cannot validate (JYANG.TEST4_U) - duplicate keys found
SQL>
SQL> select * from test3;
C1 C2
-- ----------
a 1
b 2
c 3
e 4
SQL>
SQL> -- Add data to mess up the FK
SQL> insert into test4 values('*',5);
1 row created.
SQL>
SQL> -- Current FK data is messed up, but FK is being enforced on all new data.
SQL> alter table test4 add constraint test4_fk foreign key (c1)
2 references test3(c1) enable novalidate;
Table altered.
SQL> insert into test4 values('w',0);
insert into test4 values('w',0)
*
ERROR at line 1:
ORA-02291: integrity constraint (JYANG.TEST4_FK) violated - parent key not
found
SQL>
SQL> alter table test4 drop constraint test4_fk;
Table altered.
SQL>
SQL> spool off