RE: [PHP-WIN] SQL-query

I'd fire any person working for me who was writing their own code to handle incrementing a unique ID field. If you have to insert a record after deleting one, with the same id, you have some serious relationship issues in your data. And if you _REALLY_ have to, then lock the data, turn auto increment off, insert the record, turn auto increment back on, and unlock the data Yes, you can disable auto increment fields for mass inserts, and then re-enable them after. -----Original Message----- From: Cam Dunstan [mailto:cam@norex.com.au] Sent: Saturday, 18 January 2003 10:56 AM To: php-windows@lists.php.net Subject: Re: [PHP-WIN] SQL-query Amen to that - I really believe you are better off (if you have the freedom to do so) changing autoincrement fields to an integer type and managing the incrementing yourself. In addition to the problem Dash mentions there is yet another "gotcha" - if you are ever faced with having to combine two tables of existing data, each with an autoincrement fields you have a serious problem on your hands and a lot of sweat work to change all the related tables before you combine them. If I was Prime Minister I would immediately pass legislation outlawing autoincrement fields ( my chances are not good however). ----- Original Message ----- From: "Dash McElroy" <php@l1te.net> To: "Piotr Pluciennik" <ppluciennik@yahoo.com> Cc: <php-windows@lists.php.net> Sent: Saturday, January 18, 2003 4:54 AM Subject: Re: [PHP-WIN] SQL-query > Piotr is right. SQL shouldn't let you do that, if only for possible > consistency errors. Imagine if you had id 3 referenced in some other table > and then you put new data in the primary table with id of 3. Then all the > other stuff referring to 3 would then refer to the new data. Not a good > thing. > > I've been digging through an SQL book lately... some mind bending (but > extremely logical) information. > > -Dash > > Don't look back, the lemmings are gaining on you. > > On Fri, 17 Jan 2003, Piotr Pluciennik wrote: > > > Hi, > > > > I don't use MYSQL, but the problem is common for > > databases. One of the idea of auto increment field is > > to have unique identifiers, so you can't insert a > > record with auto increment fields set by yourself. > > It's always done by DB. So you can't fill a "hole" in > > numbering after deleting record. Of course in a normal > > way of using DB... :-) > > > > HTH > > > > Piotr > > > > --- Bobo Wieland <dev@elstudion.com> wrote: > > > I hope this isn't to much OT... Sorry if it is... > > > > > > In my MySQL-db that I use for a php-driven site I > > > have many tables with the > > > Primary Key set as an auto increment value. > > > > > > If I have 4 records with id 1, 2, 3 and 4 and then > > > delete number 3 and add > > > one more record I get the id's 1,2,4 and 5. It isn't > > > much of a problem, but > > > it would be nice to check for the first "hole" in > > > the id's and insert the > > > record there instead. How would you do this? is > > > there some simple way from > > > either MySQl or PHP that doesn't take to much time > > > to execute? > > > > > > thanks in advance > > > > > > > > > . bobo . www.elstudion.com . dev@elstudion.com . > > > > > > > > > > > > > > > -- > > > PHP Windows Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > __________________________________________________ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > http://mailplus.yahoo.com > > > > -- > > PHP Windows Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > -- > PHP Windows Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Windows Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php