0

Through pure SQL statements for creating a table, is it possible to make one with primary keys that auto-increment from 1, and also reset themselves to the form 1, 2, ..., n if one is deleted?

For example, if my table cool_dudes has rows 

id    |     name 
------------------------
 1        "John Skeet"
 2        "Douglas Crockford"
 3        "Steve Ballmer"
 4        "Bjarne Stroustrup" 
 5        "Home Simpson"

and I delete Steve Ballmer, then it becomes 

id    |     name 
------------------------
 1        "John Skeet"
 2        "Douglas Crockford"
 3        "Bjarne Stroustrup"
 4        "Homer Simpson"

and any tables that have columns referencing cool_dudes.id then those columns are updated accordingly.

  • 2
    What for? And correct all foreign keys too. It will be very costly. – Lukasz Szozda Aug 26 '15 at 00:22
  • 4
    Short answer: yes it's possible. See this: http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key The real question is why you would want to do this. – jpw Aug 26 '15 at 00:23
  • 2
    Don't do that! Don't mess with the auto-increment columns! Use a `timestamp` to indicate order. – juergen d Aug 26 '15 at 00:27
  • 2
    This is only possible with a trigger. However, there is no reason to reset the values of primary keys once they have been created. – Gordon Linoff Aug 26 '15 at 00:54
  • **This is a *very* bad idea.** It is possible, but then again, so is jumping off a plain without a parachute... – Zohar Peled Aug 26 '15 at 12:26

0 Answers0