PostgreSQL – Create Table with Multiple Foreign Keys

Create Table with Multiple Foreign Keys in PostgreSQL Database

In PostgreSQL, a table can have multiple foreign keys, each referencing a primary key in another table. This allows the table to maintain relationships with multiple parent tables, ensuring data integrity across the database.

In this tutorial, we’ll explore how to create a table with multiple foreign keys, demonstrate different use cases, and include self-sufficient examples.

---

Example 1: Basic Table with Multiple Foreign Keys

In this example, we will create three tables:

• departments: A parent table that stores department information.
• projects: A parent table that stores project information.
• employees: A child table that references both departments and projects via foreign keys.

First, create the departments table:

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

Next, create the projects table:

CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL
);

Finally, create the employees table with two foreign keys:

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

Insert some sample data into the parent tables:

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

INSERT INTO projects (project_name)
VALUES ('Project A'), ('Project B'), ('Project C');

Insert data into the employees table, referencing both parent tables:

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

Retrieve data to verify the relationships:

SELECT e.employee_name, d.department_name, p.project_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN projects p ON e.project_id = p.project_id;

Explanation: The employees table references the departments and projects tables via foreign keys. If a department is deleted, the ON DELETE RESTRICT constraint prevents deletion. If a project is deleted, the ON DELETE SET NULL constraint sets the project_id to NULL in the employees table.

---

Example 2: Creating a Table with Modified Constraints

If you want to create the employees table with different delete behaviors, you need to recreate the table. This example assumes the same departments and projects tables, but with ON DELETE CASCADE constraints for both foreign keys.

First, drop the existing employees table:

DROP TABLE IF EXISTS employees;

Create the employees table with cascading behavior:

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

Insert sample data again into the employees table:

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

Delete a department and observe the cascading behavior:

DELETE FROM departments WHERE department_id = 1;

Result: The corresponding rows in the employees table where department_id = 1 will also be deleted.

Verify the updated data:

SELECT * FROM employees;

Explanation: With ON DELETE CASCADE, deleting a parent record in the departments or projects table automatically removes all associated rows in the employees table, ensuring no orphaned rows remain.

---

Conclusion

Using multiple foreign keys in a table allows you to define complex relationships between tables in PostgreSQL. By specifying constraints like ON DELETE RESTRICT, ON DELETE SET NULL, or ON DELETE CASCADE, you can control how deletions in parent tables affect child tables. Practice these examples to understand the flexibility and behavior of foreign key constraints in PostgreSQL.