-1

I have a database with multiple tables. One table is called "users". An example of the data is 

0 John Black
1 Joe Bloggs
2 Jane Doe

If I perform a delete on row where id = 1 the data becomes:

0 John Black
2 Jane Doe

What SQL would I write to alter the further records to decrement the ID, so that the data becomes:

0 John Black
1 Jane Doe

Note: There are other tables with related data so e.g employee details may have address and phone number linked via the ID column, but I assume this would also change, as ON UPDATE is set to "cascade"

Sled
  • 18,541
  • 27
  • 119
  • 168
Wayneio
  • 3,466
  • 7
  • 42
  • 73
  • 6
    probably not a good idea – Drew Nov 21 '12 at 15:38
  • Why do you need to have gapless ids? – juergen d Nov 21 '12 at 15:40
  • you would be reshuffling your whole database. if you dont have cascading set everywhere you might orphan records not to mention the unnecessary thing of doing it anywat – Drew Nov 21 '12 at 15:40
  • 1
    plus Jane Doe is user or customer #2. probably printed on documents – Drew Nov 21 '12 at 15:42
  • 2
    You'll completely trash your references to User ID 1 if you do this. Suddenly Jane Doe will be linked in place of Joe Bloggs. If you miss one of these by accident, suddenly you've exposed some of Joe's data to Jane. Generally IDs, once assigned, should *never* be changed unless you have an exceptionally good reason. OCD does not qualify. – tadman Nov 21 '12 at 15:44
  • I don't see any positive reason in doing that, are you afraid of consume every id? Don't worry, DBs manage that for you. – juankysmith Nov 21 '12 at 15:44
  • funny ocd was the first thing i thought of – Drew Nov 21 '12 at 15:49
  • Also, you will not run out of ids, if you do then just make the type larger or make a key out of two columns. – Sled Nov 21 '12 at 15:50
  • heres a news for you: you are not even quaranteed ID-s to be sequential after multiple INSERTs. (mysql >= 5.1) – Imre L Nov 21 '12 at 20:27

3 Answers3

0

Normally, we never change an ID in a database for integrity reasons and there's no problem having gaps in a database. It's normal!

You are not supposed to find a record based on a row number. If you are having problems because of gaps, you need to rethink your design in your application.

but if it's for a display reason, I recommend using the Row_Number

Community
  • 1
  • 1
Marc
  • 16,170
  • 20
  • 76
  • 119
0

If you really wanted to you could:

  1. write a trigger that on deleting a record
  2. updates all the ids that are greater than the one being deleted by -1
  3. then updates all tables that refer to this table.

Depending on the database you are using you maybe need to drop the foreign key constraints at the beginning of the transaction and re-enable them at the end.

But you are probably solving the wrong problem here. This seems like a bad idea resulting from analness rather than any real need.

Sled
  • 18,541
  • 27
  • 119
  • 168
0

DO NOT change your identities. This would be like the bank changing your account numbers every time someone left the bank. There is far too much room at stake here for things to go wrong.

An alternative to what you are trying to do would be to have a sort_order column and have that fix itself. Every time there is deletions have a trigger update the column using the mysql version of the MS SQL function RANK function based on UserID ASC.

This would give you the same desirable output but without breaking any table links.

Regards,

Marcus

Marcus Culver
  • 305
  • 1
  • 4
  • 8