Hi, Martijn, Gavin.
SHOW INNODB STATUS gave me helpful messages like following:
> ------------------------
> LATEST FOREIGN KEY ERROR
> ------------------------
> 090821 12:53:18 Error in foreign key constraint of table test_fk/tbl1:
>
> FOREIGN KEY (`col1` , `col2` , `col3` )
> REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB:
> Cannot find an index in the referenced table where the
> referenced columns appear as the first columns, or column types
> in the table and the referenced table do not match for constraint.
> Note that the internal storage type of ENUM and SET changed in
> tables created with >= InnoDB-4.1.12, and such columns in old tables
> cannot be referenced by such columns in new tables.
> See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
> for correct foreign key definition.
> ------------
> TRANSACTIONS
> ------------
and then, I modified columns order, I succeeded to create `test_fk`.`tbl1` on
5.1.31sp1-ent.
Thank you!
wabi
Gavin Towey wrote:
> Run:
> SHOW ENGINE INNODB STATUS \G
>
> And look for the "LATEST FOREIGN KEY ERROR" section. It'll explain the reason for
> the (errno: 150) message.
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: wabiko.takuma [mailto:wabiko@stripped]
> Sent: Friday, August 14, 2009 3:35 AM
> To: mysql@stripped
> Subject: foreign keys: Cannot create InnoDB table
>
> Hi, All,
>
> 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;
>
>
>
> -- ------------------------------------------------------
> -- mysql Output
> mysql> SELECT VERSION(),NOW() FROM DUAL\G
> *************************** 1. row ***************************
> VERSION(): 5.1.31sp1-enterprise-gpl-advanced
> NOW(): 2009-08-14 18:04:00
> 1 row in set (0.00 sec)
>
> mysql> DROP DATABASE `test_fk` ;
> ERROR 1008 (HY000): Can't drop database 'test_fk'; database doesn't exist
> mysql> CREATE DATABASE IF NOT EXISTS `test_fk` ;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> SHOW WARNINGS;
> +-------+------+-------------------------------------------------------+
> | Level | Code | Message |
> +-------+------+-------------------------------------------------------+
> | Error | 1008 | Can't drop database 'test_fk'; database doesn't exist |
> +-------+------+-------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> mysql> USE `test_fk`;
> Database changed
> mysql>
> mysql> DROP TABLE IF EXISTS `test_fk`.`tbl2` ;
> Query OK, 0 rows affected, 1 warning (0.00 sec)
>
> mysql> SHOW WARNINGS;
> +-------+------+----------------------+
> | Level | Code | Message |
> +-------+------+----------------------+
> | Note | 1051 | Unknown table 'tbl2' |
> +-------+------+----------------------+
> 1 row in set (0.00 sec)
>
> mysql> 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;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> SHOW WARNINGS;
> Empty set (0.00 sec)
>
> mysql>
> mysql> DROP TABLE IF EXISTS `test_fk`.`tbl1` ;
> Query OK, 0 rows affected, 1 warning (0.00 sec)
>
> mysql> SHOW WARNINGS;
> +-------+------+----------------------+
> | Level | Code | Message |
> +-------+------+----------------------+
> | Note | 1051 | Unknown table 'tbl1' |
> +-------+------+----------------------+
> 1 row in set (0.00 sec)
>
> mysql> 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;
> ERROR 1005 (HY000): Can't create table 'test_fk.tbl1' (errno: 150)
> mysql> SHOW WARNINGS;
> +-------+------+------------------------------------------------+
> | Level | Code | Message |
> +-------+------+------------------------------------------------+
> | Error | 1005 | Can't create table 'test_fk.tbl1' (errno: 150) |
> +-------+------+------------------------------------------------+
> 1 row in set (0.00 sec)
>
> mysql> Bye
> # perror 150
> MySQL error code 150: Foreign key constraint is incorrectly formed
> #
