Saturday, August 05, 2006

Circular dependency between Tables in SQL

Update: Added a new way to solve the problem

Q) How had to create Tables with a circular dependency between them. Like

CREATE TABLE A (
....A_ID INT,
....PRIMARY KEY A_ID,
....B_FK INT,
....FOREIGN KEY B_FK
....REFERENCES B(B_ID)
);


CREATE TABLE B (
....B_ID INT,
....PRIMARY KEY B_ID,
....A_FK INT,
....FOREIGN KEY A_FK
....REFERENCES A(A_ID)
);

... now this will not work as during create, table B does not exist

A) So one solution is ...

CREATE TABLE A (
....A_ID INT,
....PRIMARY KEY A_ID
);

CREATE TABLE B (
....B_ID INT,
....PRIMARY KEY B_ID,
....A_FK INT,
....FOREIGN KEY A_FK
....REFERENCES A(A_ID)
);

ALTER TABLE A ADD COLUMN B_FK INT AFTER A_ID;
ALTER TABLE A ADD FOREIGN KEY B_FK REFERENCES B(B_ID);

A little bit of cheating does the trick :)



A) Another mysql specific solution is to use the FOREIGN_KEY_CHECKS variable.

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

In the dump_file_name, tables can be created in any order without checking of Foreign Key constraints. So we can create tables with circular depenndencies

2 comments:

Sean said...

Mohnish, I found your blog via LinkedIn. I'm looking for a C#/.Net web app developer who knows AJAX and other spiffy buzzwords. http://www.pluck.com/company/jobs.html

Unknown said...

hey in MYSQL,i have created a table with circular dependency in the same table...but i am not able to inser any data due to constraint restriction even though the value for foreign key attribute is NULL...

Like for example there is a table A withe 2 attributes sno(primary key) and serial(foreign key referring to sno of the same table A).

what do i do???