CODEWITHSUNDEEP
phpmysql
Aiden
Aiden

Reputation: 399

mySQL primary key oddity

I have a script which INSERT's data into a table and then later on when you INSERT new data it DELETE's the previous record/s and INSERT's the current data set.

The only issue is that the primary key gets wacked.

e.g. first four rows

1
2
3
4

then when i delete these and enter new data

5
3
4
6

note: the above numbers represent primary key id auto incrementations

Why does the incrementation become confused almost?

Upvotes: 0

Views: 35

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

If you want to restart the numbers, use truncate table instead of delete. This will reset the counter to 0:

truncate table <your table here>;

Upvotes: 0

juergen d
juergen d

Reputation: 204766

Auto-increment number do not get confused. They are unique over the table and that is the only purpose they have.

If you select the data then the DB will grab the records as fast as possible and if you do not specify a specific order then the records are returned in an unpredictable order.

That means if you specify

select * from your_table
order by id

Then the records have incrementing numbers. If you delete records then the gabs won't be filled.

Upvotes: 2

Related Questions