Page 1 of 1
how to start my PRIMARY KEY with 0 - using AUTO_INCREMENT
Posted: Wed Oct 06, 2004 9:38 am
by jasongr
Hello
I have the following table definition:
Code: Select all
CREATE TABLE `site_info` (
`SiteID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Title` VARCHAR(255) NOT NULL,
`Description` varchar(255) default '',
PRIMARY KEY (`SiteID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
When I insert a new row into the table, a new siteID is automatically generated by MySQL. The first row has the ID 1, the second 2 and so on.
My question:
How can I modify my table definition so that the first row will have the ID 0, the second will have the ID 1 and so on?
thanks in advance
Posted: Wed Oct 06, 2004 9:41 am
by feyd
feyd wrote:auto_increment is incremented each time a record is inserted. It does not count backwards, ever. You can soft reset the value using
Code: Select all
ALTER TABLE `table_name` AUTO_INCREMENT = XXX
switch out XXX for the new value you wish, if the PK used that slot, I believe it'll reset to 1 past the max you have.
As for inserting on an "empty" spot, I wouldn't bother.. Just make your PK field large enough to hold a lot of records, or don't use an auto_increment.
Posted: Wed Oct 06, 2004 9:42 am
by twigletmac
When it comes to modifying the behaviour of AUTO_INCREMENT the first thing we need to know is why you need to (sometimes there is a better solution if things are done differently).
Mac
Posted: Wed Oct 06, 2004 9:52 am
by jasongr
every site that is added to the table should have a number starting from 1.
There should be a special site in the system which is marked by the special ID 0.
I would like to add it to the table, but the auto_increment causes it to start with the value 1
Posted: Wed Oct 06, 2004 9:53 am
by feyd
you could just insert with 0 in the primary key slot.
Posted: Wed Oct 06, 2004 4:55 pm
by Roja
feyd wrote:you could just insert with 0 in the primary key slot.
Insert to row 0, even with the soft count at 0, results in the row being placed at 1.
Posted: Wed Oct 06, 2004 4:57 pm
by Roja
feyd wrote:feyd wrote:auto_increment is incremented each time a record is inserted. It does not count backwards, ever. You can soft reset the value using
Code: Select all
ALTER TABLE `table_name` AUTO_INCREMENT = XXX
switch out XXX for the new value you wish, if the PK used that slot, I believe it'll reset to 1 past the max you have.
As for inserting on an "empty" spot, I wouldn't bother.. Just make your PK field large enough to hold a lot of records, or don't use an auto_increment.
Even if you set auto_increment to -1, it sets to 0, and when its at zero, the next item you add is placed in row 1.
Posted: Wed Oct 06, 2004 4:59 pm
by Roja
jasongr wrote:every site that is added to the table should have a number starting from 1.
There should be a special site in the system which is marked by the special ID 0.
I would like to add it to the table, but the auto_increment causes it to start with the value 1
The closest I've come to working around this issue is:
- Clear the db (sets the autoinc to 0)
- Do the first row insert (automatically placed at #1)
- Update the first row to 0 (this much DOES work)
- Then add rows like normal.
Its a little obnoxious, but it does work. I haven't found a way to get the db to let me do an autoinsert that STARTS at 0. If someone else has a different solution, I'm extremely interested in hearing it!!!
Posted: Wed Oct 06, 2004 5:11 pm
by feyd
Roja wrote:feyd wrote:you could just insert with 0 in the primary key slot.
Insert to row 0, even with the soft count at 0, results in the row being placed at 1.
my bad, I haven't ever had a db data design that required a zero'd auto_increment.
not so sure the thread needed 3 consecutive posts though

Posted: Wed Oct 06, 2004 5:33 pm
by Roja
Sorry bout that, kept finding new info and testing new solutions.
Also found this out..
mysql manual wrote:
As of MySQL 4.1.1, specifying the NO_AUTO_VALUE_ON_ZERO flag for the --sql-mode server option or the sql_mode system variable allows you to store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value.
Posted: Wed Oct 06, 2004 8:47 pm
by jasongr
I ended up keeping the AUTO_INCREMENT in the table definition and simply modified the requirements.
The special site will have the reserved ID 1
All the other sites will start from ID 2 and will continue on from there
Posted: Thu Oct 07, 2004 1:08 am
by AGISB
0 value might be good for a default value. I create 1 entry and change it to 0. The next entry I add with ID set to 1 and for the next the auto-increment is in order.