PostgreSQL-Database/C2/Foreign-Key-Constraint/English

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