What is a primary key and a foreign key? What role do they play in a relational database?

Question

Accepted Answer

Primary keys and foreign keys are essential for structuring data in a relational database.

A primary key uniquely identifies each record in a table. It cannot be null and must be unique. For example, in a users table, the id column would be the primary key. Each user has a different id, which allows retrieving them without ambiguity.

A primary key can be simple, using a single column, or composite, using multiple columns. For example, in an order items table, you might have a composite key of order_id and product_id.

A foreign key is a column that references the primary key of another table. It creates a relationship between the two tables. For example, in an orders table, the user_id column would be a foreign key referencing the id column of the users table.

Foreign keys guarantee referential integrity. The database prevents inserting an order with a user_id that does not exist in the users table. Similarly, it can prevent deleting a user who has associated orders.

You can configure the behavior on deletion or modification with ON DELETE and ON UPDATE. Options include CASCADE to propagate the action, SET NULL to set the value to null, or RESTRICT to block the operation.

These constraints maintain data consistency and document the relationships between entities.