POSTGRESQL
SQL

A Definitive Guide to Postgres Foreign Key

intro

In this blog, we’re walking you through the Postgres foreign key constraint. Join us!

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

Have you ever stumbled upon a tangled web of tables in your PostgreSQL database, yearning for a way to fabricate order from the chaos? Enter the Postgres foreign key: your valiant thread, expertly linking tables and ensuring harmonious data relationships.

Imagine a bustling marketplace, overflowing with products and orders, yet each order is neatly tied to its rightful product. Foreign keys play that role, acting as silent keepers of referential integrity, preventing orphaned records, and maintaining the sanctity of our data kingdom. So, buckle up, database developers, as we journey to decode the puzzle of PostgreSQL foreign keys!

Introduction to the Postgres Foreign Key Constraint

In PostgreSQL, a foreign key is an accessory or qualifier uniquely identifying a row in another table. It refers to a column or set of columns and establishes a link between tables in a Postgres database.

It acts as a bridge between tables, enforcing defined relationships by ensuring that data in a child table corresponds to data in a parent table, thereby ensuring PostgreSQL data integrity.

How does the Postgres foreign key constraint do this? The foreign key constraint establishes a link between the data in two tables by referencing the primary key or a unique constraint of the referenced table.

The PostgreSQL Foreign Key Constraint Syntax

This is the syntax for defining a PostgreSQL foreign key constraint:

Copy
        
1 [CONSTRAINT fk_name] 2 FOREIGN KEY(fk_columns) 3 REFERENCES parent_table(parent_key_columns) 4 [ON DELETE delete_action] 5 [ON UPDATE update_action]

Breaking down each line of the syntax:

1.CONSTRAINT fk_name

  • You specify the name of the foreign key constraint after the CONSTRAINT keyword. This is an optional clause that gives a name to the foreign key constraint, making it easier to identify and manage. Postgres will automatically assign a name to the foreign key constraint if the constraint name is omitted.

2. FOREIGN KEY(fk_columns)

  • This section of the syntax declares the creation of a foreign key constraint.
  • fk_columns specifies one or more columns in the current table that will reference the parent table.

3. REFERENCES parent_table(parent_key_columns)

  • Establishes the link to the parent table.
  • parent_table denotes the name of the table being referenced.
  • parent_key_columns specifies the columns in the parent table that the foreign key columns will reference, commonly the primary key or a unique constraint.

4. [ON DELETE delete_action]

  • This is an optional clause defining what happens to child rows when a row in the parent table is deleted. Common actions include:
    • **CASCADE**: Automatically deletes child rows that reference the deleted parent row.
  • **RESTRICT**: Prevent deletion of the parent row if child rows exist, maintaining data integrity.
  • **SET NULL**: Sets foreign key columns in child rows to NULL if the parent row is deleted.
  • **SET DEFAULT**: This sets foreign key columns to their default values.

5. [ON UPDATE update_action]

  • Similar to ON DELETE, but specifies actions when a parent key value is updated. Options include CASCADE, RESTRICT, SET NULL, and SET DEFAULT, NO ACTION.

A simple example of how to create a table with a foreign key in Postgres is:

Copy
        
1 CREATE TABLE transactions ( 2 transactions_id SERIAL PRIMARY KEY, 3 product_id INT, 4 quantity INT, 5 FOREIGN KEY (product_id) REFERENCES products(product_id) 6 );

In the above query, the transactions table has a foreign key constraint on the product_id column referencing the product_id column in the products table. This is to ensure that every product_id in the transactions table is in tune with a valid product_id in the products table, maintaining referential integrity.

Creating PostgreSQL Foreign Key Constraints

To create a foreign key constraint in PostgreSQL, we need to:

  1. Define the referencing and referenced tables.
  2. Specify the columns involved in the relationship.
  3. Define the actions to be taken to update or delete referenced rows.

This is an example to understand the process better:

Suppose we have two tables, orders and transactions, and we want to create a foreign key relationship between them so that the orders table's id column references the order_id column in the transactions table. To do this, we would first create our parent table, orders:

