CODEWITHSUNDEEP
mysqlauto-increment
Mahdi_Nine
Mahdi_Nine

Reputation: 14751

problem with auto_increment()

I have a table that have two fields.

table test
{
fname char(20),
id int not null auto_increment,
primary key(id)
}

now I add 3 records to the table like below:

insert into test(fname) values
('a'),('b'),('c');

and the table looks like

fname  id
a       1
b       2
c       3

now I delete b from table so I have:

fname id
a      1
b      3

now again I insert a new record into the table

insert into test(fname) values('d);

and get:

fname id 
a      1
b      3
d      4

but I want last record's id to be "2"

how can I do this?

Upvotes: 0

Views: 156

Answers (3)

evilone
evilone

Reputation: 22740

An auto increment column would be used to identify your rows as unique if you have no other candidate for a primary key. If you are relying on their being no gaps in your sequence then you have trouble with the logic of how you are approching the problem, your queries should not rely on anything other than them being unique.

Also find a piece of MySQL Cookbook chapter that says the same

Upvotes: 3

Wikeno
Wikeno

Reputation: 419

You can't do that with autoincrement. It only keeps the id of the last inserted record and increments it when you insert. It doesn't keep track of delete operations.

Anyway, why do you want to do it?

Upvotes: 0

sanders
sanders

Reputation: 10898

I don't think you can change that. This is houw auto_increment works in mysql.

Upvotes: 0

Related Questions