List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:August 14 2009 3:01pm
Subject:Re: foreign keys: Cannot create InnoDB table
View as plain text  
Hi,

> I can't create InnoDB table with foreign key constraints using more than 3 
> colmuns.
> When I create table `test_fk`.`tbl1`, it gives me:
>
>    Can't create table 'test_fk.tbl1' (errno: 150)
>
> why? CREATE TABLE syntax looks perfectly right to me.
>
> Any suggestions are welcome.
>
> Thank you,
> wabi
>
> -- ------------------------------------------------------
> -- DDL
> CREATE  TABLE IF NOT EXISTS `test_fk`.`tbl2` (
>  `col1` VARCHAR(2) NOT NULL ,
>  `col2` VARCHAR(2) NOT NULL ,
>  `col3` VARCHAR(2) NOT NULL ,
>  PRIMARY KEY (`col1`, `col2`, `col3`) )
> ENGINE = InnoDB;
>
> CREATE  TABLE IF NOT EXISTS `test_fk`.`tbl1` (
>  `tbl1_id` VARCHAR(12) NOT NULL ,
>  `col1` VARCHAR(2) NULL ,
>  `col2` VARCHAR(2) NULL ,
>  `col3` VARCHAR(2) NULL ,
>  PRIMARY KEY (`tbl1_id`) ,
>  INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) ,
>  CONSTRAINT `fk_test`
>    FOREIGN KEY (`col1` , `col2` , `col3` )
>    REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` )
>    ON DELETE NO ACTION
>    ON UPDATE NO ACTION)
> ENGINE = InnoDB;

I guess your FK constraint needs the columns in the same
order as the PK constraint. That is: col1, col2, col3 in the
REFERENCES clause.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com 

Thread
foreign keys: Cannot create InnoDB tablewabiko.takuma14 Aug
  • Re: foreign keys: Cannot create InnoDB tableMartijn Tonies14 Aug
  • RE: foreign keys: Cannot create InnoDB tableGavin Towey14 Aug
    • Re: foreign keys: Cannot create InnoDB tablewabiko.takuma21 Aug