Hi Jim, Only difference is auto increment in the field. You cannot have two auto increment in a single table also auto increment field must be the key.
On 11/30/09, Jim Lyons <jlyons4...@gmail.com> wrote: > 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 > -- 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=arch...@jab.org
