hi,
I run your code , but worked propertly. I suggested to check the table USERS ,
ROLES, is the column userid / roleid same data type (INT(10) UNSIGNED ) with
your userroles table ?
======================================================
mysql> create table users (userid INT(10) UNSIGNED primary key);
Query OK, 0 rows affected (0.06 sec)
mysql> create table roles (roleid INT(10) UNSIGNED primary key);
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE `userroles` (
-> `roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
-> `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
-> PRIMARY KEY (`roleid`, `userid`),
-> INDEX `FK1_user` (`userid`),
-> CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users`
-> (`userid`) ON UPDATE CASCADE ON DELETE CASCADE,
-> CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles`
-> (`roleid`) ON UPDATE CASCADE ON DELETE CASCADE
-> )
-> COLLATE=utf8_general_ci
-> ENGINE=InnoDB
-> ROW_FORMAT=COMPACT
-> AVG_ROW_LENGTH=0;
Query OK, 0 rows affected (0.08 sec)
mysql>
======================================================
best regards
liuyann
> Date: Sun, 4 Oct 2009 23:47:54 +0530
> Subject: error creating table
> From: saf...@gmail.com
> To: mysql@lists.mysql.com
>
> Hi,
> I'm trying to create a table with 2 columns both are primary key
> (combined) and both are foreign key as well. I'm getting error cannot
> create table. Here is the sql
>
> CREATE TABLE `userroles` (
> `roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
> `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
> PRIMARY KEY (`roleid`, `userid`),
> INDEX `FK1_user` (`userid`),
> CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users`
> (`userid`) ON UPDATE CASCADE ON DELETE CASCADE,
> CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles`
> (`roleid`) ON UPDATE CASCADE ON DELETE CASCADE
> )
> COLLATE=utf8_general_ci
> ENGINE=InnoDB
> ROW_FORMAT=COMPACT
> AVG_ROW_LENGTH=0
>
> --
> 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=liuy...@live.com
>
_________________________________________________________________
Windows Live: Keep your friends up to date with what you do online.
http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_1:092010
