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