Add item with same id. Auto increment old id's by 1

Question

So my database's primary key is just a column called 'id'. When i go to add a new item into the database I want it in a specific order without having to go into the DB and changing every value after it to +1 whatever it was before.

Example:

   ID
 | 1 | item1 |
 | 2 | item2 |
 | 3 | item3 |
 | 4 | item4 |

Say I want to add an item inbetween item2 and item3. To do that I would need to change item 3's id to 4 and item4's id to 5, but currently I have to go into the database and do it automatically.

How would I make it increment automatically when I INSERT a new item?

Note: http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key — Suvarna Pattayil, Oct 15 '13 at 15:27
`auto increment` values should be considered opaque. If you care what values they have, you're doing it wrong. — Alnitak, Oct 15 '13 at 15:27
What would be the best way to go about ordering a giant list of items and then when one gets added somewhere in the middle move the rest down? — Doug Leary, Oct 15 '13 at 15:31
Ordering has to do with how objects are _displayed_, and a primary key is something that gives a row a unique identity and controls how things are related. Don't use one for the other. — Burhan Khalid, Oct 15 '13 at 15:36

score 1 · Answer 1 · answered Oct 15 '13 at 15:34

You should consider leaving IDs unaltered and adding a secondary column to sort by, e.g. sort_order. Needing to alter all the IDs in the parent and related tables for every insert can't be a good idea, esp. if you don't have properly crafted foreign keys.

If you do so, it should be fairly easy to accomplish:

-- Untested
START TRANSACTION;
UPDATE foo SET sort_order=sort_order+1 WHERE sort_order>=4;
INSERT INTO foo (name, sort_order) VALUES ('item', 4);
COMMIT;

Add item with same id. Auto increment old id's by 1

1 Answers1