How to Add Foreign Key in PostgreSQL: Syntax and Examples

To add a FOREIGN KEY in PostgreSQL, use the ALTER TABLE statement with ADD CONSTRAINT specifying the foreign key column and referenced table. This enforces a link between two tables ensuring data integrity.

Syntax

The basic syntax to add a foreign key in PostgreSQL is:

• ALTER TABLE table_name: Specifies the table to modify.
• ADD CONSTRAINT constraint_name: Names the foreign key constraint.
• FOREIGN KEY (column_name): Defines the column in the current table that will reference another table.
• REFERENCES other_table (other_column): Specifies the referenced table and column.

sql

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name)
REFERENCES other_table (other_column);

Example

This example shows how to add a foreign key to link orders.customer_id to customers.id. It ensures every order references a valid customer.

sql

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT
);

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers (id);

Output

CREATE TABLE
CREATE TABLE
ALTER TABLE

Common Pitfalls

Common mistakes when adding foreign keys include:

• Referencing a column that is not a primary key or unique in the other table.
• Adding a foreign key on columns with mismatched data types.
• Trying to add a foreign key when existing data violates the constraint.
• Not naming the constraint, which can make error messages harder to understand.

Example of a wrong and right way:

sql

-- Wrong: referencing a non-unique column
ALTER TABLE orders
ADD CONSTRAINT fk_wrong
FOREIGN KEY (customer_id)
REFERENCES customers (name);

-- Right: referencing the primary key
ALTER TABLE orders
ADD CONSTRAINT fk_correct
FOREIGN KEY (customer_id)
REFERENCES customers (id);

Quick Reference

Command	Description
ALTER TABLE table_name	Selects the table to modify
ADD CONSTRAINT constraint_name	Names the foreign key constraint
FOREIGN KEY (column_name)	Defines the foreign key column
REFERENCES other_table (other_column)	Specifies the referenced table and column
ON DELETE CASCADE	Optional: deletes dependent rows automatically
ON UPDATE CASCADE	Optional: updates dependent rows automatically

Key Takeaways

Use ALTER TABLE with ADD CONSTRAINT to add a foreign key in PostgreSQL.

The foreign key column must match the referenced column's data type and be unique or primary key.

Name your foreign key constraints for easier management and error tracking.

Check existing data for violations before adding a foreign key constraint.

Optional ON DELETE and ON UPDATE actions control behavior on referenced row changes.