View source for PostgreSQL-Database/C2/Foreign-Key-Constraint/English




{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''Narration'''

|-
|| '''Slide 1'''

'''Title Slide'''
|| Welcome to this Spoken Tutorial on '''Foreign Key Constraint''' in '''PostgreSQL'''.

|-
|| '''Slide 2''' 
'''Learning Objectives'''
|| In this tutorial, we will learn about:
* Foreign key constraint

|-
|| '''Slide 3'''

'''System Requirements'''
* '''Ubuntu 24.04 LTS'''
* '''PostgreSQL version 18.1''', and
* '''PgAdmin4 version 9.11'''.
|| To record this tutorial, I am using the following setup.

|-
|| '''Slide 4'''

'''Pre-requisites'''

|| To follow this tutorial,

* Learners should have '''PostgreSQL''' and '''pgAdmin4'''

* For the prerequisite '''PostgreSQL''' tutorials, please visit this website.

|-
|| '''Slide 5'''

'''Code Files'''

The following code file is required to practice this tutorial:
* '''Fk-commands.txt'''
This file is provided in the Code Files link on this tutorial page.

Please download and extract the file.

|| The following '''code file''' is required to practice this tutorial.

This file is provided in the '''Code Files''' link of this tutorial page.

Please download and extract the file.

|-
|| '''Open pgAdmin 4'''

|| I have opened '''pgAdmin4''' and connected to the '''PostgreSQL''' server.

|-
|| 
|| Now let us create a database named '''Company'''.

|-
|| Right click on '''Databases''' node.

From the submenu select Create and then select Database.
Create database window opens.
Type database name: Company
Click Save at the bottom right corner.
|| Right-click on the '''Databases node'''.
From the submenu select '''Create''' and then select '''Database'''.
In the '''Create Database''' window, let us type the '''Database''' name as '''Company'''.

Click the '''Save''' button at the bottom right corner.

|-
|| Under the Database expand '''Schemas''' node, '''public''' node and '''Tables''' node.
|| Now let us expand the '''Schemas''' node, '''public''' node and then '''Tables''' node.

|-
|| Right click on '''Tables''' node and select Query Tool.
Query window opens.

|| Right click on '''Tables''' node and select the '''Query Tool''' option.<br><br>
The '''Query''' window opens.

|-
|| Type: 

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

Click on the '''execute query''' icon'''.'''

|| Let me create a table named '''department'''.
Type this query in the query window.
Here '''department id''' is the '''primary key'''.
It uniquely identifies each department.

Click on the '''execute query icon''' to execute the query.

|-
|| Type:

'''CREATE TABLE employee ('''
'''emp_id INT PRIMARY KEY,'''
'''emp_name VARCHAR(50),'''
'''salary INT,'''
'''dept_id INT'''
''');'''

Click the '''Execute query icon.'''

|| Now let us create a table named '''employee'''.
Type the following query.
In this query '''employee id''' is the '''primary key.'''
In this table '''department id''' is just a column.
There is no restriction on its values.
Click the '''Execute query icon''' to execute the query.

|-
|| Point to the database and tables.
|| We have successfully created the database and tables.

|-
|| Type:
'''INSERT INTO department VALUES'''
'''(1, 'HR'),'''
'''(2, 'IT'),'''
'''(3, 'Sales');'''

Click on the '''execute query button'''.

|| Now let us insert some data into the '''department''' table.
Now type the following query.

Let me execute the query to see the output.

|-
|| Type:

'''SELECT * FROM department;'''

Click on the '''execute query button**.

|| We will now type this query.
Let us execute the query to see the created table.
Here is the '''department''' table with 3 records.

|-
|| Type:

'''INSERT INTO employee VALUES'''
'''(101, 'Ajay', 50000, 1),'''
'''(102, 'Neha', 60000, 2),'''
'''(103, 'Raj', 55000, 3),'''
'''(104, 'Simran', 65000, 10),'''
'''(105, 'Karan', 48000, 5);'''

Click on the '''execute query button'''.

|| Now let us insert some data into the '''employee''' table.
Type this query to insert the records in the '''employee''' table.
Let us execute the query and see the output.

|-
|| Type:

'''SELECT * FROM employee;'''
Click the '''Execute query icon.

|| Now type this query.

Let us execute the query and see the output.
Here is the employee table.

|-
|| Highlight the '''dept id records 10 and 5 in the employee table.'''

|| Observe that, dept id 10 and dept id 5 are not available in the department table.
However the database still allows the values.
This creates inconsistent data.

|-
|| 
|| To prevent such issues, we use a foreign key constraint.

|-
|| '''Type:'''

'''DELETE FROM employee WHERE dept_id NOT IN (1,2,3);'''

Click the '''Execute query icon.'''

|| Let us first delete the inconsistent data from the '''employee''' table.
Type this query.
In this query the '''Delete''' keyword deletes records.
Let us execute the query.
The output shows the query returned successfully.

|-
|| Type:

'''SELECT * FROM employee;'''

Click the '''Execute query icon.

|| Let us type this query to see the updated table.
Let us execute the query.
The records with dept id 10 and dept id  5 are deleted from the '''employee''' table.

|-
|| 
|| Now, let us learn about Foreign key.

|-
|| '''Slide 6

What is a Foreign key?'''

|| * A foreign key is a column that references a primary key in another table.
* It creates a relationship between two tables in a database.
* It ensures that the referenced value exists in the parent table.

|-
|| '''Slide 7

Syntax of Foreign key
FOREIGN KEY (column_name) REFERENCES parent_table(column_name);'''

|| This is the syntax of the Foreign key constraint.

|-
|| 
|| Let’s add a Foreign key.

|-
|| Type:

'''ALTER TABLE employee'''
'''ADD CONSTRAINT fk_dept'''
'''FOREIGN KEY (dept_id) REFERENCES department(dept_id);'''

Click the '''Execute query icon.'''

|| Type the following query.

This query adds a '''foreign key''' on dept id.
Each value must exist in the department table.
We will now execute the query.
Output shows '''Query''' returned successfully.

|-
|| '''Type this command and execute:'''

'''INSERT INTO employee'''
'''VALUES (105, 'Karan', 48000, 7);'''

Click the '''Execute query icon.'''

'''Point to the error.'''

|| Now, let us insert an invalid department id.
Type the following query
Let us execute the query.
The result shows an error.

|-
|| '''Type:

INSERT INTO employee'''
'''VALUES'''
'''(104, 'Simran', 65000, 3),'''
'''(105, 'Karan', 48000, 2);'''
|| Type the following query.

It inserts a valid record without issues.

Now execute the query.

A foreign key links two tables.
It ensures data integrity by preventing invalid entries.

|-
|| Type:

**SELECT * FROM employee;'''

Click the '''Execute query icon.**

|| Let us type this query to see the updated table.
Let us execute the query.
The records are now updated in the employee table.

|-
|| '''Slide 8'''

'''Summary'''

In this tutorial, we have learnt about:

*Foreign key constraint


|| With this we come to the end of this tutorial.
Let us summarise

|-
|| '''Slide 9'''

'''Assignment'''

'''As an assignment,'''

'''Create a table named project with columns:
project_id, project_name, and dept_id.'''
'''Add a foreign key on dept_id referencing the department table.'''
'''Insert a valid record and execute the query.'''
'''Insert an invalid record and observe the error.'''

|| We encourage you to try this assignment.

|-
|| '''Slide 10'''

'''Thank you'''

This Spoken Tutorial is brought to you by EduPyramids Educational Services Private Limited SINE IIT Bombay.

'''Thank you.'''

|| Thank you for joining.

|}