In relational databases like PostgreSQL, Foreign keys are a widely used concept that allows us to link the data of one table to another. A table can have zero, one, or multiple foreign keys; it depends on the table’s relation with other tables. The table holding a foreign key is known as the child/referencing table, while the table which is referenced through the foreign key is named as parent/referenced table.

This write-up will cover all the basics of Postgres Foreign key constraints using practical examples. So, let’s begin.

FOREIGN KEY Constraint in Postgres?

The FOREIGN KEY refers to a column/field in a table that points to the PRIMARY KEY in some other Postgres table. Postgres allows foreign keys to be defined using foreign key constraints. Moreover, the data referential integrity is maintained between the child and parent tables with the help of the foreign key constraint.

Syntax

The below snippet depicts the syntax of the foreign key constraint:

[CONSTRAINT name]  FOREIGN KEY(col_list)
REFERENCES parent_tab(col_list)
[ON DELETE action]
[ON UPDATE action]

Let’s comprehend the above-given syntax step-by-step:

• Firstly, specify the foreign key name using the CONSTRAINT keyword/clause. The CONSTRAINT is optional; if you skip it, Postgres will specify an auto-generated name.
• Next, use the FOREIGN KEY keyword followed by a set of parentheses and specify the foreign key column or group of columns within the parenthesis.
• In the REFERENCES clause, identify the parent/referenced table along with the foreign key columns.
• Finally, you can use a couple of optional clauses, such as ON DELETE and ON UPDATE, to determine the referential actions.

Create a Foreign Key Constraint in Postgres

The following example shows how a foreign key constraint can be created at the time of table creation:

CREATE TABLE emp_info( 
emp_id INT PRIMARY KEY NOT NULL, 
emp_name TEXT NOT NULL, 
emp_email VARCHAR(100) NOT NULL, 
emp_salary INT NOT NULL);

emp_info table with four columns has been created successfully.

Let’s create another table named dept_info that contains a foreign key emp_id:

CREATE TABLE dept_info(
dept_id INT PRIMARY KEY NOT NULL, 
dept_name TEXT NOT NULL, 
emp_id INT,
CONSTRAINT fk_emp_dept 
FOREIGN KEY(emp_id)
REFERENCES emp_info(emp_id));

The dept_info table has been created successfully. Let’s run the below command to get more clarity:

\d+ emp_info;

The output shows that the emp_id column is referenced by the dept_info table.

Let’s execute the below command to understand the table’s relation more clearly:

\d+ dept_info;

The output clarifies that the emp_id is a foreign key in the dept_info table.

That was all the basics regarding Postgres FOREIGN KEY CONSTRAINT.

Conclusion

In PostgreSQL, Foreign keys are a widely used concept that allows us to link the data of one table to others. A table can have zero, one, or multiple foreign keys; it depends on the table’s relation with other tables. The table referencing the foreign key is named the child/referenced table, while the table referenced by the foreign key is known as the parent/referenced table. This post explained the FOREIGN KEY CONSTRAINT with a practical example.