On Wed, 2006-03-29 at 11:35, Nico Callewaert wrote:
> Hi !,
>
> Is it wise to define foreign keys for referential entegrity ?
> Example : I have a customer table with 40 fields. Out of that 40
> fields, 10 fields contain information linked to other tables. So, is
> defining foreign keys for these 10 fields a good idea ? Because from
> what I understand, for every foreign key, there is an index defined.
> So, all these indexes has to be maintained. Is that killing
> performance ? What's the best practise : defining foreign keys or not
> ?
While a foreign key has to point to a field(s) with a unique index on
it, the foreign key itself doesn't require an index. That said,
performance is usually better with it than without it.
If performance is your only consideration, then an SQL database is
probably not your best choice. There are plenty of solutions that can
run faster. They just may not guarantee you data stays coherent. And
sometimes, that's ok. Sometimes you have a margin of error in your data
that means you can lost a few bits here and there and the system is
still allright. (i.e. weather monitoring and such)
However, if your data is critical, and even a single error is a bad
thing (i.e. accounting, airline reservations, medical, and so on) or
possibly even deadly.
From a performance perspective, I haven't found that FK/PK is the
problem so much as extreme normalization. When you have to join 100+
tables for every request, your performance may not be as fast as you'd
like. Setting up fk/pk relations for these 100 tables, however, almost
never makes them slower, unless they're set up poorly.
Occasionally you'll see someone used mismatched types in a FK/PK
relationship (i.e. int -> text) or other strange things. That can cause
issues.
I'd suggest benchmarking your issue, and seeing what kind of performance
you get in your schema with and without fk/pk references.
and if you do decide that going without fks are fine, then don't forget
to factor in your daily / weekly / monthly / yearly data cleaning
festivals...