Thanks Liu, This type mismatch issue.
On 10/5/09, LIU YAN <liuy...@live.com> wrote: > > 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 -- 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