Copy
        
1 CREATE TABLE orders ( 2 id SERIAL PRIMARY KEY, 3 name VARCHAR(100) 4 );

Next, we will create the child table, transactions:

Copy
        
1 CREATE TABLE transactions ( 2 id SERIAL PRIMARY KEY, 3 transaction_number VARCHAR(20), 4 order_id INT, 5 FOREIGN KEY (order_id) REFERENCES orders(id) 6 );

After running these queries in the SQL Commander of DbVisualizer, refresh the objects tree by right clicking on the tables node to view the list of all current tables in the database as shown below:

A view of the lists of all tables in the current database
A view of the lists of all tables in the current database
Copy
        
1 A view of the lists of all tables in the current database

In the transactions table, we defined the order_id column as a foreign key that references the id column in the orders table.

Notice that the Postgres foreign key constraint here will default to NO ACTION because the foreign key constraint does not have the ON DELETE and ON UPDATE action.

Other Postgres Foreign Key Constraint Actions

In this section of the blog, we’re going to expand on the example of modifying foreign key constraints by exploring different actions such as SET NULL, CASCADE, and SET DEFAULT.

SET NULL

One of the foreign key constraint actions is the SET NULL action. This action allows you to set the value of the foreign key column to NULL when the referenced row is updated or deleted.

Consider an example where we have two tables: employees and departments. The employees table has a foreign key constraint referencing the id column of the departments table. We want to set the foreign key column to NULL when the referenced department is deleted.

But first, we will create the sample tables with the foreign key that uses the SET NULL action in the ON DELETE clause:

Copy
        
1 CREATE TABLE departments ( 2 id SERIAL PRIMARY KEY, 3 department_name VARCHAR(100) 4 );
Copy
        
1 CREATE TABLE employees ( 2 id SERIAL PRIMARY KEY, 3 name VARCHAR(100), 4 department_id INT, 5 FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL 6 );
The foreign key column set to NULL in DbVisualizer
The foreign key column set to NULL in DbVisualizer
Copy
        
1 The foreign key column set to NULL in DbVisualizer

Next, we will populate the deparments and employees tables:

Copy
        
1 INSERT INTO departments (department_name) VALUES 2 ('IT'), 3 ('Finance'), 4 ('Production');
Copy
        
1 INSERT INTO employees (name, department_id) VALUES 2 ('Jon Snow ', 1), 3 ('Janet Villa', 2), 4 ('Bobby Carson', 3);
Populating ‘departments’ and ‘employees’ tables
Populating departments and employees tables
Copy
        
1 Populating ‘departments’ and ‘employees’ tables

In this example, when a department is deleted from the departments table, the department_id column in the employees table will be set to NULL for all employees associated with the deleted department.

Now, let’s delete the department with id = 1 from the departments table:

Copy
        
1 DELETE FROM departments 2 WHERE id = 1;

Because of the ON DELETE SET NULL action, the referencing rows in the employees table are set to NULL.

Now that we’ve implemented a SET NULL action, we can verify the data in the departments table to determine the effect of the action by running the query below:

Copy
        
1 SELECT * FROM departments;
SET NULL action in DbVisualizer
SET NULL action in DbVisualizer
Copy
        
1 SET NULL action in DbVisualizer

From the query outputs above, we can see that the value of the department id 1 changed to NULL.

SET DEFAULT

The ON DELETE SET DEFAULT Postgres foreign key action allows you to set the value of the foreign key column to its default value when the referenced row is updated or deleted.

Consider an example where we have two tables: users and user_profiles. The user_profiles table has a foreign key constraint referencing the id column of the users table.

The goal is to set the foreign key column to its default value when the referenced user is deleted, but let’s first create the sample tables as shown below:

Copy
        
1 CREATE TABLE users ( 2 id SERIAL PRIMARY KEY, 3 username VARCHAR(100) 4 );

