Oracle : Foreign key and validation
Illustrating how the foreign key could be disabled and using the NOVALIDATE - the rows in the child table could be preserved even after enabling the foreign key. The newer rows will be checked for presence in the ‘parent’ table.
SQL> create table test1(a number(8,2) primary key)
2 /
Table created.
SQL> create table test2(a number(8,2) primary key, constraint fkey1 foreign key(a) references test1(a))
2 /
Table created.
SQL> insert into test1 values(1)
2 /
1 row created.
SQL> c/1/2
1* insert into test2 values(1)
SQL> /
1 row created.
SQL>insert into test1 values(2)
SQL> /
1 row created.
SQL> c/(2)/(3)
1* insert into test1 values(3)
SQL> /
1 row created.
SQL> c/1/2
1* insert into test2 values(3)
SQL> /
1 row created.
SQL> alter table test2 disable constraint fkey1
SQL> /
Table altered.
SQL> truncate table test1
2 /
Table truncated.
SQL> alter table test2 enable novalidate constraint fkey1
SQL> /
Table altered.
SQL> insert into test2 values(2)
2 /
insert into test2 values(2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FKEY1) violated - parent key not found
Recent comments