Difference between revisions of "PostgreSQL-Database/C2/Foreign-Key-Constraint/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
 
(One intermediate revision by one other user not shown)
Line 1: Line 1:
  
 +
'''Title of the Script: Foreign Key Constraint'''
 +
'''
 +
Author: EduPyramids'''
 +
 +
 +
'''Keywords''': PostgreSQL, pgAdmin4, Foreign Key Constraint, primary key, Database, Database node, table, Delete,  EduPyramids, Video Tutorial.
  
  
Line 15: Line 21:
 
|-
 
|-
 
|| '''Slide 2'''  
 
|| '''Slide 2'''  
 +
 
'''Learning Objectives'''
 
'''Learning Objectives'''
 
|| In this tutorial, we will learn about:
 
|| In this tutorial, we will learn about:
Line 26: Line 33:
 
* '''PostgreSQL version 18.1''', and
 
* '''PostgreSQL version 18.1''', and
 
* '''PgAdmin4 version 9.11'''.
 
* '''PgAdmin4 version 9.11'''.
 +
 +
EduPyramids periodically verifies the correctness of the tutorials for subsequent versions. Changes, if any, are explained through the Instruction Sheet.
 +
 
|| To record this tutorial, I am using the following setup.
 
|| To record this tutorial, I am using the following setup.
  
Line 32: Line 42:
  
 
'''Pre-requisites'''
 
'''Pre-requisites'''
 +
 +
'''[https://edupyramids.org/ https://EduPyramids.org]'''
  
 
|| To follow this tutorial,
 
|| To follow this tutorial,
Line 45: Line 57:
  
 
The following code file is required to practice this tutorial:
 
The following code file is required to practice this tutorial:
−
* '''Fk-commands.txt'''
+
 
 +
* '''fk-commands.txt'''
 +
 
 
This file is provided in the Code Files link on this tutorial page.
 
This file is provided in the Code Files link on this tutorial page.
  
Line 58: Line 72:
 
|-
 
|-
 
|| '''Open pgAdmin 4'''
 
|| '''Open pgAdmin 4'''
−
 
 
|| I have opened '''pgAdmin4''' and connected to the '''PostgreSQL''' server.
 
|| I have opened '''pgAdmin4''' and connected to the '''PostgreSQL''' server.
  
Line 69: Line 82:
  
 
From the submenu select Create and then select Database.
 
From the submenu select Create and then select Database.
 +
 
Create database window opens.
 
Create database window opens.
−
Type database name: Company
+
 
−
Click Save at the bottom right corner.
+
Type database name: '''Company'''.
 +
 
 +
Click '''Save''' at the bottom right corner.
 
|| Right-click on the '''Databases node'''.
 
|| Right-click on the '''Databases node'''.
 +
 
From the submenu select '''Create''' and then select '''Database'''.
 
From the submenu select '''Create''' and then select '''Database'''.
 +
 
In the '''Create Database''' window, let us type the '''Database''' name as '''Company'''.
 
In the '''Create Database''' window, let us type the '''Database''' name as '''Company'''.
  
Line 84: Line 102:
 
|-
 
|-
 
|| Right click on '''Tables''' node and select Query Tool.
 
|| Right click on '''Tables''' node and select Query Tool.
 +
 
Query window opens.
 
Query window opens.
  
−
|| Right click on '''Tables''' node and select the '''Query Tool''' option.<br><br>
+
|| Right click on '''Tables''' node and select the '''Query Tool''' option.
 +
 
 
The '''Query''' window opens.
 
The '''Query''' window opens.
  
Line 100: Line 120:
  
 
|| Let me create a table named '''department'''.
 
|| Let me create a table named '''department'''.
 +
 
Type this query in the query window.
 
Type this query in the query window.
 +
 
Here '''department id''' is the '''primary key'''.
 
Here '''department id''' is the '''primary key'''.
 +
 
It uniquely identifies each department.
 
It uniquely identifies each department.
  
