I created dummy tables for Roles and Users specifying the primary keys as 'serial' and then tried the below syntax. It failed.
Then I redefined the primary keys in the parent tables to be the exact same type as the foreign keys in UserRole and it worked. So, check the datatype of all your keys and make sure they match. On Mon, Nov 30, 2009 at 11:50 AM, Sharique uddin Ahmed Farooqui < saf...@gmail.com> wrote: > Hi, > I'm developing a CMS, in which I have 3 tables user, roles and userRoles. > Here is the code for userRoles table. on this I'm getting error > creating table (error code 1005), both userid and roleid are pkey > (int, auto increment) > > CREATE TABLE IF NOT EXISTS `mydb`.`UserRole` ( > `roleid` INT(10) UNSIGNED NOT NULL , > `userid` INT(10) UNSIGNED NOT NULL , > PRIMARY KEY (`roleid`, `userid`) , > INDEX `fk_userid` (`userid` ASC) , > INDEX `fk_roleid` (`roleid` ASC) , > CONSTRAINT `fk_userid` > FOREIGN KEY (`userid` ) > REFERENCES `mydb`.`Users` (`userid` ) > ON DELETE NO ACTION > ON UPDATE NO ACTION, > CONSTRAINT `fk_roleid` > FOREIGN KEY (`roleid` ) > REFERENCES `mydb`.`Roles` (`roleid` ) > ON DELETE NO ACTION > ON UPDATE NO ACTION) > ENGINE = InnoDB > DEFAULT CHARACTER SET = utf8 > COLLATE = utf8_general_ci; > -- > Sharique uddin Ahmed Farooqui > (C++/C# Developer, IT Consultant) > http://safknw.blogspot.com/ > "Peace" is the Ultimate thing we want. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com > > -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
