I have an indexed main table with a record ID that is ONE to Many with several other tables.
The main table has 50,000 records and I want to archive half of them. The other tables,
the "many", are smaller and I do not need to archive those records.
If I Append those 25,000 records to an ARCHIVE table for storage and then delete them from the
main table, there will orphans in the "many" tables and their ID field will be blank.
I also want to be able to selectively bring back and restore occasional records from the ARCHIVE,
and append them back to the MAIN table. When I "append" them "back" to the MAIN table, they will have their ID#, but no way for those ID#s to link back up with the orphans, as they were deleted.
1) In the "many" tables, I suppose I could copy the ID# to a dummy field, before executing the fatal delete. Then after restore, I could copy the dummy field back into the ID field and pray they hook up to the main table.
2) Instead, I was wondering whether I can remove indexing before the fatal delete? Would removing the index, temporarily (using DROP command I suppose), influence ACCESS not to delete the ID# field in the child table?
I can see that I really do not understand why it is necessary for the database engine to delete ID#s in the children tables, once they have been created. As a user, once I start populating children for a parent record in the main table, I give the child a name (the ID#) and why does their name evaporate if the parent is buried or goes on vacation overseas for awhile? Physically, why does that linking ID # have to be destroyed? I thought maybe because I have created an index that links the one ID to the many, and the delete action destroys the index? So I thought turning off the index immediately before the delete and then turning it back on after might work? But something tells me I am not thinking it through all the way, for reasons surrounding "wishful thinking.
Can some one explain to me the physical nature of indexing and one to many Ids created when children are created.
Thanks in advance and sorry for the clunky, imprecise nature of the question. I am a decent programming hack, but a hack nevertheless.