Primary Key vs Foreign Key in SQL: How Tables Connect

A primary key is a column or set of columns that uniquely identifies every row in a database table and cannot contain null values. A foreign key is a column that references a primary or unique key in another table, establishing a link between the two and enforcing referential integrity.

Think of a primary key as an employee's worker ID number, while their department code acts as a foreign key connecting them to the company department directory.

What Is the Difference Between a Primary Key and a Foreign Key?

The fundamental difference between a primary key and a foreign key comes down to identity versus reference. A primary key establishes the unique identity of a row within its own table, whereas a foreign key connects a child row to an existing parent record across tables.

As documented in Microsoft Learn's guide on primary and foreign keys, these constraints work together to maintain database consistency:

Feature	Primary Key	Foreign Key
Primary Purpose	Uniquely identifies each record	Connects child records to parent records
Uniqueness	Must be unique across all rows	Can contain duplicate values
Nullability	Cannot contain NULL	Allows NULL unless marked NOT NULL
Constraint Limit	Only one per table	Multiple allowed per table
Automatic Indexing	Indexed automatically in PostgreSQL	Not indexed automatically

How Do Primary and Foreign Keys Work Together in SQL?

Primary keys and foreign keys collaborate to build parent-child relationships across tables. In PostgreSQL, you must create the parent table before a child table can reference its key column.

Imagine that you need to store departments and employees:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT REFERENCES departments(department_id) ON DELETE SET NULL
);

CREATE INDEX idx_employees_department_id ON employees(department_id);

Running CREATE INDEX on employees(department_id) is an essential best practice. PostgreSQL automatically creates a unique B-tree index for primary keys, but it does not index foreign key columns. Without this manual index, updating or deleting parent rows can trigger slow sequential scans across the child table.

What Are the ON DELETE Options for Foreign Keys?

Referential actions define what happens to child records when a referenced parent row is deleted. According to the PostgreSQL documentation on constraints, common delete rules include:

• ON DELETE NO ACTION: The default behavior that rejects the parent deletion if referencing child rows exist.
• ON DELETE RESTRICT: Immediately blocks the deletion of a parent record without allowing deferred checks.
• ON DELETE CASCADE: Automatically deletes all referencing child rows when the parent row is deleted.
• ON DELETE SET NULL: Sets the foreign key column in all matching child rows to NULL upon parent deletion.

Key Takeaways

• A primary key uniquely identifies every row within its own table and never permits NULL values.
• A foreign key preserves referential integrity by pointing to an existing unique or primary key in a parent table.
• PostgreSQL automatically indexes primary keys, but you must index foreign key columns manually to prevent table scans.

Frequently Asked Questions

Can a foreign key column contain NULL values?

Yes, foreign key columns accept NULL values by default unless you explicitly restrict them with a NOT NULL constraint.

Can a table have more than one primary key?

No, a table can only have one primary key constraint, although that single key can combine multiple columns into a composite key.

Can a foreign key reference a column that is not a primary key?

Yes, a foreign key can reference any parent column that has a UNIQUE constraint or a unique index.

Understanding how primary and foreign keys interact ensures your database prevents orphaned data while maintaining fast query performance across normalized tables.