PostgreSQL – Create Table with Foreign Key

PostgreSQL – Create Table with Foreign Key

In relational database systems like PostgreSQL, foreign keys are an essential feature for maintaining data integrity between related tables. A foreign key creates a relationship between two tables, ensuring that values in one table (the child table) correspond to values in another table (the parent table). It helps enforce referential integrity, meaning invalid data cannot be inserted into the child table.

This tutorial explains how to create tables with foreign keys in PostgreSQL, demonstrates various use cases, and includes detailed examples for better understanding.

---

What is a Foreign Key?

A foreign key in PostgreSQL is a column (or a group of columns) in a child table that references a column in a parent table. It ensures that the values in the foreign key column(s) match the values in the referenced column(s) of the parent table or are NULL. A foreign key relationship is useful for defining logical links between tables and enforcing data consistency.

Key Features of Foreign Keys:

• Ensures referential integrity between tables.
• Prevents insertion of invalid data in the child table.
• Allows cascading updates or deletions in the child table when changes occur in the parent table.

---

Basic Syntax

The general syntax for creating a table with a foreign key is as follows:

CREATE TABLE child_table (
    column1 datatype,
    column2 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES parent_table(referenced_column)
        ON DELETE action
        ON UPDATE action
);

Explanation:

• The FOREIGN KEY constraint establishes a relationship between the child table and the parent table.
• REFERENCES specifies the parent table and the column in the parent table being referenced.
• ON DELETE and ON UPDATE define actions that occur when rows in the parent table are deleted or updated. Common options include CASCADE, SET NULL, and RESTRICT.

---

Example 1: Basic Foreign Key Relationship

Let’s create a basic example to demonstrate foreign key relationships. We’ll create two tables:

• departments: A parent table that stores department information.
• employees: A child table that stores employee information and references the departments table.

Create the parent table:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Create the child table with a foreign key referencing the parent table:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Insert data into the parent table:

INSERT INTO departments (department_name)
VALUES ('HR'), ('Finance'), ('IT');

Insert data into the child table:

INSERT INTO employees (employee_name, department_id)
VALUES ('Alice', 1), ('Bob', 2), ('Charlie', 3);

Retrieve the data to verify the relationship:

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Explanation: The employee_id column in the employees table is a primary key, while the department_id column is a foreign key referencing the department_id column in the departments table. The ON DELETE CASCADE clause ensures that when a department is deleted, all employees in that department are also deleted.

---

Example 2: Preventing Deletion of Referenced Rows Without CASCADE when using Foreign Key

In this example, we will demonstrate how foreign key constraints prevent the deletion of referenced rows when ON DELETE CASCADE is not used. This ensures referential integrity by restricting operations that would leave orphaned rows in the child table.

You may delete the tables that are created in the previous example.

Let’s create two tables:

• departments: The parent table that stores department information.
• employees: The child table that references the departments table via a foreign key, without using ON DELETE CASCADE.

Create the parent table:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Create the child table with a foreign key that references the parent table:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

Insert some data into the departments table:

INSERT INTO departments (department_name)
VALUES 
('HR'),
('Finance'),
('IT');

Insert some data into the employees table:

INSERT INTO employees (employee_name, department_id)
VALUES 
('Alice', 1),
('Bob', 2),
('Charlie', 3);

Now, attempt to delete a department that is referenced in the employees table:

DELETE FROM departments
WHERE department_id = 1;

Result: The deletion will fail, and PostgreSQL will throw the following error:

---

Example 3: Foreign Key Column Using SET NULL

In some scenarios, you may want to set the foreign key column to NULL instead of deleting rows in the child table when the parent row is deleted. Here’s how:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Explanation: The ON DELETE SET NULL action sets the department_id in the employees table to NULL when a referenced row in the departments table is deleted.

Delete a department and verify the behavior:

DELETE FROM departments WHERE department_id = 1;

SELECT * FROM employees;

The department_id of employees previously associated with the deleted department will now be set to NULL.

---

Conclusion

Foreign keys in PostgreSQL are crucial for maintaining data consistency across related tables. They provide flexibility with options like ON DELETE CASCADE, SET NULL, and RESTRICT to handle various scenarios. By enforcing referential integrity, foreign keys ensure that relationships between tables remain intact, making them an essential tool for relational database design.