> Nevertheless, foreign key constraints belong in the database, not in your
> application... If you have foreign keys (your wording), you need foreign
> key constraints. Period. Plain and simple. No discussion :-)
How about a log database? We log every phone call to a calls table which
currently has 27 million records. We log every event that happens on every
phone call to an events table which currently has 240 million records, which
are linked to their calls via a foreign key. Inserts into the calls and
especially the events table happen up to 120 times per second. If these
inserts back up, our telephony service stalls and people hang up and cancel
service. Every time we insert an event into the events table, we do not
want the database engine to see that it has a foreign key and select on the
calls table for that foreign key. It simply makes no sense. First, events
wouldn't be posted if we hadn't already done a successful insert a half of a
second ago to calls and gotten the insert id. Second, even if we did,
maintaining quality of service is far more important than the possibility
that events could be inserted with an invalid foreign key. In matters of
logging, that's typically not a critical issue, and in this application, it
is certainly not the priority. On the other hand, in a billing log, it may
be essential.
There are different solutions to different applications. :)
