EDUCBA

Home Data Science Data Science Tutorials PostgreSQL Tutorial Foreign Key in PostgreSQL

Foreign Key in PostgreSQL

By Priya PedamkarPriya Pedamkar

Foreign Key in PostgreSQL

Introduction to PostgreSQL Foreign Key

The foreign key in PostgreSQL states that values in the first table column must appear with values with the second table column; the foreign key is most important in PostgreSQL. A foreign key is a group or field of tables used to identify the rows from another table uniquely. In general words, the foreign key in PostgreSQL is defined as the first table that has a reference to the second table’s primary key. The table name states that the child table contains the foreign key, and another table with foreign key reference is called the parent table.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

CREATE TABLE table_name (
Column_name1 data_type primary key,
Cloumn_name2 data_type
Column_nameN data_type
);
CREATE TABLE table_name (
Column_name1 data_type references table_name (Primary key table name reference) (Column_name),
Column_name1 data_type
Column_name2 data_type
Column_nameN data_type
);

Below is the description of the above syntax as follows:

  • Create a table statement to create a new table and implement the column’s primary key and foreign key.
  • Table name: Name of the table on which table column we have implementing foreign key in PostgreSQL.
  • Column_name1 to column_nameN: Column name of the table on which we are implementing foreign key and primary key in PostgreSQL.
  • Primary key: We are creating a primary key on the first table column and give reference to that primary key on the second table column to implement a foreign key in PostgreSQL.
  • Data type: We need to define the data type of column. We define the data type of a specific column and what kind of data we have inserted into a table. If the column row contains a numeric value, we define the integer data type for the same.
  • References: References defined as we have created a primary key on the first table and give reference to that primary key on the second table to implement it.

How does Foreign Key work in PostgreSQL?

  • A foreign key is most important in PostgreSQL.
  • A foreign key is a type of constraint in PostgreSQL. Foreign key states that values in the column must match with values with some other row from another table.
  • PostgreSQL foreign key maintains the referential integrity concepts with the two related tables.
  • Foreign key constraints in PostgreSQL states that values in the first table column must appear or present with values with a second table column. If the value is not present in the first table, it will show an error message (“ERROR: insert or update on table “table_name” violates foreign key constraint “table2 column_name fkey”) at the time of insertion of a new record in the second table.
  • The table name in PostgreSQL foreign key concept states that the child table contains the foreign key, and another table with foreign key reference is called the parent table.
  • PostgreSQL foreign key concept is based on the first table combination of columns with primary key values from the second table.
  • It is also known as constraints or referential integrity constraints. It is specified that the values of the foreign key constraints column, which was correspond with the actual values of the primary key column from another table.

Examples to Implement Foreign Key in PostgreSQL

Given below are the examples:

Example #1

Create Employee1 table and create primary key constraints.

  • First, we are creating an employee1 table and creating a primary key on the emp_id table.
  • Below is the example of creating an employee1 table with primary key constraints on the emp_id column.

CREATE TABLE Employee1 (emp_id INT primary key, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);

Output:

foreign key in postgreSQL 1

  • In the above example, we have created the primary key on the emp_id column.
  • Below is the description of the Employee1 table states that we have created a foreign key on the Employee2 table and given a reference to the emp_id column from the Employee1 table.

\d+ Employee1;

Output:

employee

Example #2

Create an Employee2 table and create foreign key constraints.

  • In the below example, we have created the Employee2 table and created foreign key constraints on the emp_id column.
  • We have given a primary key reference to the emp_id column from the employee1 table. Now we have created primary key and foreign key constraints between Employee1 table and Employee2 table.

CREATE TABLE Employee2 (emp_id INT references Employee1 (emp_id), emp_first_name character(10) NOT NULL, emp_local_address character(20) NOT NULL, emp_primary_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);

Output:

oreign key in postgreSQL 3

  • In the above example, we have created a foreign key on the emp_id column.
  • Below is the description of the Employee2 table states that we have created a foreign key on the Employee2 table and given a reference to the emp_id column from the Employee1 table.

\d+ Employee2;

Output:

foreign key in postgreSQL 4

Example #3

Insert value in Employee1 and Employee2 table.

  • In the first insertion, we have to insert the same emp_id in both the table to issue an error; also insert command is executed successfully.

INSERT INTO Employee1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (1, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

Insert value in Employee1 and Employee2 table

INSERT INTO Employee2 (emp_id, emp_first_name, emp_local_address, emp_primary_phone, emp_salary, date_of_joining) VALUES (1, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

foreign key in postgreSQL 7

  • At the time of insertion second record in the Employee table, it will show an error that “ERROR: insert or update on table “employee2” violates foreign key constraint “employee2_emp_id_fkey”.
  • Please find below the example for the same.

INSERT INTO Employee2 (emp_id, emp_first_name, emp_local_address, emp_primary_phone, emp_salary, date_of_joining) VALUES (2, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

foreign key in postgreSQL 8

Example #4

Create foreign key constraints after creating a table using alter command.

  • First, create a department table.

CREATE TABLE department (emp_id INT NOT NULL, dept_name character(10) NOT NULL, dept_id int NOT NULL, dept_code varchar(10));

Output:

Create foreign key constraints

  • Alter the department table to add a foreign key.

ALTER TABLE department ADD CONSTRAINT fk_key FOREIGN KEY (emp_id) REFERENCES Employee1 (emp_id);

Output:

Alter the department table

Conclusion

Foreign key constraints state that the first table column values must appear with values with a second table column. If the value is not present in the first table, it will show an error message at the time of insertion new record in the second table. A foreign key is essential in PostgreSQL.

Recommended Articles

This has been a guide to Foreign Key in PostgreSQL. Here we discuss the introduction, how foreign key works in PostgreSQL? and examples. You may also have a look at the following articles to learn more –

  1. LEFT OUTER JOIN in PostgreSQL
  2. PostgreSQL Versions
  3. SQL WHERE Clause
  4. What is PostgreSQL?