Euphorbium Euphorbium - 2 years ago 71
SQL Question

How to refer to a table, which is not yet created?

Lets say I need to create two tables which both refer to each other (they need constraints). Is the only way to create the tables without the constraints, and then add them in a separate statement? I remember that some orms can solve this themselves, but is it possible to do this only using sql and in two statements?

Answer Source

You should must create the tables first without foreign keys and append them after both tables are created:

create table t1 (id int not null primary key, id2 int not null);
create table t2 (id int not null primary key, id1 int not null);

alter table t1 add foreign key (id2) references t2(id);
alter table t2 add foreign key (id1) references t1(id);

Good news: A schema dump works perfect (so is my database):

mysqldump -u root so t1 t2 | mysql -u root so

This gives no errors because mysqldump inserts DISABLE KEYS and ENABLE KEYS at the right places.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download