SQL Server Foreign Key constraint benefits

Question

We're designing a database in which I need to consider some FK(foreign key) constraints. But it is not limited to formal structuring and normalization. We go for it only if it provides any performance or scalability benefits.

I've been going thru some interesting articles and googling for practical benefits. Here are some links:

http://www.mssqltips.com/tip.asp?tip=1296

I wanted to know more about the benefits of FK (apart from the formal structuring and the famous cascaded delete\update).

• FK are not 'indexed' by default so what are the considerations while indexing an FK?

• How to handle nullable fields which are mapped as foreign key - is this allowed?

• Apart from indexing, does this help in optimizing query-execution plans in SQL-Server?

I know there's more but I'd prefer experts speaking on this. Please guide me.

Answer 1

• Foreign keys provide no performance or scalability benefits.
• Foreign keys enforce referential integrity. This can provide a practical benefit by raising an error if someone attempted to delete rows from the parent table in error.
• Foreign keys are not indexed by default. You should index your foreign keys columns, as this avoids a table scan on the child table when you delete/update your parent row.
• You can make a foreign key column nullable and insert null.

Answer 2

The main benefit is that your database will not end up inconsistent if your buggy client code tries to do something wrong. Foreign keys are a type of 'constraint', so that's how you should use them.

They do not have any "functional" benefit, they will not optimize anything. You still have to create indexes yourself, etc. And yes, you can have NULL values in a column that is a foreign key.