I am trying to have 2 tables with crossed foreign keys, but I am not allowed to reference a table that doesn't exist when I am creating them. Any way of creating tables like this for mysql, something like declare both tables at the same time or delay evaluation of foreign keys?
Error is 1005: Can't create table blocks.frm (errno 150) on a mysql 5.0
SQL:
create table if not exists blocks(
id int unsigned not null auto_increment,
title varchar(100),
defaultpage int unsigned not null,
foreign key(defaultpage) references pages(pageID),
primary key(id)) engine=innodb;
create table if not exists pages(
pageID int unsigned not null auto_increment,
title varchar(50) not null,
content blob,
blockid int unsigned not null,
foreign key(blockid) references block(id),
primary key(pageID) ) engine=innodb;
What is the proper way to solve the problem?
Bringing cletus's answer (which is perfectly correct) to the code...
create table if not exists pages(
pageID int unsigned not null auto_increment,
title varchar(50) not null,
content blob,
blockid int unsigned not null,
primary key(pageID) ) engine=innodb;
create table if not exists blocks(
id int unsigned not null auto_increment,
title varchar(100),
defaultpage int unsigned not null,
foreign key(defaultpage) references pages(pageID),
primary key(id)) engine=innodb;
alter table pages add constraint fk_pages_blockid foreign key (blockid) references blocks (id);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With