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...
