Foreign Key
A column in a database table that creates a link between data in two tables by referencing the primary key of another table.
Detailed Explanation
Foreign keys enforce referential integrity in relational databases. They ensure that a value in one table's foreign key column must exist in the referenced table's primary key column. This prevents orphaned records—like an order that references a user who doesn't exist.
Foreign keys define the relationships between tables: one-to-one, one-to-many, and many-to-many. They are essential for normalized database design and are used in JOIN queries to combine data from multiple tables. Foreign key constraints can also cascade operations—automatically deleting or updating related rows when the referenced row changes.
Why It Matters
Foreign keys maintain data integrity across related tables and are essential for designing normalized, reliable database schemas.
Real-World Example
In an e-commerce database, the orders table has a `user_id` foreign key that references the users table's `id` primary key. This ensures every order belongs to a valid user.
When to Use
Whenever you have relationships between tables. Foreign keys are used for one-to-many relationships (one user has many posts), and combined with junction tables for many-to-many relationships.
Advantages
- Enforces referential integrity between tables
- Prevents orphaned records
- Supports cascade operations for bulk updates
- Documents table relationships explicitly
- Enables efficient JOIN queries
Disadvantages
- Adds overhead to INSERT and UPDATE operations
- Can make schema migrations more complex
- Cross-database foreign keys are not supported
- Over-normalization can reduce query performance
- Debugging constraint violations can be confusing
Related Terms
Frequently Asked Questions
What is the difference between primary key and foreign key?
A primary key uniquely identifies rows in its own table. A foreign key references a primary key in another table, creating a relationship between the two tables.
Can a foreign key be NULL?
Yes. A foreign key can be NULL, which means the relationship is optional. For example, a `manager_id` in an employees table can be NULL for employees who have no manager.
What does CASCADE mean in a foreign key?
CASCADE means that when you delete or update a row in the parent table, all related rows in the child table are automatically deleted or updated. Other options include RESTRICT, SET NULL, and SET DEFAULT.
Do I need foreign keys for all relationships?
Foreign keys are recommended for data integrity, but some high-performance systems skip them for speed. This trades safety for performance and requires application-level checks to maintain integrity.
How do foreign keys affect performance?
Foreign keys add a small overhead to writes (INSERT, UPDATE, DELETE) because the database must check constraints. However, they improve query performance by enabling efficient JOINs and are indexed by default in most databases.