Primary vs Foreign Keys
What a Primary Key Does
Primary key vs foreign key: a primary key uniquely identifies each row in a table, while a foreign key points to a primary key in another table. Together, they form the basis of how tables are linked in a relational database.
A primary key is a column, or set of columns, whose values are unique within the table and never empty. No two rows may share the same primary key value. Most tables have one primary key, often a numeric ID such as customer_id. Many database systems create an index for the primary key automatically, so lookups by that ID are fast.
A composite key combines two or more columns to form the unique identifier. An order-items table might use the order number together with a line number. Auto-increment IDs are a common way to generate primary keys, because the database assigns the next number when a row is inserted. Other systems use universally unique identifiers (UUIDs) instead, each with its own trade-offs.
A unique key is related but different. It also prevents duplicate values, such as two accounts with the same email address, but a table can have several unique keys. Rules about empty values in unique columns vary between systems.
How Foreign Keys Link Tables
A foreign key is a column in one table whose values must match primary key values in another table. It points from a child table to a parent table. For example, an orders table can have a customer_id column that is a foreign key referencing the id column of the customers table.
This rule is called referential integrity. The DBMS rejects an order that names a customer who does not exist. When a parent row is deleted, the database can be configured to refuse the delete, remove the linked child rows or set the link to empty, depending on the rule chosen.
Foreign keys do not copy data. The orders table stores only the customer's ID, so the name and address stay in one place. Combining the two tables in a query is called a join.
Frequently asked questions
Can a foreign key be empty?
Usually yes. A foreign key column can hold NULL, meaning no related row, if the column is defined to allow it. For example, an order placed by a guest might have no customer ID. Whether NULL is permitted depends on how the column was defined, so check the table design before relying on it.
Does every table need a primary key?
Good practice says yes. A primary key gives each row a stable identity, which keeps updates, deletes and foreign key references reliable. Some database systems allow tables without one, but that is generally considered a design weakness, because individual rows cannot be told apart reliably.