Re: How to create a solid sequence?

[Peter Brawley]

> Is it possible with auto_increment?

No, there is no practical way to guarantee seqentiality with auto_increment PKs, because there is no way to guarantee that no insert will ever fail or that no row will never need to be deleted.

How to guarantee sequentiality depends on how tight your requirement is, and in any case is more difficult in MySQL than in other RDBMSs because MySQL does not implement Check Constraint. If there must never be a gap, then you must prevent deletion and key edits. If the requirement is only that gaps should be filled before new key values are assigned, then write a Trigger which does that under transactional control, but if it is to be effective, you need total control of who can modify the table.

PB