DbSchema | How to Use Foreign Key in PostgreSQL?

PostgreSQL • 02-Jun-2023

by Malik Umair Sajjad

Introduction
Prerequisites
What is a Foreign Key?
Advantages of Using a Foreign Key
Limitations of Using a Foreign Key
Restrictions on Using a Foreign Key
Using Foreign Keys in psql
Using Foreign Keys in DbSchema
Conclusion
References

Introduction

PostgreSQL is a powerful open-source relational database management system that provides various features for managing data __integrity and relationships. One such essential feature is the use of foreign keys, which establish relationships between tables in a database. In this article, we will explore how to utilize foreign keys in __psql, the command-line interface for PostgreSQL, and __DbSchema`, a visual database design and management tool.

Prerequisites

Before we proceed, ensure you have the following prerequisites in place:

__PostgreSQL` installed on your system
Access to __psql`, the PostgreSQL command-line interface
Access to __DbSchema`, either the installed application or the web-based version
__Basic knowledge` of SQL and database concepts

For installation and establishing connection refer to PostgreSQL-How to create a database?

What is a Foreign Key?

A foreign key is a column or a combination of columns in a table that establishes a link to the primary key or a unique key of another table. It creates a relationship between two tables, known as the parent table and the child table. The foreign key constraint ensures referential integrity, meaning that the values in the foreign key column must exist in the referenced table’s primary key or unique key column.

Foreign Key Overview alt

Supported Actions:

PostgreSQL supports following actions:

*Action*	*Description*
`SET NULL`	Sets the foreign key column(s) in the referencing table to NULL when a referenced row is deleted or updated.
`SET DEFAULT`	Sets the foreign key column(s) in the referencing table to their default values defined when creating the table.
`RESTRICT`	Prevents deletion or update of a referenced row if there are any referencing rows, ensuring referential integrity.
`NO ACTION`	Prevents deletion or update of a referenced row if there are any referencing rows without deferring the constraint.
`CASCADE`	Deletes or updates all referencing rows when a referenced row is deleted or updated, maintaining data integrity across the relationship.

Advantages of Using a Foreign Key

Using foreign keys offers several advantages:

Data Integrity: Foreign keys enforce data integrity by preventing inconsistent or orphaned records. They ensure that the data in the child table references valid data in the parent table.
Relationships: Foreign keys allow you to establish relationships between tables, enabling efficient querying and data retrieval based on these relationships.
Cascading Actions: Foreign keys can be configured to perform cascading actions such as cascading updates and deletes, which propagate changes made to the parent table to the child table automatically.
Query Optimization: By defining foreign key relationships, the database optimizer can generate more efficient query plans, leading to improved performance.

Limitations of Using a Foreign Key

While foreign keys offer numerous benefits, they also have certain limitations:

Performance Impact: Foreign keys can impact database performance, especially during data modification operations, as they require additional checks and validations.
Complexity: The proper use of foreign keys requires careful consideration of the relationships between tables and adherence to the defined constraints. This complexity can increase as the number of tables and relationships grows.
Maintenance Overhead: Modifying or deleting tables involved in foreign key relationships can be more complex and time-consuming, as dependencies must be managed to maintain data integrity.

Restrictions on Using a Foreign Key

There are a few restrictions to keep in mind when working with foreign keys in PostgreSQL:

The referenced table must have a primary key or a unique key constraint defined on the referenced column(s).
The data types of the foreign key and the referenced key must match.
You cannot directly modify or delete a row in a referenced table if it has dependent rows in the child table.

Using Foreign Keys in psql

Let’s now explore how to use foreign keys in psql with step-by-step instructions and examples:

Step 1: Create the Parent Table


CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

Step 2: Create the Child Table with a Foreign Key Constraint


CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    author_id INT REFERENCES authors(author_id)
);

Sample Database:

Table: authors

_author_id_	_name_
`1`	John Smith
`2`	Jane Doe
`3`	Mark Johnson
`4`	Emily Wilson

Table: books

_book_id_	*title*	_author_id_
`1`	Book 1	2
`2`	Book 2	1
`3`	Book 3	3
`4`	Book 4	4

Step 3: Insert Data into the Parent and Child Tables


-- Inserting data into the authors table
INSERT INTO authors (name) VALUES ('John Smith'), ('Jane Doe'), ('Mark Johnson'), ('Emily Wilson');

-- Inserting data into the books table
INSERT INTO books (title, author_id) VALUES ('Book 1', 2), ('Book 2', 1), ('Book 3', 3), ('Book 4', 4);

Step 4: Retrieve Data with Foreign Key References


-- Retrieve books along with their authors
SELECT b.title, a.name
FROM books b
JOIN authors a ON b.author_id = a.author_id;

Result from Query:

Following is the result obtained by executing query on the sample database

_title_	_name_
`Book 1`	Jane Doe
`Book 2`	John Smith
`Book 3`	Mark Johnson
`Book 4`	Emily Wilson

Step 5: Update and Delete Data with Foreign Key Constraints


-- Update the author of a book
UPDATE books SET author_id = 2 WHERE book_id = 1;

-- Delete a book
DELETE FROM books WHERE book_id = 2;

Results from Query:

Following are the results obtained by executing query on the sample database

Update:

_book_id_	*title*	_author_id_
`1`	Book 1	2

Delete:

No result to display.

Note:

Since the book with book_id = 2 was deleted, there is no result to display for this query.

Using Foreign Keys in DbSchema

Now, let’s see how to utilize foreign keys in DbSchema:

Step 1: Connect to the PostgreSQL Database

For installation and establishing connection refer to PostgreSQL-How to create a database?

Step 2: Create the Parent Table

To create a table in psql refer to PostgreSQL-How to Create a Table?

Step 3: Create the Child Table with a Foreign Key Constraint

Right-click on the “Tables” section and select “Create Table.”
Define the table structure, including the foreign key column, in the table editor.
In the “Foreign Key” tab, select the referenced table and column.

Step 4: Insert Data into the Parent and Child Tables

Double-click on the parent table and click on the “Data” tab.
Enter the required data in the table grid.
Repeat the same process for the child table.

Step 5: Retrieve Data with Foreign Key References

Use the visual query builder or SQL editor in DbSchema to construct a query that retrieves data from the parent and child tables using the foreign key relationship.

Step 6: Update and Delete Data with Foreign Key Constraints

Use the table editor in DbSchema to update or delete records in the parent or child tables.
DbSchema will enforce the foreign key constraints to ensure data integrity.

Visually Manage PostgreSQL using DbSchema

DbSchema is a PostgreSQL client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

Foreign keys are a crucial aspect of database design and management. They establish relationships between tables, promote data integrity, and improve query optimization. Both psql and DbSchema offer convenient methods to work with foreign keys in PostgreSQL. By following the steps outlined in this article, you can confidently utilize foreign keys to build robust and relational databases.

References

PostgreSQL Documentation: https://www.postgresql.org/docs/
DbSchema Documentation: https://www.dbschema.com/documentation/