Liquid Fire Posted October 31, 2007 Share Posted October 31, 2007 i know that i can only auto increment the primary key but is there a way to auto increment another field that is not the primary key? we are trying to auto increment a field through php code but we are getting duplicates still and trying to find a way to do it in mysql directly. our database type does not support locking tables or else we would just do that. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted October 31, 2007 Share Posted October 31, 2007 Why do you want to auto-increment two fields? Wouldn't the first one have the same value as the second one? Quote Link to comment Share on other sites More sharing options...
Liquid Fire Posted October 31, 2007 Author Share Posted October 31, 2007 well the TableID field(the primary key) is used to link some table to this rows record however we also have a CustomerID field which needs to be unique and automatically created by auto increment. this table i am working with is quite old and instead on re building it properly(which would take forever since we would need to rewrite the entire database and also all the code the queries that database) we have to continue to use this system until a new one is implemented. The CustomerID field will not be the same because if was created at a later date. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted October 31, 2007 Share Posted October 31, 2007 Shouldn't the customerID field hold the customers ID from another table which is already unique? I don't know what the use would be to just put in any unique ID for that field....doesn't make any sense. But to answer your initial question, no...I'm pretty sure you can only have 1 auto-incremented field per table. You may be able to use mysql_insert_id() to get what you want done. Quote Link to comment Share on other sites More sharing options...
Liquid Fire Posted October 31, 2007 Author Share Posted October 31, 2007 we are using another table to generate that id number however we are still getting duplicates. the only what i can think is the between the time one insert happens and then getting the new insert id, another insert happens. i know the chance are low but that ii the only thing that can be happening and locking the table is the only solution we can think on but MyISAM does not support that as far as i know. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted October 31, 2007 Share Posted October 31, 2007 On the page that inserts the info, do a check to see if that row already exists in the DB...and give an error if it's already there. Once you do that, it should completely solve the problem. Quote Link to comment Share on other sites More sharing options...
Liquid Fire Posted October 31, 2007 Author Share Posted October 31, 2007 I can't do that because i would be giving customers errors and part some customers on that reservation they booked with exist so adding to the reservation is impossible for the customer. is it possible to switch a database from MyISAM to InnoDB without any issues? Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted October 31, 2007 Share Posted October 31, 2007 is it possible to switch a database from MyISAM to InnoDB without any issues? I can't help you with that question...I have no idea, I've never had to do it. Quote Link to comment Share on other sites More sharing options...
aschk Posted November 1, 2007 Share Posted November 1, 2007 I believe that converting between the two database engines will cause any FULLTEXT indexes to be dropped. However I don't believe changing MySQL engines is going to resolve your situation. It sounds like your data is not normalised properly. Please post the DESCRIBE TABLE <tablename> output and some sample input for your database and we'll help to you to define a structure that WILL work. Quote Link to comment Share on other sites More sharing options...
fenway Posted November 1, 2007 Share Posted November 1, 2007 we are using another table to generate that id number however we are still getting duplicates. the only what i can think is the between the time one insert happens and then getting the new insert id, another insert happens. i know the chance are low but that ii the only thing that can be happening and locking the table is the only solution we can think on but MyISAM does not support that as far as i know. You can't get duplicates with an auto-increment column... ever. There's something else going on. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.