Mysql – Foreign key for rows added later

foreign keyMySQL

I'm using a queue to crawl pages and track links between them.

I have a table called page with a primary key called page_id.

I have a table called links with two columns: link_from, link_to

The queue takes a page, saves it, collects all links from it and saves those. The link_to value in some rows points to a non-existent row (temporarily, until the target pops out of the queue).

From a long-term point of view, I'd like to connect them with foreign keys (for multiple reasons, e.g. delete cascading, automatic relationship discovery from an application which uses the database).

How do you do such a thing? I can't insert the row with a foreign key reference until the row exists.

Best Answer

I would suggest the following:

  • Allow null foreign keys in the main table.
  • Insert rows minus their foreign keys in the import.
  • Add a table ImportRelationships that stores relationships during importing.
  • Rectify the foreign keys in the main table at the end of the import based on ImportRelationships.
  • Delete the rows from ImportRelationships.
Related Question