And here's how we’ll create the user_profiles table with a foreign key constraint using SET DEFAULT action:

Copy
        
1 CREATE TABLE user_profiles ( 2 id SERIAL PRIMARY KEY, 3 user_id INT, 4 profile_data TEXT, 5 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET DEFAULT 6 );

The next thing is to populate the users and users_profiles tables:

Copy
        
1 INSERT INTO users (username) VALUES 2 ('Joy'), 3 ('Leslie'), 4 ('Carter');
Copy
        
1 INSERT INTO user_profiles (user_id, profile_data) VALUES 2 (1, 'Product Manager'), 3 (2, 'Software Engineer'), 4 (3, 'Technical Product Manager');
Populating ‘users’ and ‘user_profiles’ sample tables in DbVisualizer
Populating users and user_profiles sample tables in DbVisualizer
Copy
        
1 Populating ‘users’ and ‘user_profiles’ sample tables in DbVisualizer

In this example, we have specified ON DELETE SET DEFAULT as part of the foreign key constraint on the user_id column in the user_profiles table. This means that if a row is deleted from the users table and there are referencing rows in the user_profiles table, the user_id column in the user_profiles table will be set to its default value.

Now, say we delete a user from the users table:

Copy
        
1 DELETE FROM users 2 WHERE id = 1;

After executing the query, the row with id = 1 is deleted from the users table. Since there is a referencing row in the user_profiles table with user_id = 1, the user_id column in the user_profiles table will be set to its default value. The default value will depend on how you have defined it for the user_id column.

CASCADE

Another important Postgres foreign key constraint action is the ON DELETE CASCADE action. This particular PostgreSQL foreign key constraint allows you to automatically propagate changes to related rows when a referenced row is updated or deleted.

Consider an example where we have two tables: orders and order_items. The order_items table has a foreign key constraint referencing the id column of the orders table. We want to set the CASCADE action on deletion, so when an order is deleted, all associated order items are also removed.

Just as we did with the SET NULL action, we’ll first create the sample tables with the foreign key that uses the CASCADE action in the ON DELETE clause:

Copy
        
1 CREATE TABLE orders ( 2 id SERIAL PRIMARY KEY, 3 order_number VARCHAR(20) 4 );
Copy
        
1 CREATE TABLE order_items ( 2 id SERIAL PRIMARY KEY, 3 order_id INT, 4 item_name VARCHAR(100), 5 FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE 6 );
Creating ‘orders’ and ‘order_items’ sample tables in DbVisualizer
Creating orders and order_items sample tables in DbVisualizer
Copy
        
1 Creating ‘orders’ and ‘order_items’ sample tables in DbVisualizer

Now, populate the tables with data by running the query below:

Copy
        
1 INSERT INTO orders (order_number) VALUES 2 ('001'), 3 ('002'), 4 ('003');
Copy
        
1 INSERT INTO order_items (order_id, item_name) VALUES 2 (1, 'Item A'), 3 (1, 'Item B'), 4 (2, 'Item C'), 5 (3, 'Item D');
Populating ‘orders’ and ‘order_items’ sample tables in DbVisualizer
Populating orders and order_items sample tables in DbVisualizer
Copy
        
1 Populating ‘orders’ and ‘order_items’ sample tables in DbVisualizer

In this example, when an order is deleted from the orders table, all corresponding order items in the order_items table will be automatically deleted. To do this, let’s run this query:

Copy
        
1 DELETE FROM orders 2 WHERE id = 001;

To verify the successful execution of the ON DELETE CASCADE action, run this query to display the data in the order_items table:

Copy
        
1 SELECT * FROM order_items;

Because of the ON DELETE CASCADE action, all the referencing rows in the order_items table are automatically deleted:

ON DELETE CASCADE action successfully executed in DbVisualizer
ON DELETE CASCADE action successfully executed in DbVisualizer
Copy
        
1 ON DELETE CASCADE action successfully executed in DbVisualizer

Great! We’re done experimenting with how some of these actions work. Time to look at some of the best practices for working with PostgreSQL foreign keys!

