subject:"foreign keys\: Cannot create InnoDB table"

Re: foreign keys: Cannot create InnoDB table

2009-08-20 Thread wabiko.takuma

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:wab...@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

Re: foreign keys: Cannot create InnoDB table

2009-08-14 Thread Martijn Tonies


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=arch...@jab.org

foreign keys: Cannot create InnoDB table

2009-08-14 Thread wabiko.takuma

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=arch...@jab.org

RE: foreign keys: Cannot create InnoDB table

2009-08-14 Thread Gavin Towey

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:wab...@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=gto...@ffn.com


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.

--
MySQL General Mailing

Re: foreign keys: Cannot create InnoDB table

Re: foreign keys: Cannot create InnoDB table

foreign keys: Cannot create InnoDB table

RE: foreign keys: Cannot create InnoDB table

4 matches

Site Navigation

Mail list logo

Footer information