SQL> create table t1 ( 2 id number primary key, 3 val varchar2(10)); Table created. SQL> create table t2 ( 2 id number primary key, 3 val varchar2(10)); Table created.Next, we'll create an FK constraint from T1 to T2.
SQL> alter table t1 add constraint t1_t2_fk 2 foreign key (id) references t2; Table altered.Now, we'll attempt to create a constraint from T2 to T1 using the same columns.
SQL> alter table t2 add constraint t2_t1_fk 2 foreign key (id) references t1; Table altered.So we now have created the constraints successfully. So Oracle will allow this situation, but there is a problem we have introduced here. I cannot insert values into any of the two tables:
SQL> insert into t1 values (1,'test'); insert into t1 values (1,'test') * ERROR at line 1: ORA-02291: integrity constraint (SYS.T1_T2_FK) violated - parent key not foundAs you can see, I could not insert a value into T1 because the parent record does not exist in T2. Similarly, I cannot insert a value into T2 because the parent record does not exist in T1. To get around this problem, you will have to disable the constraint(s) when inserting data.
That being said, I cannot think of a relation that would use this type of referential integrity. How can a table...
require a parent record in another table, but that other table requires a parent record in the first table? This is a circular definition and probably means the relational model is flawed somewhere.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.