MySQL import records and shift primary key increment

Question

I have a MySQL table that contains the columns id, created_date, and name, where id is the auto-incremented primary key (starting at 1) and created_date is the date the row was created (just an example).

There are 50 rows in this table since the launch of this application. Suppose I have 100 old records (considering the created_date values) that I want to import into this table.

Is there a simple way to shift the id values of the current records to 101-150, so I can import the old records with id values of 1-100? Is this even advisable?

If I had to do it over again, I would've adjusted up the auto-increment of the id column (primary key) before launching the application to accommodate the records I intended to import.

Answer 1

It is possible, but I wouldn't advise shifting primary keys on a production table. This would kill referential integrity by breaking your data associations.

Answer 2

1.update table1 set id = id+100. Make sure that you changed all references to these records in other tables(it will be done automatically if you use foreign keys with on update cascade option.
2. insert 100 old records
3. update auto-increment of the id

Answer 3

You could renumber them with an UPDATE command:

UPDATE table SET id = id + 100;

Or import them into a new table which has the AUTO_INCREMENT value pre-set:

 CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101;

You'll need to take care to ensure that you maintain referential integrity - if other tables reference this table, then you'll need to make sure that they are updated too.