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:wab...@sysrdc.ns-sol.co.jp] > Sent: Friday, August 14, 2009 3:35 AM > To: mysql@lists.mysql.com > 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 > # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
