Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Crossed foreign keys in SQL

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?


1 Answers

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);
like image 195
Alexander Lebedev Avatar answered Jul 01 '26 01:07

Alexander Lebedev



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!