PostgreSQL Foreign Key Constraint

In PostgreSQL, the foreign key is a column(s) in a table that points to a primary key or unique key column in the same or another table.

Foreign key establishes referential integrity between the parent and child tables. The table that has a foreign key is called the Child table and the table that has a primary key or unique key column that is being referenced by the foreign key is called the Parent table.

For example, the following employee table has a foreign key column dept_id that links to a primary key column dept_id in the department table. Thus, it forms one-to-many relationships between the employee and department table, which means for one department there could be multiple employees. In other words, multiple records in the employee table can contain the same dept_id that points to one dept_id value in the department table.

Define Foreign Key while Creating a Table

You can define a foreign key when you create a table using CREATE TABLE statement.

CONSTRAINT [constraint_name] FOREIGN KEY(fk_columns) 
REFERENCES <parent_table>(parent_table_columns)
[ON DELETE <delete_action>]
[ON UPDATE &lt;update_action]

In the above syntax,

• Use the CONSTRAINT keyword to define a constraint and then the name of the foreign key constraint. The constraint name is optional; if you do not specify it, PostgreSQL will give the name as per the default naming convention.
• Specify one or more column names of the table on which you want to define foreign key constraint after the FOREIGN KEY keyword.
• The REFERENCES keyword is used to specify the parent table and parent table columns which are referenced by a foreign key in the current table.
• The ON DELETE and ON UPDATE clauses are optional. These actions determine the behavior when a primary key is deleted or updated in the parent table.

PostgreSQL supports the following referential actions:

1. NO ACTION
2. RESTRICT
3. SET NULL
4. SET DEFAULT
5. CASCADE

The following example demonstrates creating a foreign key in the employee table that points to the department table.

CREATE TABLE department(
    dept_id	INT PRIMARY KEY,
    dept_name	VARCHAR(50)
);

CREATE TABLE employee(
    emp_id 	INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email	VARCHAR (100),
    hiredate	DATE,
    manager_id	INT,
    salary		INT,
    dept_id		INT,

    CONSTRAINT FK_employee_department FOREIGN KEY(dept_id)
        REFERENCES department(dept_id)
);

In the above example, the dept_id column in the employee table is defined as a foreign key column that references the primary key column dept_id of the department table. The CONSTRAINT FK_employee_department specifies the foreign key name FK_employee_department, FOREIGN KEY(dept_id) specifies the foreign key column in the employee table, and REFERENCES department(dept_id) specifies that the foreign key column refers to the dept_id column of the department table.

The above foreign key established a one-to-many relation between department and employee table where a department can have zero or more employees, and one employee cannot have more than one department.

Notice that we did not define any action such as ON DELETE or ON UPDATE clause. So, it will consider the default NO ACTION.

Note: The foreign key column name does not need to be the same as a primary key column, but it's advisable to do so for readability purposes.

NO ACTION – Raise an Error on Delete or Update

The NO ACTION referential action is the default action if ON DELETE or ON UPDATE clause is not specified. The NO ACTION produces an error indicating that the deletion or update would create a foreign key constraint violation.

The following example demonstrates the NO ACTION referential action.

INSERT INTO department 
VALUES(1,'HR'),
	(2,'IT'),
	(3,'FINANCE');

INSERT INTO employee(emp_id, first_name, last_name, dept_id) 
VALUES(1,'Annie','Smith',1),
	(2,'Susan','Klassen',1),
	(3,'May','Kaasman',2);

Now let's delete a department with dept_id = 1, as shown below.

We are trying to delete a row in the department table where dept_id = 1, but two employees in the employee table belong to that department. So, PostgreSQL raises a foreign key constraint violation error and will not allow deletion of the department.

To delete a row in the department table, you need to delete all employees who belong to that department and then delete a department.

RESTRICT

The RESTRICT action is the same as NO ACTION. The difference is when you define the foreign key constraint as DEFERRABLE with an INITIALLY DEFERRED or INITIALLY IMMEDIATE mode.

SET NULL - Set Referencing Column to NULL

When a foreign key is created with ON DELETE SET NULL or ON UPDATE SET NULL, then on delete or update of data in the parent table, the foreign key column of referencing row in the child table will be automatically set to NULL.

The following example demonstrates the SET NULL action.

CREATE TABLE department(
    dept_id	INT PRIMARY KEY,
    dept_name	VARCHAR(50)
);

CREATE TABLE employee(
    emp_id 	INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email_id	VARCHAR (100),
    hire_date	DATE,
    manager_id	INT,
    salary		INT,
    dept_id		INT,
    
    CONSTRAINT FK_Employee_Department FOREIGN KEY(dept_id)
        REFERENCES department(dept_id)
        ON DELETE SET NULL
);

Now, let's insert data into the above tables.

