To create relationships between tables and enforce referential integrity in PostgreSQL, the FOREIGN KEY constraint is your best approach. This key ensures that data remains consistent across related tables and maintains the relational structure of PostgreSQL databases.
Table of contents- Learn the basics of the PostgreSQL FOREIGN KEY constraint
- Add FOREIGN KEY while creating a table
- Add FOREIGN KEY to an existing table
- How to drop FOREIGN KEY in PostgreSQL
- Why is a GUI tool a better way to manage keys in PostgreSQL?
- Summary
This article is the second in our series on PostgreSQL database constraints, following our recent review of Primary Keys. Since both primary keys and foreign keys are fundamental for enforcing data integrity in PostgreSQL tables, understanding how to work with them is essential for effective database design.
In this article, you’ll learn the following topics:
- Understand the basics of the PostgreSQL foreign key constraint.
- Create, drop, and manage foreign keys with real-world SQL examples.
- Work with one of the most convenient PostgreSQL GUI tools for handling foreign keys.
Learn the basics of the PostgreSQL FOREIGN KEY constraint
What is FOREIGN KEY in PostgreSQL?
A foreign key in PostgreSQL is a constraint that links one or more columns in a table (the child or referencing table) to the primary key of another table (the parent or referenced table) and ensures referential integrity between the two.
The table that contains the foreign key is called the child table, while the table referenced by the foreign key is known as the parent table. A foreign key is sometimes referred to as the referencing key because it matches the primary key of the parent table. In other words, a foreign key field in one table points to the primary key field of another.
Foreign keys, along with others like primary keys, check constraints, and unique constraints, all belong to the family of PostgreSQL database constraints. These constraints define rules on table columns and prevent invalid or inconsistent data from being inserted into the database.
How to define a FOREIGN KEY in SQL (Example)
Here’s how you can create two related tables in PostgreSQL using a foreign key constraint. In this example, there is a sportspeople table, which includes a foreign key column sport_id that references the primary key column sport_id in the sports table. This establishes a one-to-many relationship, meaning one sport can have multiple sportspeople associated with it.
CREATE TABLE sports (
sport_id INT,
sport_name VARCHAR(255) NOT NULL,
PRIMARY KEY (sport_id)
);
CREATE TABLE sportspeople (
sportsperson_id INT,
sport_id INT,
sportperson_name VARCHAR(255) NOT NULL,
PRIMARY KEY (sportsperson_id),
CONSTRAINT fk_sport_id
FOREIGN KEY (sport_id)
REFERENCES sports (sport_id)
); 
Note
A PostgreSQL table can include multiple foreign keys if it needs to reference more than one related table.
Add FOREIGN KEY while creating a table
To define a foreign key during table creation in PostgreSQL, use the CONSTRAINT … FOREIGN KEY … REFERENCES … syntax. You can also add a foreign key later with an ALTER TABLE statement or create it visually using a PostgreSQL GUI tool like dbForge Studio for PostgreSQL. Here is the syntax to define your foreign key.
PostgreSQL FOREIGN KEY Syntax
CONSTRAINT [constraint_name] FOREIGN KEY (fk_columns)
REFERENCES parent_table (parent_table_columns)
[ON DELETE action]
[ON UPDATE action] In this syntax:
- constraint_name represents the name of the foreign key constraint. If not specified, PostgreSQL generates one automatically using its default naming convention.
- fk_columns is the column(s) in the child table that will act as the foreign key.
- parent_table is the name of the referenced (parent) table, followed by the column(s) it exposes as the primary key.
- ON DELETE / ON UPDATE are optional clauses that define what happens when the referenced row in the parent table is deleted or updated.
Also, PostgreSQL supports the following referential actions:
- NO ACTION
- RESTRICT
- SET NULL
- SET DEFAULT
- CASCADE
Let’s explore each of these actions in more detail below.
What does NO ACTION mean in PostgreSQL?
The NO ACTION referential action prevents deleting or updating a referenced row if it would violate a foreign key constraint. Instead, PostgreSQL raises an error. This procedure is the default behavior when no other action is specified.
Here’s how it behaves when you try to delete a referenced row:
CREATE TABLE sports (
sport_id INT,
sport_name VARCHAR(255) NOT NULL,
PRIMARY KEY (sport_id)
);
CREATE TABLE sportspeople (
sportsperson_id INT,
sport_id INT,
sportperson_name VARCHAR(255) NOT NULL,
PRIMARY KEY (sportsperson_id),
CONSTRAINT fk_sport_id
FOREIGN KEY (sport_id)
REFERENCES sports (sport_id)
ON DELETE NO ACTION
); If you attempt to delete a row from the parent sports table:
DELETE FROM sports
WHERE sport_id = 2; 
PostgreSQL will issue an error because the referenced sport_id still exists in the sportspeople table, and deleting it would break referential integrity.
RESTRICT
In PostgreSQL, the RESTRICT referential action prevents the deletion of a parent row if any child rows reference it, and it does so immediately, without deferral. This means that the check is enforced at the moment the DELETE or UPDATE command is executed, rather than waiting until the end of the transaction.
For example, if you attempt to run the following command:
DELETE FROM sports
WHERE sport_id = 1; If there are still rows in the sportspeople table referencing sport_id = 1, PostgreSQL will block the operation and return an error. Here is how the RESTRICT differs from NO ACTION.
Key difference:
- NO ACTION: constraint check can be deferred until the end of the transaction.
- RESTRICT: constraint check happens immediately and cannot be deferred.
This method of handling constraints makes RESTRICT slightly stricter than NO ACTION in terms of timing.
SET NULL
In PostgreSQL, SET NULL is a referential action that sets the foreign key column in the child table to NULL when the referenced parent row is deleted or updated. This ensures that the child record remains in the table, but its link to the parent is cleared.
Example: If the sportspeople table uses ON DELETE SET NULL for its foreign key and a row is deleted from the sports table, the corresponding sport_id values in sportspeople will automatically become NULL.
As you can see from the output, the row that had sport_id equal to 1 now has it set to NULL.
Before deletion:
| sportsperson_id | sport_id | sportperson_name |
|---|---|---|
| 2 | 2 | John Cena |
| 1 | 1 | Michael Phelps |
After deletion of sport_id = 1 from sports:
| sportsperson_id | sport_id | sportperson_name |
|---|---|---|
| 2 | 2 | John Cena |
| 1 | NULL | Michael Phelps |
This action is useful when you want to preserve child records but remove their dependency on the deleted or updated parent row.
SET DEFAULT
In PostgreSQL, SET DEFAULT sets the child table’s foreign key column to its defined default value when the referenced row in the parent table is deleted or updated. This action works only if the child column has a default value specified.
For example, suppose the sportspeople table defines a foreign key on sport_id with ON DELETE SET DEFAULT and a default value of 2. If a row in the parent sports table with sport_id = 1 is deleted, the matching rows in sportspeople will have their sport_id automatically reset to the default value 2. Here is how this works in practice.
First, let’s create the table:
If we now delete the parent row with sport_id = 1:
DELETE FROM sports
WHERE sport_id = 1;
SELECT * FROM sportspeople; 
Before deletion:
| sportsperson_id | sport_id | sportperson_name |
|---|---|---|
| 1 | 1 | John Cena |
| 2 | 2 | Michael Phelps |
After deletion of sport_id = 1 from sports:
| sportsperson_id | sport_id | sportperson_name |
|---|---|---|
| 2 | 2 | John Cena |
| 1 | 2 | Michael Phelps |
With ON DELETE SET DEFAULT, the orphaned row is reassigned to the default value (2 in this case). This behavior is useful when you want to redirect orphaned child rows to a fallback reference rather than removing or nullifying the relationship.
CASCADE
The CASCADE referential action automatically deletes or updates child rows when any change is made to the referenced parent row. With ON DELETE CASCADE, removing a row in the parent table also removes all related rows in the child table. With ON UPDATE CASCADE, changes to the parent’s primary key value also result in changes in the child table automatically.
Here’s an example using ON UPDATE CASCADE:
First, you’ll create two tables—sports and sportspeople—but this time add a foreign key with ON UPDATE CASCADE.
Now update the sports table, changing the value in the primary key column.
UPDATE sports
SET sport_id = 5
WHERE sport_id = 1; Here is how this action affects the data in the child sportspeople table.
Before update:
| sportsperson_id | sport_id | sportperson_name |
|---|---|---|
| 2 | 2 | John Cena |
| 1 | 1 | Michael Phelps |
After update of sport_id = 1 → 5 in sports:
| sportsperson_id | sport_id | sportperson_name |
|---|---|---|
| 2 | 2 | John Cena |
| 1 | 5 | Michael Phelps |
As shown, the child table automatically reflects the updated key value, preserving referential integrity without manual intervention.
Add FOREIGN KEY to an existing table
To add a foreign key to an existing PostgreSQL table, use the ALTER TABLE ... ADD CONSTRAINT syntax. This allows you to enforce referential integrity even after the table has already been created.
For example, here is how to add a foreign key to the orders table that references the customers table:
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers (id); If you need to introduce a new column to serve as a foreign key, you can add it first and then define the constraint:
To add a new column, use the following syntax:
ALTER TABLE orders
ADD COLUMN IF NOT EXISTS city_id INT; To add a foreign key on the new column, use the following syntax:
ALTER TABLE orders
ADD CONSTRAINT fk_city_id
FOREIGN KEY (city_id) REFERENCES city (id); Note: Both adding and dropping foreign keys in PostgreSQL are performed using the ALTER TABLE statement.
How to drop FOREIGN KEY in PostgreSQL
To remove a foreign key constraint in PostgreSQL, use the ALTER TABLE command followed by DROP CONSTRAINT. This statement detaches the foreign key from the table, meaning the database will no longer enforce the relationship.
Syntax:
ALTER TABLE table_name
DROP CONSTRAINT foreign_key_name; What it does:
- table_name: the name of the child table that contains the foreign key.
- foreign_key_name: the name of the foreign key constraint to be removed.
Example:
The following example demonstrates how to drop the foreign key constraint named fk_sport_id from the sportspeople table:
After this command, the sportspeople table will no longer enforce a foreign key relationship on the sport_id column.
ALTER TABLE sportspeople DROP CONSTRAINT fk_sport_id;
After this command, the sportspeople table will no longer enforce a foreign key relationship on the sport_id column.
Why is a GUI tool a better way to manage keys in PostgreSQL?
Why GUI is better than CLI
While PostgreSQL offers powerful command-line capabilities, managing keys through SQL commands can be challenging, especially for beginners. The command line requires memorizing syntax, remembering table and column names, and troubleshooting errors when constraints fail. This requirement increases both the learning curve and the risk of mistakes.
However, one of the easiest ways to manage keys is to use a graphical user interface (GUI) tool. GUI tools simplify this process by providing visual interactions, intuitive menus, and built-in validation. Instead of typing every command manually, you can create, modify, or drop keys with a few clicks.
One of the most efficient GUI tools is dbForge Studio for PostgreSQL.
How dbForge Studio solves these problems
dbForge Studio for PostgreSQL is an advanced GUI IDE designed to streamline database development and administration. It reduces complexity and speeds up common tasks, making it easier for developers, administrators, and analysts to manage keys and constraints without worrying about syntax errors. Here is a breakdown of the key features in dbForge Studio that can help you manage keys efficiently in PostgreSQL.
Key features for managing keys in dbForge Studio:
- Instant syntax checking: with dbForge Studio for PostgreSQL, you can easily prevent errors with real-time validation as you write queries.
- Intelligent code completion: this is one outstanding feature for managing keys in your database. With this, you get context-aware suggestions for tables, columns, and constraints.
- Master-detail browser: with dbForge Studio for PostgreSQL, you can view and analyze related data across tables in an intuitive interface.
- Cross-platform convenience: dbForge Studio for PostgreSQL works with PostgreSQL databases on all major operating systems.
dbForge Studio for PostgreSQL eliminates the need to memorize commands and makes key management more efficient, accurate, and user-friendly.
Summary
In summary, PostgreSQL foreign keys are essential for maintaining referential integrity by enforcing relationships between parent and child tables. In this guide, we have explored how to define a foreign key constraint when creating a table with CREATE TABLE or later with ALTER TABLE. We also reviewed how to remove a key using DROP CONSTRAINT.
Different referential actions, such as CASCADE, SET NULL, SET DEFAULT, RESTRICT, and NO ACTION, determine how updates or deletions in the parent table affect related rows in the child table. For example, CASCADE is useful when child rows should follow parent changes, while SET NULL or SET DEFAULT preserve rows by adjusting values instead.
By carefully choosing the right action for each relationship, you can ensure that your database remains consistent, synchronized, and free from orphaned records after any operation.
Try before you buy
Download a free 30-day trial of dbForge Studio for PostgreSQL, and see how it can handle your database challenges.
