All dependent objects are retrieved when you perform a Flashback Drop, just as the objects are all added to the Recycle Bin when the base table is dropped.
Emp is dependent on dept table , here i go
SQL> drop table dept cascade constraints
2 /
Table dropped.
SQL> flashback table dept to before drop
2 /
Flashback complete.
SQL> select * from dept
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into emp values (7935,'JERRY','DBA',7782,sysdate,8000,null,50)
2 /
1 row created.
Above insertion is not validating its parent key why? I dropped the dept table and then flashbacked.The PK should also be flashbacked and it is..
SQL> alter table dept add primary key (deptno)
2 /
alter table dept add primary key (deptno)
*
ERROR at line 1:
ORA-02260: table can have only one primary key
So whats wrong , dropping and flashbacking parent table can cause to dependent table references?
SQL> alter table emp add foreign key (deptno) references dept
2 /
alter table emp add foreign key (deptno) references dept
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.SYS_C005461) - parent keys not found
Flashingback to parentes table mess up the PK and FK.