INSERT INTO department 
VALUES(1,'HR'),
	(2,'IT'),
	(3,'Finance');

INSERT INTO employee(emp_id, first_name, last_name, dept_id) 
VALUES(1,'Annie','Smith',1),
	(2,'Susan','Klassen',1),
	(3,'May','Kaasman',2);

Now try to delete department where dept_id = 1, as shown below.

We defined foreign key constraint with ON DELETE SET NULL clause, so two referencing rows in the employee table whose dept_id was 1 are now set to NULL. Let's check the data in the employee table.

SET DEFAULT

When a foreign key is created with ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT, then on deleting or updating data in the parent table, the foreign key column of referencing row in the child table will be automatically set to the default value if specified any. There must be a row in the referenced table matching the default values if they are not null, or the operation will fail.

The following example demonstrates the SET DEFAULT action:

CREATE TABLE department(
    dept_id	INT PRIMARY KEY,
    dept_name	VARCHAR(50)
);

CREATE TABLE employee(
    emp_id 	INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email_id	VARCHAR (100),
    hire_date	DATE,
    manager_id	INT,
    salary		INT,
    dept_id		INT DEFAULT 3,
    CONSTRAINT FK_Employee_Department FOREIGN KEY(dept_id)
        REFERENCES department(dept_id)
        ON DELETE SET DEFAULT
);

Insert data into the above tables:

INSERT INTO department 
VALUES(1,'HR'),
	(2,'IT'),
	(3, 'Finance');

INSERT INTO employee(emp_id, first_name, last_name, dept_id) 
VALUES(1,'Annie','Smith',1),
	(2,'Susan','Klassen',2),
	(3,'May','Kaasman',2);

Now try to delete department with dept_id = 1, as shown below.

DELETE FROM department WHERE dept_id = 1;

As you can see, it allowed deletion of the department. We defined foreign key constraint with ON DELETE SET DEFAULT, so referencing row with emp_id = 1 in employee table whose dept_id was 1 is now set to DEFAULT value which is 3. Let's check the data in the employee table.

Note that there is default value 3 specified in the employee table for the dept_id column. If no default value is specified for dept_id in the employee table, then the above deletion will set the value as NULL.

The dept_id 3 must exist in the department table; otherwise, an error will be raised. For example, if you specify 4 as the default value of dept_id in the employee table then trying to delete a row in the department table would raise an error, as shown below.

CASCADE

When a foreign key is created with ON DELETE CASCADE or ON UPDATE CASCADE, then on delete or update of a referenced row in the parent table, the foreign key row of referencing row in the child table will be automatically deleted.

The following example demonstrates the CASCADE action:

CREATE TABLE department(
    dept_id	INT PRIMARY KEY,
    dept_name	VARCHAR(50)
);

CREATE TABLE employee(
    emp_id 	INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email_id	VARCHAR (100),
    hire_date	DATE,
    manager_id	INT,
    salary		INT,
    dept_id		INT,

    CONSTRAINT FK_Employee_Department FOREIGN KEY(dept_id)
        REFERENCES department(dept_id)
        ON DELETE CASCADE
);

Now, insert data into tables.

INSERT INTO department 
VALUES(1,'HR'),
	(2,'IT'),
	(3, 'Finance');

INSERT INTO employee(emp_id, first_name, last_name, dept_id) 
VALUES(1,'Annie','Smith',1),
	(2,'Susan','Klassen',2),
	(3,'May','Kaasman',2);

Now try to delete a department where dept_id = 1.

DELETE FROM department WHERE dept_id = 1;

The above DELETE statement was executed successfully and allowed deletion of a row in the department table. Because of ON DELETE CASCADE option, all the referencing rows in the employee table will be deleted. Let's check the data in the employee table.

As you can see, there is an employee with emp_id = 1 belonging to the 'HR' department. On deletion of 'HR' department with emp_id = 1 is deleted from the employee table.

Adding Foreign Key to an Existing Table

A foreign key constraint can be added to one or more columns of the existing table. If the table you are adding foreign key constraint contains data, then that column or set of columns must have matching values with referencing column of the Parent table, otherwise, it will not allow adding a constraint.

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> FOREIGN KEY(<fk_columns>)
    REFERENCES <parent_table>(<parent_table_columns>)
    [ON DELETE <action>]
    [ON UPDATE <action>];

Assume we have department and employee table as bellow without any parent-child relationship defined between them.

Note that one employee does not belong to any department that has dept_id = NULL. Now we will add a foreign key constraint on the dept_id column of the employee table.

ALTER TABLE employee
    ADD CONSTRAINT FK_Employee_Department FOREIGN KEY(dept_id)
    REFERENCES department(dept_id)
    ON DELETE CASCADE;

The above will create a foreign key in the existing employee table.