Should I use foreign keys? [duplicate]

Question

Possible Duplicate:
What’s wrong with foreign keys?

I use MS Sql Server with a large database about 4 GB data.

I search around the web why I should use foreign keys. by now I only indexed the keys used to join tables. Performance is all fine, dataintegrety is no problem.

Should I use foreign keys? Will I get even more performance with foreign keys?

Answer 1

Foreign key's don't actually improve performance, in fact they incur a small performance penalty on all write operations, to ensure that the constraint is followed.

The reason why you want to use these is to prevent destructive write operations. If you don't have them, buggy code, or a bad sql statement can remove rows that are expected to be there.

Answer 2

A foreign key is primarily a tool for enforcing database integrity, which is unrelated to speed of execution.

If you have already optimized your index design, then you probably have these indexes already installed, at least as non-unique indexes. So I wouldn't expect any performance change just from installing foreign keys (whicb don't even necessarily involve an index.)

I'd be a little suspicious of your complacency about the optimization of your design, though, if you don't already have this concept nailed.

Read the documentation for Foreign Keys with the goal of understanding what they do to enforce integrity (it's worth knowing about in any case.) Then see if that doesn't answer your question more completely.