sql - Dropping last record with autoincrementing primary key -

suppose have database auto-incrementing primary key , 25 records (pk goes 1 25). delete record pk=25; pk values go 1-24. add new record. pk value 25.

this quite problematic if other tables used value 25 foreign key. would linked erroneous record.

how issue customarily addressed?

in databases, once allocated, auto increment value not reused if original row deleted.

there exception in sqlite if rely on built-in rowid value rather declaring column autoincrement. in second case, things work expect (values never recycled). in first case, however, in situation describe (deletion of highest-numbered row), value will recycled.

but consider in case describe rowid used primary key , all other references protected enforced foreign key relationship then, in order delete record value 25, references must first removed database.

the problem occurs when have non-enforced references primary key value.

nonetheless, think it's best practice explicitly declare primary key autoincrement. counter-argument can read here: http://www.sqlite.org/autoinc.html.