I think this is an old question, but I want to know if it really
is well worth to not create some foreign keys an deal with the
referential integrity at application-level?????
Trust me : do it in the application and you'll enter a world of
hurt. I'm doing it with some mysql apps, and it's a nightmare ; doing
cascaded delete's by hand, etc, you always forget something, you have
to modify a million places in your code everytime you add a new table,
your ORM bloats, you get to write cleanup cron scripts which take
forever to run, your website crashes etc.
All good advice, but... there are no absolutes in this world. Application-enforced referential integrity makes sense if (and probably ONLY if):
1. You have only one application that modifies the data. (Otherwise, you have to duplicate the rules across many applications, leading to a code-maintenance nightmare).
2. If your application crashes and leaves a mess, it's not a catastrophe, and you have a good way to clean it up. For example, a bank shouldn't do this, but it might be OK for a computer-aided-design application, or the backend of a news web site.
3. You have application-specific knowledge about when you can skip referential integrity and thereby greatly improve performance. For example, you may have batch operations where large numbers of rows are temporarily inconsistent.
If your application doesn't meet ALL of these criteria, you probably should use the database for referential integrity.
Craig
