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=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
