List:General Discussion« Previous MessageNext Message »
From:LIU YAN Date:October 5 2009 2:03am
Subject:RE: error creating table
View as plain text  
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: safknw@stripped
> To: mysql@stripped
> 
> 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=1
> 
 		 	   		  
_________________________________________________________________
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
Thread
error creating tableSharique uddin Ahmed Farooqui4 Oct
  • RE: error creating tableJohn5 Oct
  • RE: error creating tableLIU YAN5 Oct
    • Re: error creating tableSharique uddin Ahmed Farooqui5 Oct
      • Mysql Perl DBI DBD Version Compatability for MAC OS 10.6Hagen5 Oct
        • RE: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6Gavin Towey5 Oct
          • Re: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6Hagen Finley5 Oct
            • RE: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6Gavin Towey6 Oct