| Author |
Topic |
|
smadasty
Starting Member
3 Posts |
 Posted - 2012-08-22 : 11:54:35
|
| 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
38200 Posts |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-08-22 : 12:11:21
|
| 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 |
 |
|
|
smadasty
Starting Member
3 Posts |
Posted - 2012-08-22 : 12:27:25
|
| 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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-08-22 : 13:29:12
|
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/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
smadasty
Starting Member
3 Posts |
|
|
|
Is FOREIGN KEY so important?