From: Martijn Tonies
Date: August 14 2009 3:01pm
Subject: Re: foreign keys: Cannot create InnoDB table
List-Archive: http://lists.mysql.com/mysql/218406
Message-Id: <01b601ca1cf0$203e8f20$1401a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="ISO-2022-JP";
	reply-type=original
Content-Transfer-Encoding: 7bit

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