Re: Primary key ID gets messed up after deleting/adding
When I first added about 10 records and then deleted a few records and then added more records it didn't replace the missing records. That's MySQL's usual behaviour. auto_increment only garantees to create unique keys. "Holes" left by deleting data are not filled. Jens - 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
Re: Primary key ID gets messed up after deleting/adding
I can understand not wanting to have holes, but.. If it is a primary key, how are you going to handle updating tables that rely on that key id? If your id numbers were to shift, like you want, and you had a related sub table (one to many) and this was say, an shopping cart, then all of a sudden customer A's line items are showing up in Customer B's and not customer A's It is more common to to have reliable unique no shifting keys. You can program in some logic that deletes all related records in related tables, and then re-uses numbers if you want to 'fill' the holes, butis it really worth it? Holes are merely an aesthetic item in the vast majority of cases. Auto_increment behavior is clearly documented in the manual. On 4/8/01 9:35 AM, "David" [EMAIL PROTECTED] wrote: I have a primary key listed as ID When I first added about 10 records and then deleted a few records and then added more records it didn't replace the missing records. When I select all records to view I now get: 1 2 why didn't it go to 3, 4 and 5 after 2? 6 7 8 When I add a record it becomes record 11. Am I doing something wrong when I delete a record? I thought it would move the other records down 6 and 7 would be 3 and 4 etc... Using RedHat Linux with latest MySQL rpms: mysql-server-3.23.32-1.7 php-mysql-4.0.4pl1-3 mysql-devel-3.23.32-1.7 mysqlclient9-3.23.22-3 mysql-3.23.32-1.7 - 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 - 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
Re: Primary key ID gets messed up after deleting/adding
How would you actually overcome that? Wouldn't it be good if MySQL would be adapted to actually do this for you? -- Kobus "Jens Vonderheide" [EMAIL PROTECTED] 2001-04-08 19:25:48 When I first added about 10 records and then deleted a few records and then added more records it didn't replace the missing records. That's MySQL's usual behaviour. auto_increment only garantees to create unique keys. "Holes" left by deleting data are not filled. Jens - 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 - 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
Re: Primary key ID gets messed up after deleting/adding
No, what is meant here is that: He has record 1 2 3 and 4. Now deletes 2 and 3. Now create 2 new records. MySQL creates them as 5 and 6, instead of 2 and 3 again... --Kobus Lindsay Adams [EMAIL PROTECTED] 2001-04-08 19:42:12 I can understand not wanting to have holes, but.. If it is a primary key, how are you going to handle updating tables that rely on that key id? If your id numbers were to shift, like you want, and you had a related sub table (one to many) and this was say, an shopping cart, then all of a sudden customer A's line items are showing up in Customer B's and not customer A's It is more common to to have reliable unique no shifting keys. You can program in some logic that deletes all related records in related tables, and then re-uses numbers if you want to 'fill' the holes, butis it really worth it? Holes are merely an aesthetic item in the vast majority of cases. Auto_increment behavior is clearly documented in the manual. On 4/8/01 9:35 AM, "David" [EMAIL PROTECTED] wrote: I have a primary key listed as ID When I first added about 10 records and then deleted a few records and then added more records it didn't replace the missing records. When I select all records to view I now get: 1 2 why didn't it go to 3, 4 and 5 after 2? 6 7 8 When I add a record it becomes record 11. Am I doing something wrong when I delete a record? I thought it would move the other records down 6 and 7 would be 3 and 4 etc... Using RedHat Linux with latest MySQL rpms: mysql-server-3.23.32-1.7 php-mysql-4.0.4pl1-3 mysql-devel-3.23.32-1.7 mysqlclient9-3.23.22-3 mysql-3.23.32-1.7 - 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 - 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 - 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
Re: Primary key ID gets messed up after deleting/adding
How would you actually overcome that? Wouldn't it be good if MySQL would be adapted to actually do this for you? I think that not reusing deleted numbers is easier (i.e. more efficient). IIRC, earlier versions of MySQL in fact reused the numbers. There are 2 ways to overcome this: 1) Check if you really need to rely on numbers without any gaps. If you tell us what you want to do, someone on the list may come up with a different approach. 2) If you really need that behaviour, you shouldn't use auto_increment, but write your own functions to get a unique key. I did this once (because I needed to support some RDBMSs without auto_increment). It's not that difficult. Jens - 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
Re: Primary key ID gets messed up after deleting/adding
How I learned to love number gaps: I have a database of colleges and universities. Every degree listing as a numbered id. This used to be auto-incremented. After several deletes and additions, I found it advantageous to have gaps between schools to add new degree listings, so that I didn't get a numbering scenario like School A has degree #s 4, 5, 89, 326 and School B has #s 6, 7 8, 88, 91, 214, etc. If I have gaps, I have room to keep a school's degrees together in a sequence. It makes it so much easier to keep track. Gaps are only a subjective problem. Objectively, they make no difference to the database operation, and administratively, they can be quite useful. When we look past our expectations, we often find new posibilities. On 8 Apr 2001, at 20:37, Jens Vonderheide wrote: How would you actually overcome that? Wouldn't it be good if MySQL would be adapted to actually do this for you? I think that not reusing deleted numbers is easier (i.e. more efficient). IIRC, earlier versions of MySQL in fact reused the numbers. There are 2 ways to overcome this: 1) Check if you really need to rely on numbers without any gaps. If you tell us what you want to do, someone on the list may come up with a different approach. 2) If you really need that behaviour, you shouldn't use auto_increment, but write your own functions to get a unique key. I did this once (because I needed to support some RDBMSs without auto_increment). It's not that difficult. Jens - 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 John Jensen 520 Goshawk Court Bakersfield, CA 93309 661-833-2858 - 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
