SQL Server: Primer — Foreign Key Constraints

Overview

You may know that it is best practice to create FOREIGN KEY constraints on related tables in a database.  Why is this?

Details

A foreign key constraint can be used for:

• Ensuring referential integrity: a foreign key constraint can ensure database consistency
• Self-documenting databases: the foreign key clause can be viewed in the database and describes a relationship
• Efficiency: even without an index, the SQL Server engine recognises the constraint and uses it to optimise table joins automatically

Generally, you will also want to index the foreign key columns in the table, but it is worth adding the FOREIGN KEY constraint even if you don't create the index because it helps the database to optimise the execution plan.

References

• FOREIGN KEY Constraints (Microsoft TechNet)
• Join Fundamentals (Microsoft TechNet)

Versions

• Microsoft SQL Server 2005
• Microsoft SQL Server 2000

Metadata

• Categories: SQL Server, IT Management, Software Development, Performance Analysis
• Additional keywords: query optimisation, table join, foreign key, primary key, primer
• Technorati Tags: SQL, SQL Server, performance, database indexing, architecture, database design, IT Management, best practice, software development, foreign keys, referential integrity