List:General Discussion« Previous MessageNext Message »
From:Sharique uddin Ahmed Farooqui Date:December 1 2009 2:59pm
Subject:Re: error creating table
View as plain text  
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 <jlyons4435@stripped> 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 <
> safknw@stripped> 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=1
>>
>>
>
>
> --
> 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.
Thread
error creating tableSharique uddin Ahmed Farooqui30 Nov
  • Re: error creating tableJim Lyons30 Nov
    • Re: error creating tableSharique uddin Ahmed Farooqui1 Dec