0

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.

2

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.
  • It's a bit of work but it's a robust solution, thanks. – vvondra Oct 7 '13 at 18:36
0

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;
  • I've thought about that, is there a potential drawback? – vvondra Oct 5 '13 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.