PostgreSQL - Foreign Key

Foreign keys play a crucial role in relational databases by establishing relationships between tables and safeguarding data integrity. In this PostgreSQL foreign key tutorial, we'll cover how foreign keys work, their importance and how to create them.

We will also learn about foreign key constraints and their role in ensuring data consistency. By the end, we will have a solid understanding of what a foreign key is in PostgreSQL and how to implement it effectively.

What is a Foreign Key?

A foreign keyis a column (or a group of columns) in one table that references the primary key of another table, establishing a link between the two tables. The table containing the foreign key is known as the "child table" and the table to which it refers is known as the "parent table."

A foreign key creates a link between two tables by ensuring that any data entered into the foreign key column must already exist in the parent table. This helps maintain data integrity by preventing orphan records and ensuring that relationships between data remain consistent.

Key Terms:

• Referential Integrity: It ensures that the relationship between two tables remains consistent.
  
• Parent Table: The table containing the primary key.
  
• Child Table: The table that includes the foreign key, referencing the primary key of another table.

Creating a Foreign Key in PostgreSQL

In PostgreSQL, we can define a foreign key when creating a table or after a table has been created. Foreign keys reference the primary key of another table and they ensure that the data in the child table matches one of the values in the parent table.

Syntax for Foreign Key:

CREATE TABLE child_table (
 column1 datatype,
 column2 datatype,
 ...
 CONSTRAINT fk_name FOREIGN KEY (foreign_key_column)
 REFERENCES parent_table(primary_key_column)
);

Example

Let's create two tables, departments and employees, where the employees table has a foreign key that references the departments table.

Step 1: Create Parent Table (Departments)

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

Step 2: Create Child Table (Employees)

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100) NOT NULL,
 department_id INT,
 CONSTRAINT fk_department FOREIGN KEY (department_id)
 REFERENCES departments(department_id)
);

In this example, the department_id in the employees table references the department_id in the departments table, ensuring that each employee is assigned to a valid department.

Foreign Key Constraints

In PostgreSQL, foreign keys come with several constraints that govern how changes in the parent table affect the child table. These constraints can be set when creating or altering a table.

1. ON DELETE CASCADE: It Automatically deletes any rows in the child table when the corresponding row in the parent table is deleted.
   
2. ON DELETE SET NULL: It Sets the foreign key value in the child table to NULL when the corresponding row in the parent table is deleted.
   
3. ON UPDATE CASCADE: It Updates the foreign key in the child table when the corresponding primary key in the parent table is updated.

Foreign Key Example in PostgreSQL

Let’s go through a full example, where we insert data into the departments and employees tables and demonstrate how foreign keys work.

Step 1: Insert Data into Departments Table

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

Step 2: Insert Data into Employees Table

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

Here, department_id values 1, 2, and 3 correspond to valid departments in the departments table.

Output:

On Delete Cascade and On Update Actions

1. On Delete Cascade

When a foreign key constraint is set to ON DELETE CASCADE, deleting a row from the parent table automatically deletes all the related rows in the child table.

DELETE FROM departments WHERE department_id = 1;

After executing this query, all employees belonging to the "Human Resources" department will be deleted from the employees table.

2. On Update Cascade

With ON UPDATE CASCADE, if the primary key in the parent table is updated, the foreign key in the child table is also updated.

UPDATE departments SET department_id = 4 WHERE department_id = 3;

This will update all department_id values in the employees table where the department_id was 3, changing it to 4.

Querying Data Using Foreign Keys

We can query data from multiple tables by joining them using the foreign key relationships.

Query:

To retrieve all employees and their respective department names:

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

Output:

Important Points About Foreign Key Constraints in PostgreSQL

• Foreign key constraints enforce referential integrity between tables by ensuring that a column or a group of columns in the child table matches values in the parent table.
  
• A single table can have multiple foreign keys, each establishing a relationship with different parent tables.
  
• PostgreSQL supports several actions that can be taken when the referenced row in the parent table is deleted or updated such as CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION.
  
• You can specify a name for the foreign key constraint using the CONSTRAINT keyword. If omitted, PostgreSQL assigns an auto-generated name.
  
• If ON DELETE or ON UPDATE actions are not specified, the default behavior is NO ACTION.

Conclusion

Overall, foreign keys in PostgreSQL enforce referential integrity between tables, preventing data inconsistencies. Using foreign key constraints, you can define how data in one table relates to another and control the actions when referenced data is modified or deleted. This tutorial has provided insights into how to create foreign keys in PostgreSQL and manage them, ensuring your database remains reliable and well-structured.