On 6/10/2010 6:38 PM, Lola Lee Beno wrote:
I'm trying to create a database, using code generated by MySQL Workbench
5.2.21 RC. I'm running into this strange issue:
ERROR 1005 (HY000): Can't create table 'nxdb.#sql-a6_3b' (errno: 150)
Database was created using utf8 as charset, collation utf8_general_ci.
Original code generated by Workbench:
CREATE TABLE IF NOT EXISTS `nxdb`.`User_Role` (
`user_id` VARCHAR(35) NOT NULL ,
`role_id` VARCHAR(35) NOT NULL ,
PRIMARY KEY (`user_id`, `role_id`) ,
CONSTRAINT `user_id`
FOREIGN KEY (`user_id`)
REFERENCES `nxdb`.`Users` (`user_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `role_id`
FOREIGN KEY (`role_id`)
REFERENCES `nxdb`.`NXRoles` (`role_id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
(Note, tables Users and NXRoles were already created). This query
didn't work. So, I stripped down to the basics to get the table created:
CREATE TABLE IF NOT EXISTS `nxdb`.`User_Role` (
`user_id` VARCHAR(35) NOT NULL ,
`role_id` VARCHAR(35) NOT NULL ,
PRIMARY KEY (`user_id`, `role_id`) )
ENGINE = InnoDB;
Then, I tried running this:
ALTER TABLE `User_Role` ADD
CONSTRAINT `role_id`
FOREIGN KEY (`role_id`)
REFERENCES `NXRoles` (`role_id`)
ON DELETE CASCADE
ON UPDATE CASCADE;
And got this error message: ERROR 1005 (HY000): Can't create table
'nxdb.#sql-a6_3b' (errno: 150)
When I check the structure of User_Role in phpMyAdmin (couldn't see a
way to do this in Workbench), this is what I'm seeing:
Action Keyname Type Unique Packed Field Cardinality
Collation Null Comment
PRIMARY BTREE Yes No user_id 0 A
role_id 0 A
So, is there no need for me to actually add these constraints?
For more details about the error 150 code, check the SHOW ENGINE INNODB
STATUS report.
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org