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 jlyons4...@gmail.com 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 saf...@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=jlyons4...@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=arch...@jab.org
Re: error creating table
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 saf...@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=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: error creating table
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
RE: error creating table
What is the exact error you are getting? John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Sharique uddin Ahmed Farooqui [mailto:saf...@gmail.com] Sent: 04 October 2009 19:18 To: mysql Subject: error creating table 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=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.14.3/2413 - Release Date: 10/04/09 06:20:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: error creating table
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
RE: Error creating table in MySQL 5.0
Have you verified the syntax from the MySQL command line? -Original Message- From: Marvin Cummings To: [EMAIL PROTECTED] Sent: 5/9/04 7:26 PM Subject: Error creating table in MySQL 5.0 Wondering if anyone can give me some help with this error I'm getting when trying to create this table: ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = CREATE TABLE $subdivtable ( $subdivcolumn[subdiv_title] varchar(50), $su' at line 1 The syntax I'm using to create this table: ...\bin\Mysql $sql = CREATE TABLE $subdivtable ( $subdivcolumn[subdiv_title] varchar(50), $subdivcolumn[subdiv_id] int(4) DEFAULT '0' NOT NULL auto_increment, $subdivcolumn[country_id] int(4) NOT NULL, PRIMARY KEY(subdiv_id)); I'm running mysql 5.0 on a w2k3 server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error creating table in MySQL 5.0
If a column is auto_increment it can't be DEFAULT 0. PB - Original Message - From: Marvin Cummings To: [EMAIL PROTECTED] Sent: Sunday, May 09, 2004 7:26 PM Subject: Error creating table in MySQL 5.0 Wondering if anyone can give me some help with this error I'm getting when trying to create this table: ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = CREATE TABLE $subdivtable ( $subdivcolumn[subdiv_title] varchar(50), $su' at line 1 The syntax I'm using to create this table: ...\bin\Mysql $sql = CREATE TABLE $subdivtable ( $subdivcolumn[subdiv_title] varchar(50), $subdivcolumn[subdiv_id] int(4) DEFAULT '0' NOT NULL auto_increment, $subdivcolumn[country_id] int(4) NOT NULL, PRIMARY KEY(subdiv_id)); I'm running mysql 5.0 on a w2k3 server.
