List:General Discussion« Previous MessageNext Message »
From:wabiko.takuma Date:August 21 2009 4:45am
Subject:Re: foreign keys: Cannot create InnoDB table
View as plain text  
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:wabiko@stripped]
> Sent: Friday, August 14, 2009 3:35 AM
> To: mysql@stripped
> 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
> #
Thread
foreign keys: Cannot create InnoDB tablewabiko.takuma14 Aug
  • Re: foreign keys: Cannot create InnoDB tableMartijn Tonies14 Aug
  • RE: foreign keys: Cannot create InnoDB tableGavin Towey14 Aug
    • Re: foreign keys: Cannot create InnoDB tablewabiko.takuma21 Aug