CODEWITHSUNDEEP
mysql
Josh
Josh

Reputation: 377

MySQL import records and shift primary key increment

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.

Upvotes: 0

Views: 2258

Answers (3)

Mike
Mike

Reputation: 21659

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.

Upvotes: 2

a1ex07
a1ex07

Reputation: 37374

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

Upvotes: 0

Jason McCreary
Jason McCreary

Reputation: 73011

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.

Upvotes: 1

Related Questions