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
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=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
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=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
error creating table
Hi, I'm trying to create a table with 2 foreign keys from two tables, but it not workin here is the query ( i have generated this query from mysql workbench) -- CREATE TABLE IF NOT EXISTS `menutest`.`role_perm` ( `idrole` INT NOT NULL , `permid` VARCHAR(45) NOT NULL , `status` BOOLEAN NOT NULL DEFAULT 1 , INDEX fk_role (`idrole` ASC) , INDEX fk_perm (`permid` ASC) , CONSTRAINT `fk_role` FOREIGN KEY (`idrole` ) REFERENCES `menutest`.`role` (`idrole` ) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_perm` FOREIGN KEY (`permid` ) REFERENCES `menutest`.`permission` (`idpermission` ) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE = InnoDB; -- here is the error mssage :Can't create table '.\menutest\role_perm.frm' (errno: 150) -- 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/[EMAIL PROTECTED]
parse error creating table
I have been trying to create a table but mysql 5.0.15-nt-max is having a problem parsing the statement. Anyone know what the problem is in the syntax of the following table creation statement: CREATE TABLE registration_and_attendance ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, sf182_receivedBOOLEAN NOT NULL DEFAULT TRUE, registrant_email_address TEXT, cc_email_list TEXT, bureau_id INTEGER REFERENCES bureaus(id), office TEXT, class_id INTEGER NOT NULL REFERENCES classes(id), schedule_id INTEGER REFERENCES schedules(id), start_date DATE, end_date DATE, enrolled BOOLEAN, attendedBOOLEAN, completed BOOLEAN, cancelledBOOLEAN DEFAULT FALSE, cancelled_commentsTEXT, comments TEXT, email_confirmation_sent BOOLEAN NOT NULL, employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updated TEXT, waitlisted BOOLEAN DEFAULT FALSE, overflow_registrantBOOLEAN DEFAULT FALSE, attach_hotel_listing_directions BOOLEAN, instructor_legacy TEXT, time_legacy TIME WITHOUT TIME ZONE, ssn_legacy TEXT, position_grade_title TEXT, office_phone_legacy TEXT, contractor_legacy BOOLEAN, no_show_legacy BOOLEAN, status_legacy TEXT, funding_id INTEGER REFERENCES funding_types(id), PRIMARY KEY (firstname, lastname, class_id, start_date, end_date) ); I get the following error message with the above statement but I can't figure out what the problem is: 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 'NOT N ULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, l' at line 23 Thanks, Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: parse error creating table
Ferindo One problem is: employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, NOT NULL should be before REFERENCES. Also, in: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, (i) NOT NULL is superfluous since the default is given by CURRENT_TIMESTAMP. (ii) specifying DEFAULT CURRENT_TIMESTAMP defeats auto-resetting of the timestamp on updates. Is that what you want? To get auto-setting on INSERTs and UPDATEs, just write last_updated TIMESTAMP, Also the manual doesn't mention TIME WITHOUT TIME ZONE. Are you thinking of PostgreSQL? PB - Ferindo Middleton Jr wrote: I have been trying to create a table but mysql 5.0.15-nt-max is having a problem parsing the statement. Anyone know what the problem is in the syntax of the following table creation statement: CREATE TABLE registration_and_attendance ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, sf182_receivedBOOLEAN NOT NULL DEFAULT TRUE, registrant_email_address TEXT, cc_email_list TEXT, bureau_id INTEGER REFERENCES bureaus(id), office TEXT, class_id INTEGER NOT NULL REFERENCES classes(id), schedule_id INTEGER REFERENCES schedules(id), start_date DATE, end_date DATE, enrolled BOOLEAN, attendedBOOLEAN, completed BOOLEAN, cancelledBOOLEAN DEFAULT FALSE, cancelled_commentsTEXT, comments TEXT, email_confirmation_sent BOOLEAN NOT NULL, employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updated TEXT, waitlisted BOOLEAN DEFAULT FALSE, overflow_registrantBOOLEAN DEFAULT FALSE, attach_hotel_listing_directions BOOLEAN, instructor_legacy TEXT, time_legacy TIME WITHOUT TIME ZONE, ssn_legacy TEXT, position_grade_title TEXT, office_phone_legacy TEXT, contractor_legacy BOOLEAN, no_show_legacy BOOLEAN, status_legacy TEXT, funding_id INTEGER REFERENCES funding_types(id), PRIMARY KEY (firstname, lastname, class_id, start_date, end_date) ); I get the following error message with the above statement but I can't figure out what the problem is: 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 'NOT N ULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, l' at line 23 Thanks, Ferindo -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.11/191 - Release Date: 12/2/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: parse error creating table
Thanks Peter. I did originally use this table in a Postgresql db. Thanks for you advice. Your suggestions below allowed me to create this table and I learned a thing t two about proper usage of the TIMESTAMP data type. The intended effect is to get a timestamp field that inserts the current system time on inserts and continues to update the field with the current timestamp on updates without the application or use needing to specify it are you saying that the timestamp attribute alone will do that? Ferindo Peter Brawley wrote: Ferindo One problem is: employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, NOT NULL should be before REFERENCES. Also, in: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, (i) NOT NULL is superfluous since the default is given by CURRENT_TIMESTAMP. (ii) specifying DEFAULT CURRENT_TIMESTAMP defeats auto-resetting of the timestamp on updates. Is that what you want? To get auto-setting on INSERTs and UPDATEs, just write last_updated TIMESTAMP, Also the manual doesn't mention TIME WITHOUT TIME ZONE. Are you thinking of PostgreSQL? PB - Ferindo Middleton Jr wrote: I have been trying to create a table but mysql 5.0.15-nt-max is having a problem parsing the statement. Anyone know what the problem is in the syntax of the following table creation statement: CREATE TABLE registration_and_attendance ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, sf182_receivedBOOLEAN NOT NULL DEFAULT TRUE, registrant_email_address TEXT, cc_email_list TEXT, bureau_id INTEGER REFERENCES bureaus(id), office TEXT, class_id INTEGER NOT NULL REFERENCES classes(id), schedule_id INTEGER REFERENCES schedules(id), start_date DATE, end_date DATE, enrolled BOOLEAN, attendedBOOLEAN, completed BOOLEAN, cancelledBOOLEAN DEFAULT FALSE, cancelled_commentsTEXT, comments TEXT, email_confirmation_sent BOOLEAN NOT NULL, employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updated TEXT, waitlisted BOOLEAN DEFAULT FALSE, overflow_registrantBOOLEAN DEFAULT FALSE, attach_hotel_listing_directions BOOLEAN, instructor_legacy TEXT, time_legacy TIME WITHOUT TIME ZONE, ssn_legacy TEXT, position_grade_title TEXT, office_phone_legacy TEXT, contractor_legacy BOOLEAN, no_show_legacy BOOLEAN, status_legacy TEXT, funding_id INTEGER REFERENCES funding_types(id), PRIMARY KEY (firstname, lastname, class_id, start_date, end_date) ); I get the following error message with the above statement but I can't figure out what the problem is: 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 'NOT N ULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, l' at line 23 Thanks, Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: parse error creating table
Hi Ferindo The intended effect is to get a timestamp field that inserts the current system time on inserts and continues to update the field with the current timestamp on updates without the application or use needing to specify it are you saying that the timestamp attribute alone will do that? Yep. Try... create table ts(i int,ts timestamp); insert into ts values(1,null); select * from ts; +--+-+ | i| ts | +--+-+ |1 | 2005-12-04 17:21:01 | +--+-+ update ts set i=2; select * from ts; +--+-+ | i| ts | +--+-+ |2 | 2005-12-04 17:21:13 | +--+-+ PB http://www.artfulsoftware.com - Ferindo Middleton Jr wrote: Thanks Peter. I did originally use this table in a Postgresql db. Thanks for you advice. Your suggestions below allowed me to create this table and I learned a thing t two about proper usage of the TIMESTAMP data type. The intended effect is to get a timestamp field that inserts the current system time on inserts and continues to update the field with the current timestamp on updates without the application or use needing to specify it are you saying that the timestamp attribute alone will do that? Ferindo Peter Brawley wrote: Ferindo One problem is: employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, NOT NULL should be before REFERENCES. Also, in: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, (i) NOT NULL is superfluous since the default is given by CURRENT_TIMESTAMP. (ii) specifying DEFAULT CURRENT_TIMESTAMP defeats auto-resetting of the timestamp on updates. Is that what you want? To get auto-setting on INSERTs and UPDATEs, just write last_updated TIMESTAMP, Also the manual doesn't mention TIME WITHOUT TIME ZONE. Are you thinking of PostgreSQL? PB - Ferindo Middleton Jr wrote: I have been trying to create a table but mysql 5.0.15-nt-max is having a problem parsing the statement. Anyone know what the problem is in the syntax of the following table creation statement: CREATE TABLE registration_and_attendance ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, sf182_receivedBOOLEAN NOT NULL DEFAULT TRUE, registrant_email_address TEXT, cc_email_list TEXT, bureau_id INTEGER REFERENCES bureaus(id), office TEXT, class_id INTEGER NOT NULL REFERENCES classes(id), schedule_id INTEGER REFERENCES schedules(id), start_date DATE, end_date DATE, enrolled BOOLEAN, attendedBOOLEAN, completed BOOLEAN, cancelledBOOLEAN DEFAULT FALSE, cancelled_commentsTEXT, comments TEXT, email_confirmation_sent BOOLEAN NOT NULL, employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updated TEXT, waitlisted BOOLEAN DEFAULT FALSE, overflow_registrantBOOLEAN DEFAULT FALSE, attach_hotel_listing_directions BOOLEAN, instructor_legacy TEXT, time_legacy TIME WITHOUT TIME ZONE, ssn_legacy TEXT, position_grade_title TEXT, office_phone_legacy TEXT, contractor_legacy BOOLEAN, no_show_legacy BOOLEAN, status_legacy TEXT, funding_id INTEGER REFERENCES funding_types(id), PRIMARY KEY (firstname, lastname, class_id, start_date, end_date) ); I get the following error message with the above statement but I can't figure out what the problem is: 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 'NOT N ULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, l' at line 23 Thanks, Ferindo -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.11/191 - Release Date: 12/2/2005 -- MySQL General Mailing List For list archives:
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]
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.
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.
InnoDB: Error creating table
Encounter the following error in MyCC: [root@localhost:3306]ERROR 1005: Can't create table '.\abc\def.frm' (errno 121) I'm using MySQL 3.23.49 and Win98se. The statement I used work on the default MYISAM table and DBD table and I did changed the TYPE=InnoDB in the CREATE statement. The following is part of the my.ini settings: [mysqld] innodb_data_home_dir=c:\ibdata innodb_data_file_path=ibdata1:50M set-variable=innodb_buffer_pool_size=40M set-variable=innodb_additional_mem_pool_size=5M innodb_log_group_home_dir=c:\iblogs innodb_log_arch_dir=c:\iblogs innodb_log_archive=0 set-variable=innodb_log_files_in_group=3 set-variable=innodb_log_file_size=5M set-variable=innodb_log_buffer_size=4M innodb_flush_log_at_trx_commit=1 set-variable=innodb_file_io_threads=4 set-variable=innodb_lock_wait_timeout=50 Thank you __ Do You Yahoo!? Yahoo! Messenger - Instant Messaging, Instant Gratification. (Now with new emoticons!) http://messenger.yahoo.com.sg/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
