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

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.

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:

CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    customer_id integer REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date date
);

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:

CREATE TABLE employees (
    employee_id serial PRIMARY KEY,
    first_name varchar(50),
    last_name varchar(50),
    manager_id integer REFERENCES employees(employee_id) ON DELETE CASCADE
);

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:

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.

About the author

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