List:General Discussion« Previous MessageNext Message »
From:wabiko.takuma Date:August 14 2009 10:35am
Subject:foreign keys: Cannot create InnoDB table
View as plain text  
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