List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:August 14 2009 6:53pm
Subject:RE: foreign keys: Cannot create InnoDB table
View as plain text  
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
#



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


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