Why does auto increment not take into account deleted rows?
I have one column as an auto increment for adding numbers: 1 2 3 4 5 If i delete row 3 then add a new row and view the results i get: 1 2 4 5 6 As you can see 3 has been deleted and its now added 6! Is this normal?. How can i get it to always display numbers in this column in sequence. TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does auto increment not take into account deleted rows?
That is normal and you do NOT want it to display numbers in sequence. Lets say I use auto-increment column for primary key, lets also say that I made a mistake while coding and deleted someone's account (account #3) from the account table, but forgot to delete the corresponding entry from the balance table (where account 3's balance of $1,000,000 was not deleted when the account was closed). If the auto_increment went back and re-used deleted values, then the next time someone opens an account they get a free $1,000,000! Now that is an extreme case, but my point is simple; re-using auto_increment values can cause all kinds of trouble. Besides, an unsigned BIGINT can store so much data there is really no need to get into auto_increment reuse. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Daniel Crompton [mailto:[EMAIL PROTECTED] Sent: Monday, June 02, 2003 10:19 AM To: [EMAIL PROTECTED] Subject: Why does auto increment not take into account deleted rows? I have one column as an auto increment for adding numbers: 1 2 3 4 5 If i delete row 3 then add a new row and view the results i get: 1 2 4 5 6 As you can see 3 has been deleted and its now added 6! Is this normal?. How can i get it to always display numbers in this column in sequence. TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does auto increment not take into account deleted rows?
I have one column as an auto increment for adding numbers: 1 2 3 4 5 If i delete row 3 then add a new row and view the results i get: 1 2 4 5 6 As you can see 3 has been deleted and its now added 6! Is this normal?. How can i get it to always display numbers in this column in sequence. TIA I'm assuming you inserted a new record after deleting record 3 and are wondering why it didn't reuse '3'. Yes. Auto-increment columns are frequently used to link tables in a relationship. If you reused '3' the associated records would think the originial record was still there not that a new one had been substituted. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does auto increment not take into account deleted rows?
Daniel Crompton [EMAIL PROTECTED] wrote: I have one column as an auto increment for adding numbers: 1 2 3 4 5 If i delete row 3 then add a new row and view the results i get: 1 2 4 5 6 As you can see 3 has been deleted and its now added 6! Is this normal?. How can i get it to always display numbers in this column in sequence. Yes, it's normal. It's how auto_increment works on MyISAM and InnoDB tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does auto increment not take into account deleted rows?
Daniel, 'auto_increment' won't do that job for you and maybe it's not the right way to think about it anyway. You really only want to use 'auto_increment' for records that need to maintain serial numbers. If you are deleting records and wanting to use the 'deleted' number then your 'serial number' is not really associated with the record. Best wishes, Paul Najman [EMAIL PROTECTED] - Original Message - From: Daniel Crompton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 02, 2003 5:18 PM Subject: Why does auto increment not take into account deleted rows? I have one column as an auto increment for adding numbers: 1 2 3 4 5 If i delete row 3 then add a new row and view the results i get: 1 2 4 5 6 As you can see 3 has been deleted and its now added 6! Is this normal?. How can i get it to always display numbers in this column in sequence. TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
