"Gordon Burditt" <gordonb.3wcat@burditt.org> wrote in message
news:124t8u0dj6e3hd0@corp.supernews.com...
> >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.

Thanks for the info, especially regarding how auto-increment works. That's
very useful to know.

However I am bit confused as to why I should merge the tables. Someone else
suggested it too but if I am simply going to add a flag (which switches the
record on or off), then that should suffice shouldn't it? As I said though,
if both you and the other chap suggest the same thing, then I am most likely
missing a trick here somewhere.

Once again thanks for taking the trouble to answer.

Cheers

Phil