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/[email protected]
