[SOLVED] How to override the attribution of an auto incrementing primary key when inserting a value in a MySQL table?

Issue

I have a MySQL table of users whose primary key is an auto-incrementing integer. The table is already populated with records whose key is between 0 and 30.000. Not all records are there, though. Some users have been removed and therefore I have “holes”.

Now the client has realised they removed a bunch of users by mistake, and they now want me to reinsert those users keeping the same ID they had, for compatibility with the back-end of the e-commerce, which runs on a different machine altogether, but uses the same ID’s for the customers.

At the moment I am:

1. altering the structure of the table, by removing the auto_increment property from the ID
2. adding the records I need, specifying their ID
3. reverting the alterations to the structure of the table.

Is there a better way to achieve this? Is there any SQL override function that would allow me to force MySQL to accept a value that is unique but not necessarily “the next number in the line”?

Solution

You don’t have to disable the auto_increment feature. When you insert a row into the table and you do specify the primary key value in the row, the id you want is stored in the database. The auto_increment is only used, when you omit the primary key field.

EDIT: I thought I might give examples for that:

mysql> describe test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| value | varchar(45)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> insert into test (value) values ('row 1');
Query OK, 1 row affected (0.06 sec)

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | row 1 |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into test values (15, 'row 2');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | row 1 |
| 15 | row 2 |
+----+-------+
2 rows in set (0.00 sec)

EDIT 2

mysql> insert into test (id, value) values (3, 'row 3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | row 1 |
| 15 | row 2 |
|  3 | row 3 |
+----+-------+
3 rows in set (0.00 sec)

Answered By – Dan Soap

Answer Checked By – Gilberto Lyons (BugsFixing Admin)