CODEWITHSUNDEEP
mysqlprimary-key
SamIAm
SamIAm

Reputation: 2489

Primary Key Int in MySql

I created a table:

create table IDTable(
keyId int not null auto_increment,
primaryId varchar(50) not null,
issueDate date,
expiryDate date,
comments varchar(255),
primary key (keyIid)
);

When I insert into the table, the keyId will increment by one but when I delete a row, the next insert doesn't fill up the deleted keyID. So for example if i delete a row with keyid =3, the next insert statement will not insert the row with a keyID of 3. Is there a way to set it up so the keyID is generated based on what's missing and not just by an autoincrement?

Upvotes: 0

Views: 135

Answers (2)

dot_Sp0T
dot_Sp0T

Reputation: 399

As already stated, you do not want to do that due to confusion and inconsistency.

see: How to handle fragmentation of auto increament ID column in MySQL

For further details. It's explained pretty neatly there.

Upvotes: 0

juergen d
juergen d

Reputation: 204924

No, please don't try to mess with the auto-increment key!

If you need a rank of some sort then use another column. You could use a timestamp to save the order when the record was inserted if that is what you want.

Upvotes: 2

Related Questions