Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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