FOREIGN KEY
POSTGRESQL

How to Use a FOREIGN KEY Referring to the Source Table in Postgres

intro

PostgreSQL is an open-source relational database management system that is widely used in web applications. One of the essential features of PostgreSQL is the ability to use foreign keys to establish relationships between tables. A foreign key is a field or combination of fields in a table that refers to the primary key of another table. In this blog post, we will discuss how to use a FOREIGN KEY that is referring to the source table in PostgreSQL, with the CASCADE option.

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

Understanding Foreign Keys in PostgreSQL

Before diving into the details of using a FOREIGN KEY that is referring to the source table in PostgreSQL, let's first understand what foreign keys are and how they work in PostgreSQL.

A foreign key is a field or combination of fields in a table that refers to the primary key of another table. It establishes a link between two tables and ensures that the data in the related tables are consistent. In PostgreSQL, we can create a foreign key constraint using the FOREIGN KEY keyword in the CREATE TABLE statement.

Here is an example of a foreign key constraint:

Copy
        
1 CREATE TABLE orders ( 2     order_id serial PRIMARY KEY, 3     customer_id integer REFERENCES customers(customer_id) ON DELETE CASCADE, 4     order_date date 5 );

In the above example, the orders table has a foreign key constraint that references the customer_id column in the customers table. The ON DELETE CASCADE option ensures that if a customer is deleted from the customers table, all orders associated with that customer will also be deleted from the orders table.

Using a FOREIGN KEY Referring to the Source Table in PostgreSQL

A FOREIGN KEY that is referring to the source table in PostgreSQL is used when we want to create a self-referencing relationship in a table. For example, let's say we have a table called employees, and we want to create a relationship between the employee_id and manager_id columns in the same table. We can do this using a FOREIGN KEY constraint that refers to the same table.

Here is an example of how to create a self-referencing FOREIGN KEY constraint in PostgreSQL:

Copy
        
1 CREATE TABLE employees ( 2     employee_id serial PRIMARY KEY, 3     first_name varchar(50), 4     last_name varchar(50), 5     manager_id integer REFERENCES employees(employee_id) ON DELETE CASCADE 6 );

In the above example, the employees table has a foreign key constraint that references the employee_id column in the same table. The ON DELETE CASCADE option ensures that if an employee is deleted from the table, all employees who report to that employee will also be deleted.

Using CASCADE with a FOREIGN KEY Constraint

In PostgreSQL, the CASCADE option can be used with a FOREIGN KEY constraint to automatically delete related rows from the child table when a row is deleted from the parent table. This option is useful when we want to maintain data consistency between related tables.

Let's take the example of the employees table we created earlier. If we want to delete an employee from the table, we can use the following SQL statement:

Copy
        
1 DELETE FROM employees WHERE employee_id = 10;

If the ON DELETE CASCADE option is set in the FOREIGN KEY constraint, all employees who report to the deleted employee will also be deleted from the table.

Conclusion

In conclusion, a FOREIGN KEY constraint is an essential feature of PostgreSQL that helps to establish relationships between tables and maintain data consistency. Using a FOREIGN KEY that is referring to the source table in PostgreSQL allows us to create self-referencing relationships in a table. Additionally, using the CASCADE option with a FOREIGN KEY constraint ensures that related rows in the child table are automatically deleted when a row is deleted from the parent table. We hope this blog post has provided you with a better understanding of how to use a FOREIGN KEY that is referring to the source table in PostgreSQL with the CASCADE option.

Dbvis download link img
About the author
TheTable
TheTable

The Table by DbVisualizer is where we gather together to learn about and simplify the complexity of working with database technologies.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

PostgreSQL Upsert: INSERT ON CONFLICT Guide

author Antonello Zanini tags POSTGRESQL UPSERT 7 min 2024-03-25
title

Postgres TEXT vs VARCHAR: Comparing String Data Types

author Antonello Zanini tags POSTGRESQL TEXT VARCHAR 6 min 2024-03-14
title

Schemas in PostgreSQL

author Leslie S. Gyamfi tags POSTGRESQL SCHEMA 6 min 2024-03-11
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22

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 ↗