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
