PostgreSQL – Foreign Key Constraints

PostgreSQL – Foreign Key

A foreign key in PostgreSQL is a constraint that links one table to another. It establishes a relationship between the columns of two tables, ensuring referential integrity. When a foreign key is applied, PostgreSQL ensures that the values in the foreign key column(s) match the values in the referenced table’s column(s) or are NULL.

In this tutorial, we will explain how to use foreign key constraints in PostgreSQL with examples.

---

Setup for Examples

To demonstrate the usage of foreign key constraints, we will create two tables: departments and employees. The employees table will reference the departments table through a foreign key.

Step 1: Create the Database

Create a database named company.

CREATE DATABASE company;

Connect to the database:

\c company;

Step 2: Create the Parent Table

Create the departments table, which will serve as the parent table in our foreign key relationship:

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

Step 3: Create the Child Table

Create the employees table, which will reference the departments 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
);

Step 4: Insert Data into the Parent Table

Insert sample data into the departments table:

INSERT INTO departments (department_name)
VALUES 
('Human Resources'),
('Finance'),
('IT'),
('Marketing');

Step 5: Insert Data into the Child Table

Insert sample data into the employees table, referencing valid department IDs:

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

---

Examples of Foreign Key Constraints

With the employees table and departments table setup in company database, we shall go through some examples on how to use Foreign Key constraints.

---

1. Preventing Invalid Data Using Foreign Key Constraints

The foreign key ensures that invalid data cannot be inserted into the child table. For example, trying to insert an employee with a non-existent department ID will result in an error:

INSERT INTO employees (employee_name, department_id)
VALUES ('Invalid Employee', 999);

Explanation: Department ID 999 does not exist in the departments table, so PostgreSQL prevents the insertion to maintain referential integrity.

---

2. Deleting Rows with ON DELETE CASCADE

When a row in the parent table is deleted, the corresponding rows in the child table are automatically deleted. For example, deleting the department with ID 1:

DELETE FROM departments
WHERE department_id = 1;

Explanation: This query will remove the Human Resources department from the departments table and automatically delete employees associated with this department in the employees table.

Result: The employees Alice and David (department ID 1) are also deleted from the employees table.

---

3. Updating Keys with ON UPDATE CASCADE

When a primary key in the parent table is updated, the foreign key in the child table is automatically updated. For example, update the department ID for Finance:

UPDATE departments
SET department_id = 10
WHERE department_id = 2;

Explanation: The department ID for Finance is updated to 10 in the departments table, and all corresponding rows in the employees table referencing department_id = 2 are updated to department_id = 10.

---

Conclusion

Foreign key constraints are essential for maintaining data integrity between related tables in PostgreSQL. By using options like ON DELETE CASCADE and ON UPDATE CASCADE, you can manage complex relationships effectively.