Best Practices for Working With Postgres Foreign Key

Below are some of the best practices that should be taken into consideration when working with a Postgres foreign key:

  • Carefully consider the ON DELETE and ON UPDATE actions based on your specific needs.
  • Although CASCADE is convenient, it can lead to unexpected data loss. It’s always advisable to use it with caution or consider alternatives like RESTRICT or SET NULL.
  • Ensure to use descriptive names for foreign keys that convey their purpose and relationship and consider naming conventions that highlight the relationship between tables (e.g., fk_order_product, fk_customer_order).
  • Remember to use ANALYZE and EXPLAIN commands to monitor the performance and effectiveness of foreign keys.

It’s a wrap!

Conclusion

Postgres foreign keys provide a powerful mechanism for establishing relationships between tables and enforcing referential integrity.

Foreign keys ensure that the values in the referencing column ie. child table correspond to the values in the referenced column, ie. parent table. This prevents orphaned rows and inconsistencies in the data. With respect to foreign key actions, PostgreSQL offers a variety of actions to handle updates and deletions of referenced rows, such as CASCADE, SET NULL, and SET DEFAULT, allowing you to define the desired behavior when changes occur.

To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all database features, including CASCADE, and SET NULL actions. Download DbVisualizer for free now!

Frequently Asked Questions

What are foreign keys, and why use them?

Foreign keys are like glue in your database, connecting related tables and preventing data inconsistencies. They ensure that, for example, an order always references a valid product, and a customer always has a valid address. Without them, you'd end up with "orphaned" records and messy data.

How do you define a foreign key in Postgres?

You use the FOREIGN KEY constraint in your CREATE TABLE or ALTER TABLE statement. Just tell Postgres which columns in your table link to specific columns in another table (like product_id referencing products.id).

Can I delete a row in a parent table if it has child rows?

It depends on what you set for ON DELETE when defining the foreign key. You can choose to automatically delete child rows (CASCADE), prevent deletion (RESTRICT), or even set the foreign key columns in child rows to null.

What are the available actions for handling updates and deletions with foreign keys in PostgreSQL?

  • **SET NULL**: When a referenced row is updated or deleted, the foreign key column in the child table(s) is set to NULL. This means that if a referenced row is updated or deleted, the corresponding foreign key values in the child table(s) will be set to NULL.
  • **NO ACTION**: This is the default action in PostgreSQL. If a referenced row is updated or deleted and there are still dependent rows in the child table(s), an error is raised, and the update or deletion is not allowed.
  • **CASCADE**: When a referenced row is updated or deleted, the changes are automatically propagated to the related rows in the child table(s). This means that if a referenced row is updated or deleted, all associated rows in the child table(s) will also be updated or deleted.
  • **RESTRICT**: Similar to NO ACTION, if a referenced row is updated or deleted and there are still dependent rows in the child table(s), an error is raised, and the update or deletion is not allowed. RESTRICT is the same as NO ACTION and is included for compatibility with other database systems.
  • **SET DEFAULT**: When a referenced row is updated or deleted, the foreign key column in the child table(s) is set to its default value. This means that if a referenced row is updated or deleted, the corresponding foreign key values in the child table(s) will be set to their default values, if any are defined.
Dbvis download link img
About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04
title

MySQL ALTER TABLE Explained

author Lukas Vileikis tags MySQL SQL 7 min 2024-06-27
title

Error: MySQL Shutdown Unexpectedly. Causes & Solutions

author Lukas Vileikis tags MySQL SQL 4 min 2024-06-24
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17
title

SQL Server DATEADD: The Complete Guide

author Ochuko Onojakpor tags SQL SQL SERVER 7 min 2024-06-10
title

mysqldump: How to Backup and Restore MySQL Databases

author Antonello Zanini tags Backup MySQL SQL 11 min 2024-06-06
title

The SQL DELETE Statement Explained

author Leslie S. Gyamfi tags DELETE SQL 4 min 2024-06-03

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