Foreign key for rows added later

Question

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.

Answer 1

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.

Answer 2

You can't defer constraint checking in MySQL.

A possible workaround is disabling foreign key checks before doing the query, and enabling it again afterwards:

SET foreign_key_checks = 0;
INSERT INTO ...
SET foreign_key_checks = 1;