views:

404

answers:

4

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.

+4  A: 

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.

Lukáš Lalinský
Not just buggy client code. "Buggy" users and database administrators have been known to make mistakes when making a quick fix by directly typing in SQL update commands!
Thomas Padron-McCarthy
+2  A: 

FK constraints keep your data consistent. That's it. This is the main benefit. FK constraints will not provide you with any performance gain.

But, unless you have denormalized on purpose db structure, I'd recommend you to use FK constraints. The main reason - consistency.

Vitaliy Liptchinsky
+4  A: 
  • 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.
Andy Jones
I've been handling some databases with a few million records. A lot goes around importing data between database and its clone and then using that clone in the web-app environment.Well, I've known that keeping PK indexes automatically and so it helps speedup the data access. Now, from this discussion I derive that if I'm using JOINs in my SQL-Queries then I shud use FK and index it to make the JOIN operations efficient.
Hemant Tank
For example, I have a table OrgMaster (contains all the Org records) then I have a BookingMaster table (contains all the Booking records). Now, the OrgMaster.Id is being 'referenced' as BookingMaster.OrgId. So, I have an FK for the OrgId-to-Id relationship and I shud 'index' it for better performance of any JOIN operation between both of these tables .. did I get it correctly?<br/>All the above - at the cost of extra overhead of space and time (while inserting record in the table with FK).
Hemant Tank
I'd request that you provide me a list of points to be considered, like - - Is FK-index going to eat up too much space\time as table grows few million records?- In that case, is it worth to go for an FK-index "each time"?- In what case shud I NOT apply FK or Index it or do neither of it (of course I can handle a LOT from the app)- Any other tricky to speedup JOIN or other such time-consuming lookups?
Hemant Tank
A: 

As mentioned, they are for data integrity. Any performance "loss" would be utterly wiped out by the time required to fix broken data.

However, there could be an indirect performance benefit.

For SQL Server at least, the columns in the FK must have the same datatype on each side. Without an FK, you could have an nvarchar parent and a varchar child for example. When you join the 2 tables, you'll get a datatype conversions which can kill performance.

Example: different varchar lengths causing an issue

gbn