Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am importing a 50 table relational database (I'm not a dba) with 250,000+ records in most of the tables, yet the auto increment is off in most tables, some for example showing the last ID at 5 million (the key) instead of 250,000.

In the past there were lot of imports which caused huge gaps, so I was wondering if it is possible to somehow remove those gaps so everything is nice and orderly so that you know that if there are 452,334 records in a table each with a uniqueID starting from 1 , then row 452,334 would be the last row and not row 3,239,211.

The ids are used in a number of other tables as a foreign key and I am sure I could do it somehow with an enormous amount of code and many lost days. So was wondering if there is some quick fix that I am overlooking or perhaps an app that will do the dirty work for me?

I tried googling but either I am not phrasing what I need correctly or I am insane for even asking this.

Thank you in advance.

share|improve this question
But even if you do this, gaps will still going to appear later by future imports or from Deleted rows or missed Inserts, etc. – ypercubeᵀᴹ Oct 23 '13 at 7:02
Yeh. Guy who asked me has everything army neat in his office so I guess maybe he wants the database the same way. In either case I prefer not to tell him its a waste of time. – NickNo Oct 23 '13 at 7:18
But he may consider this: After the rearrangement, the IDs (that were previously missing, in the gaps) will be reused. So, for example the row with ID=2329211 will have ID=452334. What if the ID=452334 was previously (say last year) used and then deleted? What if you have some link in old backups or printed reports or exported in Excel file this ID (452334) with data regarding that old (but now deleted) row/item? The change of IDs will bring confusion if you ever compare those exported versions of your data with the new ("improved") version. – ypercubeᵀᴹ Oct 23 '13 at 7:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.