how to keep a table field value in consecutive growing order throughout delete operations?

I'm working on a table in a database which has a primary key field with numeric int value.

CREATE TABLE 'table' (
    'primary_key_field' INT NOT NULL
    'other_fields' ....
    PRIMARY KEY ( 'primary_key_field' )
)

When I insert a new element the id value is set to auto increment.

My problem is that when I delete elements and insert new ones the order of the id values is not consecutive any more but has holes in it.

ex: if I delete elements with key field value 3 开发者_Go百科and 6 the values will be:

1,2,4,5,7,..and so on..

Is there a way to keep elements in the correct succession?

ex: so that when I delete the table element with field value 3 the element with field value 4 will automatically update its value to 3 and so on for all other elements?

Thanks in advance for help!

Unless you write your own query, no mainstream RBDMS supports it.

• An autonumber/identity column should have no meaning or external value
• foreign keys would need updating
• history tables track original values: delete the new id 3 in your example there is a conflict

If you need a contiguous number for reading then you need ROW_NUMBER or a MySQL equivalent.

Is there any reason why you need to not have gaps in your primary key sequence? Other than serving to identify a record in the database uniquely, an autoincrement primary key serves no other function. There's no drawbacks to allowing gaps (unless you're worried about running out of keys, and seeing as how int gives you about 2 billion values you're unlikely to run up against that problem), and there's considerable drawbacks to trying to not allowing gaps. For example, you delete the record with ID 3 from your table, then insert a new record. This new record, because you're not allowing gaps, gets an ID of 3. Now how do you know that this is the original record, or one that's replaced another record that was deleted previously?

This problem will only be compounded if your table's primary key is being used as a foreign key somewhere else. Suppose you delete record 3 and replace it with something else, but there's also an entry in another table in your database that refers to record 3. Does that record's foreign key need to be updated to point somewhere else? Should it point to the new record 3? Should it be deleted along with the original record 3? And that's just if you have 2 tables with 1 foreign key reference. Real world databases tend to get a lot more interdependent than that very quickly.

There is simply no good reason for doing what you want to do.

You are trying to reclaim the id values. You will have to do this programmatically which is not a good idea because you will have to update the foreign keys throughout the database.

I would like to ask the fundamental question about why would you want to keep the ids continous ? Are you worried about running out of ids ? If you are, then I would suggest you change the datatype of your primary key to something like bigint.