SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Is FOREIGN KEY so important?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smadasty
Starting Member

3 Posts

Posted - 08/22/2012 :  11:54:35  Show Profile  Reply with Quote
I have bunch of tables created couple of months back and at that time, for some reason we didn't create any foreign key constraints on any of the tables, now since we have a clear understanding of all the tables and the data structures in them, I want to update the tables and link them to one another using foreign key constraint - but, the problem is these tables are filled with data and they are constantly getting updates with new data - so is it a really a good idea to update these tables with foreign key now and how important is it to have a foreign key? I know they help creating database diagrams but apart from this how important are they?

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 08/22/2012 :  12:04:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes you should add the foreign keys as they are there to protect the data. Number of updates has no relation to whether or not you should add foreign keys. We have a system that does up to 7000 batches per second. And yes we have foreign keys in place to protect the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 08/22/2012 :  12:11:21  Show Profile  Reply with Quote
Referencial Integrity is one of the common question you should ask yourself if you want to comply to it. This means without foreign keys some of your tables will accept inserting rows (data) without checking the integrity of your data in other tables, for example:
OrderTable, and OrderItemTable (Think of it as Parent/child relation) logically we can't have an item that does not belong to any order. Having a foreign key will help to not allow an insertion into OrderItemTable without having a valid Order in OrderTable.

This is just simple example;
It will also help keep your data clean and consistent.
I would recommend having them.

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

smadasty
Starting Member

3 Posts

Posted - 08/22/2012 :  12:27:25  Show Profile  Reply with Quote
Thank you for both the reply's, I completely agree with you, and I guess it’s not too late to update the tables even when they are filled with data.

Also, since we are on this topic, I want to ask, if - having foreign key constraint help the performance of queries in anyway?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 08/22/2012 :  12:39:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
No they don't help the performance of queries, but you should add indexes to those same columns as they are likely being used in joins. It's the indexes that will help things, not the foreign keys. You have to manually add the indexes as you don't get an automatic index like you do with primary key and unique constraints.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/22/2012 :  13:29:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Originally posted by tkizer

No they don't help the performance of queries, but you should add indexes to those same columns as they are likely being used in joins. It's the indexes that will help things, not the foreign keys.
Certain types of queries can benefit from foreign keys, as long as they're trusted:

http://datachix.com/2010/09/09/can-you-trust-your-foreign-key-or-feature-821-i-didnt-already-know-about-sql-server/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 08/22/2012 :  14:46:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by robvolk

quote:
Originally posted by tkizer

No they don't help the performance of queries, but you should add indexes to those same columns as they are likely being used in joins. It's the indexes that will help things, not the foreign keys.
Certain types of queries can benefit from foreign keys, as long as they're trusted:

http://datachix.com/2010/09/09/can-you-trust-your-foreign-key-or-feature-821-i-didnt-already-know-about-sql-server/



Good point.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

smadasty
Starting Member

3 Posts

Posted - 08/23/2012 :  11:29:22  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

quote:
Originally posted by tkizer

No they don't help the performance of queries, but you should add indexes to those same columns as they are likely being used in joins. It's the indexes that will help things, not the foreign keys.
Certain types of queries can benefit from foreign keys, as long as they're trusted:

http://datachix.com/2010/09/09/can-you-trust-your-foreign-key-or-feature-821-i-didnt-already-know-about-sql-server/



Really good point - something new to learn, thank you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000