Line 119: Line 142:
  
 
|| Now let us create a table named '''employee'''.
 
|| Now let us create a table named '''employee'''.
 +
 
Type the following query.
 
Type the following query.
 +
 
In this query '''employee id''' is the '''primary key.'''
 
In this query '''employee id''' is the '''primary key.'''
 +
 
In this table '''department id''' is just a column.
 
In this table '''department id''' is just a column.
 +
 
There is no restriction on its values.
 
There is no restriction on its values.
 +
 
Click the '''Execute query icon''' to execute the query.
 
Click the '''Execute query icon''' to execute the query.
  
Line 139: Line 167:
  
 
|| Now let us insert some data into the '''department''' table.
 
|| Now let us insert some data into the '''department''' table.
 +
 
Now type the following query.
 
Now type the following query.
  
Line 148: Line 177:
 
'''SELECT * FROM department;'''
 
'''SELECT * FROM department;'''
  
−
Click on the '''execute query button**.
+
Click on the '''execute query button.
  
 
|| We will now type this query.
 
|| We will now type this query.
 +
 
Let us execute the query to see the created table.
 
Let us execute the query to see the created table.
 +
 
Here is the '''department''' table with 3 records.
 
Here is the '''department''' table with 3 records.
  
Line 167: Line 198:
  
 
|| Now let us insert some data into the '''employee''' table.
 
|| Now let us insert some data into the '''employee''' table.
 +
 
Type this query to insert the records in the '''employee''' table.
 
Type this query to insert the records in the '''employee''' table.
 +
 
Let us execute the query and see the output.
 
Let us execute the query and see the output.
  
Line 174: Line 207:
  
 
'''SELECT * FROM employee;'''
 
'''SELECT * FROM employee;'''
 +
 
Click the '''Execute query icon.
 
Click the '''Execute query icon.
  
Line 179: Line 213:
  
 
Let us execute the query and see the output.
 
Let us execute the query and see the output.
 +
 
Here is the employee table.
 
Here is the employee table.
  
Line 185: Line 220:
  
 
|| Observe that, dept id 10 and dept id 5 are not available in the department table.
 
|| Observe that, dept id 10 and dept id 5 are not available in the department table.
 +
 
However the database still allows the values.
 
However the database still allows the values.
 
This creates inconsistent data.
 
This creates inconsistent data.
Line 190: Line 226:
 
|-
 
|-
 
||  
 
||  
−
|| To prevent such issues, we use a foreign key constraint.
+
|| To prevent such issues, we use a '''foreign key constraint'''.
  
 
|-
 
|-
Line 200: Line 236:
  
 
|| Let us first delete the inconsistent data from the '''employee''' table.
 
|| Let us first delete the inconsistent data from the '''employee''' table.
 +
 
Type this query.
 
Type this query.
 +
 
In this query the '''Delete''' keyword deletes records.
 
In this query the '''Delete''' keyword deletes records.
 +
 
Let us execute the query.
 
Let us execute the query.
 +
 
The output shows the query returned successfully.
 
The output shows the query returned successfully.
  
Line 213: Line 253:
  
 
|| Let us type this query to see the updated table.
 
|| Let us type this query to see the updated table.
 +
 
Let us execute the query.
 
Let us execute the query.
 +
 
The records with dept id 10 and dept id  5 are deleted from the '''employee''' table.
 
The records with dept id 10 and dept id  5 are deleted from the '''employee''' table.
  
Line 221: Line 263:
  
 
|-
 
|-
−
|| '''Slide 6
+
|| '''Slide 6'''
  
−
What is a Foreign key?'''
+
'''What is a Foreign key?'''
  
 
|| A foreign key is a column that references a primary key in another table.
 
|| A foreign key is a column that references a primary key in another table.
Line 230: Line 272:
  
 
|-
 
