Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















The Importance of SQL Server Foreign Keys

MSSQLTips author Armando Prato By:   |   Read Comments (8)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Constraints

Problem
Some time ago, I had a water cooler discussion with one my company's java developers about maintaining data integrity. His belief was that all data integrity checks should be handled in the logic tier of an n-tier system. I politely explained why I consider this a bad practice. Foreign keys are specifically provided by SQL Server to prevent your database from storing invalid data relationships and should be a mainstay in every relational database model developed!

Solution
Foreign keys are part of the family of constraints that SQL Server provides to ensure database integrity. You may be familiar with other constraint types that help maintain data integrity such as Primary Key constraints, Unique constraints, Default constraints, and Check constraints. Each of these constraint types serves a specific purpose. The foreign key's purpose is to ensure the relationship integrity between a parent table and its child tables.

You can define a foreign key as follows

ALTER TABLE DBO.<child table>
ADD CONSTRAINT <foreign key name> FOREIGN KEY <child column>
REFERENCES DBO.<parent table>(<parent column>)
{ON [DELETE|UPDATE] CASCADE}

The following example declares that a line item row cannot exist without an associated order header row. The ON DELETE CASCADE option tells the database engine that if the parent ORDER HEADER row's ORDERNUMBER is deleted, then any LINE ITEM tied to the ORDER HEADER by the deleted ORDERNUMBER should be automatically deleted as well.

ALTER TABLE DBO.LINEITEM
ADD CONSTRAINT FK_LINEITEM_ORDERNUMBER FOREIGN KEY (ORDERNUMBER)
REFERENCES DBO.ORDERHEADER(ORDERNUMBER)
ON DELETE CASCADE

There are obvious reasons for defining foreign key constraints in your data model

  • They physically define the business by preventing data integrity issues in your database. (e.g. The database prevents line items from being created without an existing order header)
  • They logically document the business by showing how all data relates to each other. To someone new to your organization, this allows him/her to get a good understanding of how the business works. (e.g. Every order taken must have a valid customer assigned)
  • Foreign Keys are native to SQL Server and are designed to prevent data integrity issues. Business logic developers should not be in the business of verifying table relationships.

The not so obvious

  • If defined and indexed correctly, they can be leveraged by the SQL Server query engine to generate extremely efficient query plans.

Unlike primary key constraints and unique constraints, foreign key constraints are not automatically indexed by SQL Server. However, indexing the column used in your foreign key is a good idea for a few reasons

  • Without an index, user deletion of a parent row from the database would force the SQL Server query engine to scan the child table referenced in the foreign key to ensure that data integrity is not compromised. Consider a child table with hundreds of thousands of rows; an index can speed this lookup considerably.
  • The performance of the foreign key CASCADE options (ON DELETE CASCADE, ON UPDATE CASCADE) can be improved dramatically with the use of an index since the engine performs a query to search for the rows that should be automatically deleted or updated.
  • The performance of JOINs between the parent and child tables on the foreign key column is greatly improved. It's a natural assumption that tables that are related may be queried together to produce result sets. Consider the earlier ORDERHEADER/LINEITEM example. It would be natural for queries to be executed that would require not only elements of the LINE ITEMs but also of the ORDERHEADER (e.g. order date, the CSR who entered the order)

Next Steps

  • When developing and maintaining data models, examine where your model can benefit from a foreign key relationship
  • Ensure your foreign key columns are indexed to prevent table scanning during data deletion, cascading actions, and query JOINs.
  • Read more about foreign key CASCADE options in the SQL Server Books Online under Cascading Options or Cascading Actions.
  • Read more about the other SQL Server constraints: Primary Key, Unique, Default, and Check in the SQL Server Books Online



Last Update: 7/30/2007


About the author
MSSQLTips author Armando Prato
Armando Prato has over 24 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, March 23, 2009 - 9:37:15 AM - markntejas Read The Tip
I have a parent table with its primary key. I want it to be referenced as a foreign key in more than one child table. i receive the "ALTER TABLE statement conflicted with the FOREIGN KEY constraint ..." The conflict occurred in database "schema_name", table "dbo.a_db_tablename", column 'parent_tbl_PK_ID'." can i have a primary key that is referenced as a foreign key in multiple tables? thx in advance ....

Monday, March 23, 2009 - 6:26:40 PM - aprato Read The Tip

A message like that is indicative of having an entry in the child table that does not exist in the parent table.   You won't be able to build the FK until you clear this up (you could, if you used NOCHECK - but I don't recommend it).


Tuesday, March 24, 2009 - 7:17:36 AM - markntejas Read The Tip
many thx ...

Thursday, January 26, 2012 - 11:36:06 PM - Lx[n] Read The Tip

Nice article, but just want to let you know that I believe someone taken your article and make it as his own, below are the url link:

http://ps-india.blogspot.com/2008/10/importance-of-sql-server-foreign-keys.html

Prakash Samariya (M): +91-9879074678
http://www.365jobs4u.com/idevaffiliate/pages/2835.php
MY NEW SITE: http://perfectsolutions.blinkweb.com/
MY NEW BLOG: http://perfectsolutions.blinkweb.com/my-blog.html

Cheers!


Friday, January 27, 2012 - 9:21:04 AM - Armando Prato Read The Tip

Thanks for letting me know.  I will let the folks at Edgewood know


Monday, August 13, 2012 - 8:00:30 AM - Srikanth Read The Tip

 

Msg 1785, Level 16, State 0, Line 1

Introducing FOREIGN KEY constraint 'FK_AAAA' on table 'MASKED' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

 
 
my actual code looks similar:
 
ALTER TABLE DBO.<child table>
ADD CONSTRAINT <foreign key name> FOREIGN KEY <child column> 
REFERENCES DBO.<parent table>(<parent column>)
ON DELETE CASCADE

Monday, August 13, 2012 - 9:43:53 AM - Armando Prato Read The Tip

This is pretty self explanatory.  

 

http://support.microsoft.com/kb/321843

 

 


Thursday, April 04, 2013 - 10:44:30 AM - John Read The Tip

Considering only SQL performance: Given that I already have an index on the foriegn key candidate column, is there any performance bump in defining that field as a foreign key?

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.