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=gcdmg-mysql-2@m.gmane.org

Re: foreign keys: Cannot create InnoDB table

Hi,

> 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;

I guess your FK constraint needs the columns in the same
order as the PK constraint. That is: col1, col2, col3 in the
REFERENCES clause.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: foreign keys: Cannot create InnoDB table

Run:
SHOW ENGINE INNODB STATUS \G

And look for the "LATEST FOREIGN KEY ERROR" section. It'll explain the rea=
son for the (errno: 150) message.

Regards,
Gavin Towey

-----Original Message-----
From: wabiko.takuma [mailto:wabiko@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 =3D 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 =3D 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 =3D 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 =3D 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=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: foreign keys: Cannot create InnoDB table

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-co nstraints.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@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=gcdmg-mysql-2@m.gmane.org