auto-increment

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sirTemplar
Forum Commoner
Posts: 65
Joined: Wed Dec 18, 2002 1:57 am

auto-increment

Post by sirTemplar »

i would like to add a field on my database that i would like to auto-increment with starting value as 10001, is that possible? how? what should be the type varchar or tinyint? i already have more than 6000 data! thanx!
User avatar
puckeye
Forum Contributor
Posts: 105
Joined: Fri Dec 06, 2002 7:26 pm
Location: Joliette, QC, CA
Contact:

Re: auto-increment

Post by puckeye »

sirTemplar wrote:i would like to add a field on my database that i would like to auto-increment with starting value as 10001, is that possible? how? what should be the type varchar or tinyint? i already have more than 6000 data! thanx!
Your field will have to be at least a smallint (values up to 65535 if unsigned) or probably better as mediumint (values up to 16777215 unsigned). Tinyint has a maximum value of 256 unsigned.

To add a new field you'll have to alter your table with the following query:

Code: Select all

ALTER TABLE tbl_name ADD 'auto' MEDIUMINT(10) UNSIGNED NOT NULL AUTO_INCREMENT
I named my field "auto". Note that you can have only one auto-increment field. Now to make the field start at 10001 you'll have to create another query.

After the ALTER TABLE your new auto-increment will probably start at 0 so you sedn another query adding 10001 to that field. Something like:

Code: Select all

UPDATE tbl_name SET 'auto' + 10001
NOTE: I did not test those because I have not tables to test them onto (all my tables already have auto-increment and I don't want to play with those). Anyway I would suggest you make a copy of your current table and make tests on the copy.
sirTemplar
Forum Commoner
Posts: 65
Joined: Wed Dec 18, 2002 1:57 am

thanks

Post by sirTemplar »

thanks pal :D
User avatar
puckeye
Forum Contributor
Posts: 105
Joined: Fri Dec 06, 2002 7:26 pm
Location: Joliette, QC, CA
Contact:

Post by puckeye »

How well did the code work?
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

when you created the table you should have had

Code: Select all

CREATE TABLE test ( col1 int primary key auto_increment not null,
                             col2 text                    
                            )
AUTO_INCREMENT=10001
this would have made your first value 10001
User avatar
puckeye
Forum Contributor
Posts: 105
Joined: Fri Dec 06, 2002 7:26 pm
Location: Joliette, QC, CA
Contact:

Post by puckeye »

Wayne wrote:when you created the table you should have had

Code: Select all

CREATE TABLE test ( col1 int primary key auto_increment not null,
                             col2 text                    
                            )
AUTO_INCREMENT=10001
this would have made your first value 10001
Yes you are right but how do you ADD a field with an autoincrement starting at 10001?
Post Reply