DbSchema | How to Use Foreign Key in PostgreSQL?
Table of Contents
- 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.
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