Home
PostgreSQL

PostgreSQL Foreign Key

Learn how PostgreSQL Foreign Key ensure data integrity by enforcing relationships between tables. Explore their role in maintaining database...

PostgreSQL Foreign key is a crucial aspect of maintaining data integrity and enforcing relationships between tables within a database. They play a pivotal role in ensuring that the data remains consistent and accurate. 

PostgreSQL Foreign Key

In this article, we'll delve into the concept of foreign key in PostgreSQL, explore their functionality, syntax, and best practices, and discuss their importance in database design.

What is PostgreSQL Foreign Key?

In database management, a foreign key is a field or a set of fields in one table that uniquely identifies a record in another table. It establishes a relationship between two tables by referencing the primary key of another table. This relationship is known as a parent-child relationship, where the table containing the foreign key is the child table, and the table containing the referenced primary key is the parent table.

Syntax of Foreign Key in PostgreSQL

In PostgreSQL, foreign keys are defined at the time of table creation using the REFERENCES keyword.

CREATE TABLE child_table (
    column1 data_type PRIMARY KEY,
    column2 data_type,
    foreign_key_column data_type REFERENCES parent_table(parent_key_column)
);

Break down the syntax:

  • child_table: Name of the child table where the foreign key is being defined.
  • column1: Primary key column in the child table.
  • column2: Other columns in the child table.
  • foreign_key_column: Column in the child table that references the primary key of the parent table.
  • parent_table: Name of the parent table.
  • parent_key_column: Primary key column in the parent table.

PostgreSQL foreign key constraints with different actions 

Here are examples of foreign key constraints with different actions:

SET NULL

The SET NULL option for foreign key constraints in PostgreSQL defines the behavior when a referenced row in the parent table is deleted. If a row in the parent table is deleted, the corresponding foreign key values in the child table will be set to NULL.

This option is useful when you want to maintain referential integrity but allow orphaned rows in the child table to exist with NULL values in the foreign key column.

