EDUCBA

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

Foreign Key in PostgreSQL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 6, 2023

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 in the second table column; the foreign key is most important in PostgreSQL. A foreign key is a group or field of tables used to uniquely identify the rows from another table. In general words, the foreign key in PostgreSQL is defined as the first table referencing the second table’s primary key. The table name states that the child table contains the foreign key, and another table with a foreign key reference is called the parent table.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

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 and foreign keys.
  • Table name: Name of the table on which column we have implementing the foreign key in PostgreSQL.
  • Column_name1 to column_nameN: Column name of the table on which we are implementing the foreign key and primary key in PostgreSQL.
  • Primary key: We are creating a primary key on the first table column and giving 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 the 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 are defined as creating a primary key on the first table and referencing 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. The foreign key states that the value in the column must match those in another row from another table.
  • PostgreSQL foreign key maintains the referential integrity concepts with the two related tables.
  • Foreign key constraints in PostgreSQL state that values in the first column must appear or present with values in the second 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.
  • The 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 correspond with the actual values of the primary key column from another table.

Examples

Given below are the examples:

Example #1

Create Employee1 table and create primary key constraints.

  • First, we create an employee1 table and 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 example below, we created the Employee2 table and foreign key constraints on the emp_id column.
  • We gave a primary key reference to the emp_id column from the employee1 table. We have created primary and foreign key constraints between the Employee1 and Employee2 tables.
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 tables 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

We hope that this EDUCBA information on “Foreign Key in PostgreSQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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