0

I have a database table with this structure, it contains thousands of rows and I query on this table regularly:

Name Type Null Extra
main_id (pk) bigint(20) No AUTO_INCREMENT
order_id bigint(20) No
order_item_id bigint(20) No
order_date date No

I want to archive rows from this table into another table periodically with PHP, I'll be creating another table with similar structure to this as the archive table.

Am I correct in thinking that the archive table main_id should be created without AUTO_INCREMENT? No rows will be added to this table except when PHP periodically grabs rows from the original table where the date is older than x and inserts them.

For future proofing, if I ever wanted to move the rows in the new archive table back to the original table, will the AUTO_INCREMENT on the original table cause an issue here? e.g. what happens if a main_id (e.g. 420) from the archive table goes in to the original table (where main_id might now be at 2000 for new rows)? will it reset the AUTO_INCREMENT?

bigdaveygeorge
  • 947
  • 2
  • 12
  • 32
  • Does this answer your question? [How to insert data to MySQL with auto-incremented column(field)?](https://stackoverflow.com/questions/8753371/how-to-insert-data-to-mysql-with-auto-incremented-columnfield) – Stu May 01 '23 at 09:23
  • @Stu I understand that if I don't provide main_id on insert it would auto increment, but would the archive table need AUTO_INCREMENT (so an identical clone of the original structure) or not use it? – bigdaveygeorge May 01 '23 at 09:25
  • No your archive table wouldn't use auto_increment, you want (presumably) an exact copy of the data. – Stu May 01 '23 at 09:27
  • @Stu and then in future if I ever wanted to insert the rows from archive table back to the original table these would be inserted with the main_id, and therefore wouldn't effect the AUTO_INCREMENT of new rows added to the original table, they would continue to be added to the original count? – bigdaveygeorge May 01 '23 at 09:30
  • Why don't you test it and see – Stu May 01 '23 at 09:55
  • `AUTO_INCREMENT` will only be used if the INSERT does not have any value given for that column. E.g. `INSERT INTO tbl (order_id, order_item_id, order_date) VALUES (....)`. If from archive table, you insert records into main table WITH PK in the INSERT query, those values will be inserted as-is, and your AUTO-INCREMENT's current max value won't be affected. I.e. `INSERT INTO tbl (main_id, order_id, order_item_id, order_date) VALUES (420, ....)` won't affect auto-increment status in any way. It will just try to insert a record with whatever value provided in the query. – Ishan May 01 '23 at 10:19

0 Answers0