List:General Discussion« Previous MessageNext Message »
From:Philip Pemberton Date:February 3 2009 10:38pm
Subject: Re: Algorithm for resolving foreign key dependencies?
View as plain text  
Andy Shellam wrote:
> Am I missing something here?  (It is late after a long day, I admit!)

Only something I forgot to mention.

All the foreign keys are set up as ON DELETE RESTRICT, meaning MySQL's 
response to a foreign key violation is to spit out an error message to the 
effect of "I'm sorry, Dave, I can't let you do that."

The problem is, the target platform doesn't use foreign keys for performance 
reasons. I want to use foreign keys in development as a bug-trapping method -- 
I'd rather see an FK violation error in development than get an angry email 
from a customer asking why there's a part listed that doesn't seem to have a 
manufacturer.

The plan was to write a code-generator that would generate all the database 
code for me, then I could deal with the page templates and display logic 
myself (thus eliminating ~80% of the boring, repetitive work). I want the 
generated code to handle foreign keys itself, rather than relying on the database.

As I said above, if foreign key constraints didn't slow things down markedly, 
I'd use them in production. Based on the (admittedly limited) testing I've 
done, application-side FK enforcement is considerably faster than using ON 
DELETE CASCADE and letting MySQL deal with the foreign keys.

I don't like writing database code by hand (it all follows a standard 
template), so I figured I'd write a program to do it for me. "Work smarter not 
harder" and all that :)

Thanks,
-- 
Phil.
usenet08@stripped
http://www.philpem.me.uk/
If mail bounces, replace "08" with the last two digits of the current year.

Thread
Algorithm for resolving foreign key dependencies?Philip Pemberton3 Feb
  • Re: Algorithm for resolving foreign key dependencies?Andy Shellam3 Feb
    • Re: Algorithm for resolving foreign key dependencies?Philip Pemberton3 Feb
      • Re: Algorithm for resolving foreign key dependencies?ddevaudreuil4 Feb
        • Re: Algorithm for resolving foreign key dependencies?Peter Brawley4 Feb
  • Re: Algorithm for resolving foreign key dependencies?Andrew Garner3 Feb