Monday, March 21, 2011

SQL Server: Difference between Primary and Foreign Keys


Primary Keys:
Uniquely identify a row in a table with one or more columns
Column values cannot be null
There can only be one primary key on a table
Candidate for either a clustered or non-clustered index to improve performance
Additional information - Finding primary keys and missing primary keys in SQL Server

Foreign Keys:
They physically define the relationship between tables 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 relationships between tables 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.
If defined and indexed correctly, they can be leveraged by the SQL Server query engine to generate extremely efficient query plans.

Reference: DP(http://dptechnicalblog.blogspot.com )

No comments:

Post a Comment