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