php - MySql adaptive IDs -

i have table set auto increasing id. lets have ids 1, 2, 3, 4 , 5. when remove id number 3, want id 4 drop down 3, , id 5 drop down 4.

is possible? how done?

i think want keep record ids in order , don't want holes between them :)

that of course possible should keep away solution that. if you, later on, begin relating tables 1 another, you'll regret idea , crazy keeping track of tables' foreign ids.

really, not bad thing having holes between records. also, how actual db logic works. when id used, it's done. won't use again. when it's not there, you'll understand it's gone. databases matter keep track of last used number (for id column) , auto increment not last existing record's id, instead last used record's id.

in short, can. won't want it.

i deliberately didn't explain how it's done. before wanted make sure op wants.

as op's comment it want :) there no other tables connected it. can think of simple solution won't require any database interactions, becouse op taking responsibility now.

create table without auto-incremented id field. id field should type int. when you're creating record, last id table ordering ids desc (from bigger lower) , top 1 it. sample query request:

select top 1 id table order id desc 

use result + 1 in new insert query.

when delete value should iterate on records , update ids 1 one in loop. means, if have thousands of records, , not using inner functions of db system (here can't because you're using own logic), can take while (you've been warned!). , is, if don't have relations other tables. if do, have repeat iteration related tables in order keep foreign id relationships (which not maintained db anymore). because, defying db's inner logic, took control hands , it's responsibility track , modify own. did mention cannot use identity column in of related tables because won't let change ids ids don't exist in it's related table?

as see, painful proccess should avoid. until understand going on, things may not make sense @ all. believe me, have been there. these systems build logic behind it. no need reinvent wheel. when telling not go there, might want think it. :)

good luck!

edit: when deleting records, should use server side code , update each row. here logic. total count of table rows. create global variable outside loop , count requests incrementing 1 inside loop. begin loop (which has length of total count of rows). start bottom (from first row has id of 1). control every row if there null result requests. if there null, skip , go next. until find real record. when it, update it's id global variable created outside loop sending insert query db. loop finish every row logic , it's done.

i don't ever write these kind of detailed answers won't else op. you're lucky. read carefuly, , don't forget you're on own on.