What is the difference between a primary key and a foreign key? Why are they important in database design?

Question

Accepted Answer

Primary keys and foreign keys are fundamental constraints that enforce data integrity and establish relationships between tables in relational databases.

A primary key uniquely identifies each row in a table. Every table should have a primary key. The primary key must contain unique values and cannot contain NULL. A table can have only one primary key, but it can consist of multiple columns, called a composite key. Common examples include auto-incrementing integers like user_id or natural keys like email addresses.

A foreign key is a column in one table that references the primary key of another table. It creates a relationship between the two tables. The foreign key ensures referential integrity, meaning you cannot insert a value in the foreign key column that does not exist in the referenced primary key column.

For example, in an orders table, the customer_id column would be a foreign key referencing the id column in the customers table. This prevents orders from being created for customers that do not exist.

Primary keys are important because they ensure each record is uniquely identifiable, enable efficient data retrieval through indexing, and serve as the target for foreign key relationships.

Foreign keys are important because they maintain referential integrity between related tables, prevent orphaned records where child records have no parent, and document relationships in the database schema.

Together, primary and foreign keys form the backbone of relational database design. They enable complex queries across multiple tables while ensuring data consistency and preventing invalid data from entering the system.