[solved] Insert record in middle of table

[pikemsu28]

Is it possible to insert a record into the middle of a table allowing the auto inc id to adjust accordingly? 

example:

Table: Color Schemes

id(auto inc)  value 1           value 2
1                Red               Blue
2                Blue              Green
3                Orange          Black
4                Black             Red

Table: Color Schemes (I need to insert a record between records 2 and 3)

id(auto inc)  value 1           value 2
1                Red               Blue
2                Blue              Green
3                Green            Orange (inserted record and causes ID of following records to change)
4                Orange          Black
5                Black             Red

I hope this makes sense.

Thanks,
Jason

**Thanks to who all replied.

[artacus]

I want to say no, just because you shouldn't. Auto inc PK's aren't meant to be used like this. You could do this on the one table but it would mess up every table in your database that uses that id as a foreign key. Use instead a separate field, I usually call mine sort_order for that.

Then before the insert:
[code]
UPDATE color_schemes SET sort_order = sort_order + 1 WHERE sort_order > 2[/code]

[fenway]

There's no such thing as the "middle" of a table.. that's the first problem.