|-
−
|| '''Slide 7
+
|| '''Slide 7'''
 +
 
 +
'''Syntax of Foreign key'''
  
−
Syntax of Foreign key
+
'''FOREIGN KEY (column_name) REFERENCES parent_table(column_name);'''
−
FOREIGN KEY (column_name) REFERENCES parent_table(column_name);'''
+
  
 
|| This is the syntax of the Foreign key constraint.
 
|| This is the syntax of the Foreign key constraint.
Line 253: Line 296:
  
 
This query adds a '''foreign key''' on dept id.
 
This query adds a '''foreign key''' on dept id.
 +
 
Each value must exist in the department table.
 
Each value must exist in the department table.
 
We will now execute the query.
 
We will now execute the query.
 +
 
Output shows '''Query''' returned successfully.
 
Output shows '''Query''' returned successfully.
  
Line 268: Line 313:
  
 
|| Now, let us insert an invalid department id.
 
|| Now, let us insert an invalid department id.
−
Type the following query
+
 
 +
Type the following query.
 +
 
 
Let us execute the query.
 
Let us execute the query.
 +
 
The result shows an error.
 
The result shows an error.
  
Line 275: Line 323:
 
|| '''Type:
 
|| '''Type:
  
−
INSERT INTO employee'''
+
'''INSERT INTO employee'''
 
'''VALUES'''
 
'''VALUES'''
 
'''(104, 'Simran', 65000, 3),'''
 
'''(104, 'Simran', 65000, 3),'''
Line 286: Line 334:
  
 
A foreign key links two tables.
 
A foreign key links two tables.
 +
 
It ensures data integrity by preventing invalid entries.
 
It ensures data integrity by preventing invalid entries.
  
Line 291: Line 340:
 
|| Type:
 
|| Type:
  
−
**SELECT * FROM employee;'''
+
'''SELECT * FROM employee;'''
  
−
Click the '''Execute query icon.**
+
Click the '''Execute query icon.
  
 
|| Let us type this query to see the updated table.
 
|| Let us type this query to see the updated table.
 +
 
Let us execute the query.
 
Let us execute the query.
 +
 
The records are now updated in the employee table.
 
The records are now updated in the employee table.
  
Line 306: Line 357:
 
In this tutorial, we have learnt about:
 
In this tutorial, we have learnt about:
  
−
*Foreign key constraint
+
* Foreign key constraint
  
  
 
|| With this we come to the end of this tutorial.
 
|| With this we come to the end of this tutorial.
 +
 
Let us summarise
 
Let us summarise
  
Line 320: Line 372:
  
 
'''Create a table named project with columns:
 
'''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.'''
+
'''project_id, project_name, and dept_id'''.
−
'''Insert a valid record and execute the query.'''
+
 
−
'''Insert an invalid record and observe the error.'''
+
'''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.
 
|| We encourage you to try this assignment.
Line 334: Line 389:
 
This Spoken Tutorial is brought to you by EduPyramids Educational Services Private Limited SINE IIT Bombay.
 
This Spoken Tutorial is brought to you by EduPyramids Educational Services Private Limited SINE IIT Bombay.
  
−
'''Thank you.'''
+
'''Thank you'''.
  
 
|| Thank you for joining.
 
|| Thank you for joining.
  
 
|}
 
|}

Latest revision as of 15:47, 12 May 2026

Title of the Script: Foreign Key Constraint Author: EduPyramids


Keywords: PostgreSQL, pgAdmin4, Foreign Key Constraint, primary key, Database, Database node, table, Delete, EduPyramids, Video Tutorial.


Visual Cue 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.

EduPyramids periodically verifies the correctness of the tutorials for subsequent versions. Changes, if any, are explained through the Instruction Sheet.

To record this tutorial, I am using the following setup.
Slide 4

Pre-requisites

https://EduPyramids.org

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.

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.

Contributors and Content Editors

ANJALISATDIVE, Madhurig

Retrieved from "https://script.spoken-tutorial.org/index.php?title=PostgreSQL-Database/C2/Foreign-Key-Constraint/English&oldid=57713"