我正在尝试使用带有交叉外键的2个表,但我不允许在创建它时引用不存在的表.有什么方法可以为mysql创建这样的表,比如同时声明两个表或延迟外键的评估?
错误是1005:无法在mysql 5.0上创建表blocks.frm(errno 150)
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;
解决问题的正确方法是什么?
将cletus的答案(这是完全正确的)带到代码中......
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);