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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
