Re: reset auto increment to a lesser value
Chris W wrote: Mark Goodge wrote: On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it with the value that you want it to be. Can you elaborate on that point? Do you not use auto-increment values to link records in a one to many relationship? Yes, but the relevant factor here is that in the table where the auto-increment value is generated it has no meaning other than as a unique id. In the other tables that use it as a reference, then it has meaning there and needs to be inserted as a known value. An auto-increment field can only be used where that value never needs to be set by reference to an external value. It can be a value that other external values are set to (such as in a one-to-many relationship), but in the other tables that use it as a reference then it isn't inserted as an auto-increment. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset auto increment to a lesser value
Mark Goodge wrote: On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it with the value that you want it to be. Mark Can you elaborate on that point? Do you not use auto-increment values to link records in a one to many relationship? Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset auto increment to a lesser value
abhishek jain wrote: On Sat, Aug 2, 2008 at 10:49 But as per a thread on this link, if the resetted value is less than the highest value already in table then the effective increment value will start from highest number and not from 100. I have deleted some rows and i want the increment to start from those row-ids. That can't be done. An auto-increment value must be higher than the highest existing value. Otherwise, it would not be an auto-increment value at all - it would be an auto-interpolate. On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it with the value that you want it to be. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset auto increment to a lesser value
On Sat, Aug 2, 2008 at 10:49 PM, Nacho Garcia <[EMAIL PROTECTED]> wrote: > http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html > > mysql> *ALTER TABLE tbl AUTO_INCREMENT = 100;* > > > > On Sat, Aug 2, 2008 at 5:28 PM, abhishek jain <[EMAIL PROTECTED]> wrote: > >> Hello friends, >> I need to reset auto increment to a lesser value, is there a metod to do >> so >> in any version of mysql. >> Pl. help me. >> Thanks >> abhi >> > > Hi Nacho, Thanks for the reply, But as per a thread on this link, if the resetted value is less than the highest value already in table then the effective increment value will start from highest number and not from 100. I have deleted some rows and i want the increment to start from those row-ids. Pl. reply, Thanks, Abhi
Re: reset auto-increment value
At 21:00 -0500 12/29/02, Carlin Anderson wrote: I have a number of databases installed under mysql 3.23 and 4.0, and will need to occasionally re-initialize certain tables. Can I re-set auto-increment fields within those tables? You can drop and re-create the tables. This will work for any table type. For ISAM tables, if you delete all the records, that will automatically reset the counter. For MyISAM tables, you can delete all the records, then use: ALTER TABLE tbl_name AUTO_INCREMENT = 1 which will reset the counter. If what you mean is that you want to leave the existing records intact, but resequence the AUTO_INCREMENT column so that the sequence doesn't have holes in it, then I'd ask: why bother? Thanks Carlin Anderson - 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: Reset Auto Increment
At 6:03 PM -0500 9/25/01, [EMAIL PROTECTED] wrote: >Hi All, > >I seen a post a few days ago on how to set the auto increment value >back to one >after deleting all the data from a table. I suppose I could just re-create the >table, but thought it be simpler to just alter the table to reset the auto >increment value back to 1. what exactly is the "alter" syntax to reset it?? If you saw a post, then the link at the bottom of each message indicates how to get to the list archive. You'll find the answer there. Often a useful strategy. In any case, the answer is: ALTER TABLE tbl_name AUTO_INCREMENT = 1 > >thx's > >mysql database > >Mike(mickalo)Blezien >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= >Thunder Rain Internet Publishing >Providing Internet Solutions that work! >http://www.thunder-rain.com >Tel: 1(225)686-2002 >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > > >- >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 -- Paul DuBois, [EMAIL PROTECTED] - 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: Reset Auto Increment
I just saw this in the manual and think it can do what you need: It's an option of the myisamchk program --set-auto-increment[=value] Force auto_increment to start at this or higher value. If no value is given, then sets the next auto_increment value to the highest used value for the auto key + 1. Hope it helps John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 26 September 2001 00:03 To: [EMAIL PROTECTED] Subject: Reset Auto Increment Hi All, I seen a post a few days ago on how to set the auto increment value back to one after deleting all the data from a table. I suppose I could just re-create the table, but thought it be simpler to just alter the table to reset the auto increment value back to 1. what exactly is the "alter" syntax to reset it?? thx's mysql database Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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