Primary Key vs Foreign Key with SQL Examples

What is a primary key?

A primary key uniquely identifies each row in a table. Its values must be unique and cannot be NULL. A table has one primary-key constraint, which can contain one column or multiple columns (a composite key). A primary key may be a natural value, such as a carefully governed code, or a generated identifier.

What is a foreign key?

A foreign key is a constraint that enforces a relationship between rows. It references a primary key or another eligible unique key in the referenced table. It prevents a row from pointing to a parent record that does not exist, subject to the constraint and database behavior.

Example: customers and orders

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

Here, customers.customer_id is the primary key. orders.order_id is the primary key for orders, while orders.customer_id is a foreign key linking each order to a customer. Many orders can refer to the same customer.

Primary key vs foreign key

What happens if a referenced customer is deleted?

The foreign-key constraint defines or relies on a referential action. Common choices include restricting the delete, cascading it to related rows, or setting the foreign key to NULL (if allowed). Choose carefully: cascading deletes can be convenient but may remove more data than expected.

FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  ON DELETE RESTRICT

The supported actions and defaults vary by database. Always check the target DBMS documentation and test the behavior before applying a schema change.

Composite keys and nullable foreign keys

A composite primary key uses more than one column when the combination uniquely identifies a row, such as (student_id, course_id) in an enrollment table. A foreign key can also contain multiple columns. In many relational systems, a nullable foreign-key column can be NULL when a relationship is optional; exact composite-NULL rules depend on the database and constraint options.

Best practices

• Define primary keys explicitly for tables that represent entities.
• Use matching compatible data types for referenced and referencing columns.
• Index foreign-key columns when joins and parent-row checks need it.
• Decide delete/update actions based on business rules, not convenience alone.
• Use constraints to enforce integrity in the database rather than relying only on application code.

Frequently asked questions

Can a table have two primary keys?

A table has one primary-key constraint, but that constraint can consist of multiple columns. A table can also have additional unique constraints.

Can a foreign key contain duplicate values?

Yes. Multiple child rows can normally reference the same parent row, which is how one-to-many relationships work.

Can a foreign key be NULL?

Yes, if the column permits NULL and the relationship is optional. A NOT NULL constraint prevents it.

Does a foreign key automatically create an index?

Behavior differs by database engine. Some engines create or require supporting indexes in certain cases; verify the specific DBMS behavior.

Can a foreign key reference a non-primary column?

Often it can reference a suitable unique key, depending on the database's requirements.

Why use foreign keys?

They protect referential integrity, helping prevent orphaned child records and inconsistent relationships.

Learning tip: Try the examples in a test project before using them in production. Check official documentation for behavior that depends on a tool, database, or version.