>I have 2 virtually identical tables and wish to move data between them.
>Basically one table is called "live_table" and the other is named
>"suspended_table" and the only difference is that the primary key in the
>"suspended_table" is an auto incremented integer where as "live_table"
>primary key is just a standard integer.
>
>Here's the life-cycle:
>1. Record gets entered into "suspended_table"
>2. Record checked and then inserted into "live_table" with now corresponding
>record deleted in "suspended_table"
>3. Record (after period of time) is moved back into "suspended_table"

Why not add a column `status` which has the value 'Active' or 'Suspended',
and merge the two tables permanently?
1. Record gets entered with `status` = 'Suspended'
2. Record checked and then its status changed to 'Active'
3. Record status is changed back to 'Suspended' after a period of time.

>Here's now my question. Does the primary key integer value change when it
>moves back to the suspended table (as specified in point 3)?

It depends on how you move it. If you INSERT a value of the
auto-incremented integer that is not null, you get that value
unchanged, unless it's a duplicate, in which case the INSERT fails.

>Will it see the
>returning record as simply a new one, overwrite the existing primary key
>value and give it the next available? Is there a way stopping assigning an
>auto incremented number if a value already exists?

Consider what happens when you restore a dump made by mysqldump
of a table with an auto-incremented field. The INSERT statements
insert a specific integer (not null, and not leaving the field out of
the INSERT's field list). That's what goes into the record. It would
be a real mess if the auto-incremented field got reassigned, and make
mysqldump pretty useless for backups.

>I have some ideas for workarounds such as actually not deleting the record
>in the suspended table but instead adding an extra field to act as a flag.

I think you need to merge the two tables, with the status flag. The
`status` field may need its own index, or become part of a compound index
with something else.

Gordon L. Burditt