CODEWITHSUNDEEP
androidsqlite
mcr619619
mcr619619

Reputation: 435

Always use "lowest possible value" for SQLite primary key?

i have a table, PERSON_TABLE, which has, P_ID, P_NAME, P_AGE

P_ID is primary key and autoincrement..

i have an activity that the user will manually put data to each columns . If success, it will have a primary key, starting from 1, then 2 , then 3 and so on.

but the application also has its delete function. If I delete row 2, then i insert again 1 row, it will have primary key 4, i would like that the next inserted data will use the first available primary key, which is 2. How can i achieve that?

thanks

Upvotes: 0

Views: 346

Answers (1)

paxdiablo
paxdiablo

Reputation: 882596

You really should re-examine this requirement - it is rarely necessary.

Since there will always be a situation where there are gaps in your sequences (unless you resequence on delete, which will be a performance killer), your app will have to deal with it.

And, since your app deals with it, it may as well deal with it all the time.

I can think of no real reason why an artificial primary key would need to be contiguous. Gaps in the sequencing won't affect performance other than for poorly written queries which use the primary key as real data (and they probably shouldn't be doing that with an artificial key).

So my advice is to start with the assumption that contiguous keys are not needed (as opposed to starting with the thought that they're required), then come up with a reason why you think they are. I think you'll find it hard to come up with a reason that can't be satisfied in a better way.

Upvotes: 3

Related Questions