Search code examples
phpmysqlarchive

Archiving data in mysql

EDIT

Let me adjust my question, if I wrote a script that copies a current mysql table to an archiving table then delete the records from the original table how can I make sure the auto increment is preserved? I need to make sure the primary keys are not duplicated in the archived tables.

Solution

  • There is no need to preserve the primary key position in the archiving table. Just make sure that when you copy to the archiving table you use INSERT IGNORE INTO... and you won't need to worry about duplicate entries into the archiving table. Then simply use a unique constraint on the column of the primary key in the archiving table. You should not have an auto-incrementing primary key on your archiving table.