-- Create the parent table
CREATE TABLE parent (
    parent_id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- Create the child table with a foreign key reference to parent_id in parent table
CREATE TABLE child (
    child_id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent(parent_id) ON DELETE SET NULL,
    name VARCHAR(50)
);

-- Insert sample data into parent table
INSERT INTO parent (name) VALUES
    ('Parent 1'),
    ('Parent 2'),
    ('Parent 3');

-- Insert sample data into child table
INSERT INTO child (parent_id, name) VALUES
    (1, 'Child 1 of Parent 1'),
    (1, 'Child 2 of Parent 1'),
    (2, 'Child 1 of Parent 2'),
    (3, 'Child 1 of Parent 3');

-- Query to display the data
SELECT 
    p.parent_id, p.name AS parent_name, 
    c.child_id, c.name AS child_name
FROM 
    parent p
LEFT JOIN 
    child c ON p.parent_id = c.parent_id
ORDER BY 
    p.parent_id, c.child_id;

Output:

 parent_id | parent_name | child_id |     child_name      
-----------+-------------+----------+---------------------
         1 | Parent 1    |        1 | Child 1 of Parent 1
         1 | Parent 1    |        2 | Child 2 of Parent 1
         2 | Parent 2    |        3 | Child 1 of Parent 2
         3 | Parent 3    |        4 | Child 1 of Parent 3
(4 rows)

In this example, if a row in the parent table is deleted, the parent_id column in the child table will be set to NULL for any corresponding child rows.

SET DEFAULT

The SET DEFAULT option for foreign key constraints in PostgreSQL specifies the action to take when a referenced row in the parent table is deleted. If a row in the parent table is deleted, the corresponding foreign key values in the child table will be set to the default value specified for the column.

This option is useful when you want to maintain referential integrity by replacing the deleted parent row's references with a predefined default value.

-- Create the parent table
CREATE TABLE parent (
    parent_id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- Create the child table with a foreign key reference to parent_id in parent table
CREATE TABLE child (
    child_id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent(parent_id) ON DELETE SET DEFAULT,
    name VARCHAR(50),
    default_value INT DEFAULT 0
);

-- Insert sample data into parent table
INSERT INTO parent (name) VALUES
    ('Parent 1'),
    ('Parent 2'),
    ('Parent 3');

-- Insert sample data into child table
INSERT INTO child (parent_id, name) VALUES
    (1, 'Child 1 of Parent 1'),
    (1, 'Child 2 of Parent 1'),
    (2, 'Child 1 of Parent 2'),
    (3, 'Child 1 of Parent 3');

-- Query to display the data
SELECT 
    p.parent_id, p.name AS parent_name, 
    c.child_id, c.name AS child_name,
    c.default_value
FROM 
    parent p
LEFT JOIN 
    child c ON p.parent_id = c.parent_id
ORDER BY 
    p.parent_id, c.child_id;

Output:

 parent_id | parent_name | child_id |     child_name      | default_value 
-----------+-------------+----------+---------------------+---------------
         1 | Parent 1    |        1 | Child 1 of Parent 1 |             0
         1 | Parent 1    |        2 | Child 2 of Parent 1 |             0
         2 | Parent 2    |        3 | Child 1 of Parent 2 |             0
         3 | Parent 3    |        4 | Child 1 of Parent 3 |             0
(4 rows)

In this example, if a row in the parent table is deleted, the parent_id column in the child table will be set to the default value specified in the default_value column for any corresponding child rows.

RESTRICT

The RESTRICT option for foreign key constraints in PostgreSQL specifies the action to take when a referenced row in the parent table is attempted to be deleted or updated. If a row in the parent table is referenced by one or more rows in the child table, the RESTRICT option prevents the deletion or update of the parent row.

-- Create the parent table
CREATE TABLE parent (
    parent_id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- Create the child table with a foreign key reference to parent_id in parent table
CREATE TABLE child (
    child_id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent(parent_id) ON DELETE RESTRICT,
    name VARCHAR(50)
);

-- Insert sample data into parent table
INSERT INTO parent (name) VALUES
    ('Parent 1'),
    ('Parent 2'),
    ('Parent 3');

-- Insert sample data into child table
INSERT INTO child (parent_id, name) VALUES
    (1, 'Child 1 of Parent 1'),
    (1, 'Child 2 of Parent 1'),
    (2, 'Child 1 of Parent 2'),
    (3, 'Child 1 of Parent 3');

-- Query to display the data
SELECT 
    p.parent_id, p.name AS parent_name, 
    c.child_id, c.name AS child_name
FROM 
    parent p
LEFT JOIN 
    child c ON p.parent_id = c.parent_id
ORDER BY 
    p.parent_id, c.child_id;

Output:

 parent_id | parent_name | child_id |     child_name      
-----------+-------------+----------+---------------------
         1 | Parent 1    |        1 | Child 1 of Parent 1
         1 | Parent 1    |        2 | Child 2 of Parent 1
         2 | Parent 2    |        3 | Child 1 of Parent 2
         3 | Parent 3    |        4 | Child 1 of Parent 3
(4 rows)

In this example, if you attempt to delete or update a row in the parent table that is referenced by one or more rows in the child table, PostgreSQL will raise an error, preventing the operation and maintaining referential integrity.

NO ACTION

The NO ACTION option for foreign key constraints in PostgreSQL specifies that no action should be taken if a referenced row in the parent table is deleted or updated. This means that if a row in the parent table is deleted or updated, the operation will be aborted if there are any corresponding rows in the child table referencing it.

-- Create the parent table
CREATE TABLE parent (
    parent_id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- Create the child table with a foreign key reference to parent_id in the parent table
CREATE TABLE child (
    child_id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent(parent_id) ON DELETE NO ACTION,
    name VARCHAR(50)
);

-- Insert sample data into the parent table
INSERT INTO parent (name) VALUES
    ('Parent 1'),
    ('Parent 2'),
    ('Parent 3');

-- Insert sample data into the child table
INSERT INTO child (parent_id, name) VALUES
    (1, 'Child 1 of Parent 1'),
    (1, 'Child 2 of Parent 1'),
    (2, 'Child 1 of Parent 2'),
    (3, 'Child 1 of Parent 3');

-- Query to display the data
SELECT 
    p.parent_id, p.name AS parent_name, 
    c.child_id, c.name AS child_name
FROM 
    parent p
LEFT JOIN 
    child c ON p.parent_id = c.parent_id
ORDER BY 
    p.parent_id, c.child_id;

Output:

 parent_id | parent_name | child_id |     child_name      
-----------+-------------+----------+---------------------
         1 | Parent 1    |        1 | Child 1 of Parent 1
         1 | Parent 1    |        2 | Child 2 of Parent 1
         2 | Parent 2    |        3 | Child 1 of Parent 2
         3 | Parent 3    |        4 | Child 1 of Parent 3
(4 rows)

In this example, if you attempt to delete a row in the parent table that is referenced by one or more rows in the child table, PostgreSQL will raise an error and abort the deletion operation, maintaining referential integrity. Similarly, if you attempt to update the parent row in a way that would affect the referenced rows in the child table, the update operation will also be aborted.

CASCADE

The CASCADE option for foreign key constraints in PostgreSQL specifies that if a referenced row in the parent table is deleted or updated, the corresponding rows in the child table should be automatically deleted or updated, respectively.

-- Create the parent table
CREATE TABLE parent (
    parent_id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- Create the child table with a foreign key reference to parent_id in the parent table
CREATE TABLE child (
    child_id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent(parent_id) ON DELETE CASCADE,
    name VARCHAR(50)
);

-- Insert sample data into the parent table
INSERT INTO parent (name) VALUES
    ('Parent 1'),
    ('Parent 2'),
    ('Parent 3');

-- Insert sample data into the child table
INSERT INTO child (parent_id, name) VALUES
    (1, 'Child 1 of Parent 1'),
    (1, 'Child 2 of Parent 1'),
    (2, 'Child 1 of Parent 2'),
    (3, 'Child 1 of Parent 3');

-- Query to display the data
SELECT * FROM parent;
SELECT * FROM child;

Output:

parent_id |   name   
-----------+----------
         1 | Parent 1
         2 | Parent 2
         3 | Parent 3
(3 rows)

 child_id | parent_id |        name         
----------+-----------+---------------------
        1 |         1 | Child 1 of Parent 1
        2 |         1 | Child 2 of Parent 1
        3 |         2 | Child 1 of Parent 2
        4 |         3 | Child 1 of Parent 3
(4 rows)

This output shows the data stored in the parent and child tables. Each row represents a record in the respective table. The parent_id column in the child table corresponds to the parent_id in the parent table, establishing the parent-child relationship.

Importance of Foreign Keys

  1. Data Integrity: Foreign keys ensure that the data in the child table remains consistent with the data in the parent table. They prevent actions that would leave orphaned records or violate referential integrity.
  2. Enforcing Relationships: Foreign keys enforce relationships between tables, reflecting the logical connections between entities in the database schema.
  3. Automatic Indexing: PostgreSQL automatically creates an index on the foreign key column, which improves query performance when joining tables.
  4. Documentation: Foreign keys serve as documentation for the relationships between tables, making the database schema more understandable and maintainable.

Best Practices for Foreign Keys

  1. Consistent Naming Conventions: Use consistent naming conventions for foreign keys to make the database schema more readable and understandable.
  2. Avoid Circular References: Avoid creating circular references between tables, as they can lead to complications and difficulties in data manipulation.
  3. Cascading Actions: Define cascading actions such as ON DELETE CASCADE or ON UPDATE CASCADE to automatically propagate changes to related records, maintaining data integrity.
  4. Use Constraints: In addition to foreign keys, consider using other constraints such as NOT NULL and UNIQUE to further enforce data integrity rules.

Common Challenges and Solutions

  1. Performance Impact: While foreign keys improve data integrity, they can also have a performance impact, especially during data modification operations. Proper indexing and query optimization techniques can mitigate this impact.
  2. Bulk Data Operations: Bulk data operations such as data loading or deletion can be slower with foreign keys enabled. Consider temporarily disabling foreign key constraints during such operations and re-enabling them afterward.
  3. Handling NULL Values: Decide whether NULL values are allowed in foreign key columns and how they should be treated in the context of the relationship between tables.

Conclusion

Foreign keys are a fundamental feature of PostgreSQL databases, enabling the establishment and maintenance of relationships between tables. By enforcing referential integrity and ensuring data consistency, foreign keys play a vital role in database design and data management. Understanding their syntax, functionality, and best practices is essential for building robust and reliable database schemas.