Skip to Main Content
  • Questions
  • Query regarding primary key , foreign key relations and inserts

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Rajendra.

Asked: November 09, 2016 - 4:37 pm UTC

Last updated: November 10, 2016 - 11:32 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I have a two tables
Table1 (id_pk number, name_fk varchar(10))
Table2 (name_pk varchar(10), id_fk number)

id_pk : Primary key of table1
name_fk : foreign key referencing name_pk of Table2
name_pk : Primary key of table2
id_fk : Foreign key referencing id_pk of Table1


The requirement is to insert the records into Table1 and Table2. Is it possible? because when i tried doing so, i am getting an error saying "dependencies exist". Please help me in this regard.

Thanks and Regards,
Rajendra

and Chris said...

Ahhh, the good old circular foreign keys problem!

To overcome this you need to make one of the foreign key deferrable and make sure it's deferred:

create table t1 (
  id_pk number primary key, name_fk varchar(10)
);
create table t2 (
  name_pk varchar(10) primary key, id_fk number references t1 (id_pk)
);
alter table t1 add constraint fk foreign key (name_fk) 
  references t2 (name_pk);

insert into t1 values (1, 'NAME');

ORA-02291: integrity constraint (CHRIS.FK) violated - parent key not found

insert into t2 values ('NAME', 1);

ORA-02291: integrity constraint (CHRIS.SYS_C006026) violated - parent key not found

alter table t1 drop constraint fk;
alter table t1 add constraint fk foreign key (name_fk) 
  references t2 (name_pk) deferrable initially deferred;

insert into t1 values (1, 'NAME');
insert into t2 values ('NAME', 1);

select * from t1;

ID_PK  NAME_FK  
1      NAME 

select * from t2;   

NAME_PK  ID_FK  
NAME     1  

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Really?

Duke Ganote, November 09, 2016 - 5:24 pm UTC

Academic exercise, or is there a real-world example when such a thing is necessary?
Chris Saxon
November 10, 2016 - 11:32 am UTC

True, valid use cases for circular FKs are rare. But they do exist. e.g. tables of cities and states.

Every city belongs to a state. Each state has a capital city. So both tables should reference each other!

Very useful

Rajendra kalepu, November 10, 2016 - 4:07 am UTC

Thank you very much Chris,

Its a good learning for me.

Regards,
Raj