error creating table

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=gcdmg-mysql-2@m.gmane.org

Re: error creating table

--0016e6d785641d312f04799a7fcb
Content-Type: text/plain; charset=ISO-8859-1

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@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=jlyons4435@gmail.com
>
>


--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--0016e6d785641d312f04799a7fcb--

Re: error creating table

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 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@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=jlyons4435@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=gcdmg-mysql-2@m.gmane.org