RDBMS-PostgreSQL/C2/Foreign-key-Constraint/English

From Script | Spoken-Tutorial
Jump to: navigation, search

>>Title of script: Foreign key constraint

Author: Nirmala Venkat

Keywords: PostgreSQL, PgAdmin, Foreign key, referenced table, referencing table, Alter table, Constraints, video tutorial

Visual Cue Narration
Slide 1: Welcome to the Spoken Tutorial on Foreign key constraint.
Slide 2:

Learning Objectives

In this tutorial we will learn about
  • Foreign key constraint
  • Alter table command
  • How to add a foreign key and
  • Check constraint
Slide 3:

System requirement

To record this tutorial, I am using
  • Ubuntu Linux 14.04 operating system
  • PostgreSQL 9.3.x
  • pgAdmin 1.18
Slide 4:

Pre-requisites

To follow this tutorial, you should have
  • Basic understanding of database and tables
  • For more details, refer to the RDBMS – PostgreSQL series on this website
Show image of the students table This is the students table which we created.
Show image of the students table along with dept details ( students-dept.png) I want to add details such as department name and department head for each student as shown here.
Point to the repeating column As we can see, the department name and department head data is repeated.
Show image of the two tables department and student.


So we will separate these data into different tables and then relate them with foreign key.

It is important to separate student and dept data into separate tables since one can exist without another.

What is a foreign key?
Slide 5(a)

Foreign key:

A foreign key represents a link between columns in two different tables.

A foreign key in a table points to a primary key in another table.

A table can contain more that one foreign key constraint.

Slide 5(b)

Foreign key:

The table that has the foreign key is called as the referencing table or child table.

The table to which the foreign key references is called the referenced table or parent table.

Let’s see an example of foreign key link between students and department tables.
Show the image of students and department table (student-dept-link.p ng) Here the department table is the parent table in which the departmentid is the primary key.

Students table is the child table.

Deptid column is the foreign key which references the departmentid of the department table.

Note that the foreign key column and the referenced key column must have similar data types.

Click on the SQL editor icon Open the SQL editor window in the pgAdmin.
Now let us first create the parent table department i.e the referenced table.
Type the code

CREATE TABLE department

(

departmentid character varying(5) NOT NULL,

deptname character varying(20) NOT NULL,

depthead character varying(30),

PRIMARY KEY (departmentid)

)

Type the code.

Departmenttid is the primary key which uniquely identifies each department.

The parent table department is ready to execute.

Let's insert some data to the department table.
Click on the clear icon Clear the editor window.
INSERT INTO department(

departmentid, deptname, depthead) VALUES

('CS','Computer Science','Mr.Prasad'),

('EE','Electronics','Ms.Aysha'),

('ECO','Economics', 'Mr.Suresh'),

('MT','Mathematics', 'Mr.Pradeep'),

('MN','Management', 'Mr.Anand '),

('HM', 'Humanities','Ms.Sheena');

Type the code and execute the query.
Show the inserted data

Type,

Select * from department

Now, let us retrieve the records.

Clear the window. Type select asterisk from department.

This is the parent table. Each student must belong to one of the departments shown here.

Switch back to the pgAdmin main screen.
Point to the students table We have already created the students table with the 'Create table' command.

Next we need to add a new column deptid to the existing students table.

This will assign each student to a particular department.

To do so, we need to use the Alter table command.
Slide 6:

Alter table

Alter table command is used to

  • change the structure of an existing table
  • add, delete or modify columns in an existing table
  • add or drop constraints
  • change the data type of columns
Alter table command is used to change the structure of an existing table.

This command is used to add, delete or modify columns in an existing table.

It is used to add or drop constraints.

Using this command, even the data type of the columns can be changed.

Slide 7:

Syntax: Alter table

The syntax to Alter table is

ALTER TABLE table-name ADD new-column-name data type;

The syntax to add multiple columns at the same time, is shown here.

Switch back to the pgadmin SQL editor window Let’s switch back to the pgadmin SQL editor window for the demonstration.
Type the code

ALTER TABLE students ADD deptid varchar(5)

Let us add a new column by the name deptid to the students table.

Type the code and execute.

Switch to pgAdmin main screen Let us check this.

Go to the pgAdmin main screen.

Right click on students column node and select Refresh Right-click on students column node and select Refresh to update the changes.

We can see the deptid column.

Now let us add the foreign key constraint.
ALTER TABLE students ADD foreign key(deptid) references department(departmentid) Type the code as shown.

Here, deptid column of students table is a foreign key.

This references the departmentid column of the department table.

The foreign key constraint maintains referential integrity between the child and parent tables.

This means that the values in a column must match the values in some row of another table.

Click on execute query button Let us now execute the query.
Go to the pgAdmin main screen. Click on the students table. Go to the pgAdmin main screen.

Right click on the students table node and click refresh.

Highlight the script according to narration In the SQL Pane, we can see a constraint name for each constraint automatically created.

We can also specify a constraint name while creating or altering a table.

Use this constraint name to drop the constraint if it is not required later.

Right click on students node>> View data >>View All rows. Now right-click on students node, then on View data and then on View All rows.
Point to the deptdid column Deptid column is empty. Let us add department details to each student as shown.
Type 'MEC' Let me type 'MEC' for Mechanical department for the student Pooja and press Enter.
Point to the error message We can see a error message of “foreign key violation”.

It says 'deptid='mec' is not present in table department.

Highlight departmentid >> Click on OK Foreign key constraint prevents invalid data from being inserted into the foreign key columns.

It has to be one of the values of the referencing column. I.e departmentid column value from the department table.

Click on the OK button.

Type 'MT' in the deptid column.

Click on the save icon.

Now, I'll enter 'MT' as department id and save the record.
Enter departmentid for other records as well. Enter a valid departmentid for other students also as shown.
Open the department and students table Let me open department and students table in 2 different windows.
Point to the one to many records These tables have One-to-Many relationship.

A row from one table can have multiple matching rows in another table.

This relationship is created using Primary key – Foreign key constraints.

Slide 8:

Advantages of using Foreign Key:

  • Enforce the constraint
  • Controls redundancy and avoid inconsistency
Let us see the advantages of using Foreign key.

It will enforce the constraint.

For example, it will enforce every student should have a department.

It controls redundancy and avoid inconsistency.

For example, it controls the entry of different names of head for the same department

Next we will learn more about Alter table command.
Slide 9:

ALTER TABLE – ALTER column

To change the data type or size of a column in a table.

Syntax:

ALTER TABLE table-name ALTER column-name TYPE data type;

We can use ALTER TABLE Alter column command to change the data type or length of a column in a table.

The syntax is

ALTER TABLE table-name ALTER column-name TYPE data type;

Point to the cgpa column Say, I want to change the cgpa column's length from 2,1 to 3,1.
ALTER TABLE students ALTER cgpa type numeric(3,1) Type the code as shown and then execute.
Now the cgpa column length is altered in the students table.
Point to the cgpa column in the SQLpane script. Let's check.

Refresh the students table and see the changes.

Next we will learn about constraints.
Slide 9:

Constraints:

Constraints are the conditions or rules enforced on columns or tables.


These are used to prevent invalid data entry into the database.

Slide 9:

Constraints:

Column level constraints are applied to only one column.

Table level constraints are applied to the whole table.

Slide 10:

Constraints:

Some of the commonly used constraints in PostgreSQL are:

NOT NULL constraint

UNIQUE constraint

Primary key constraint

Foreign key constraint

Check constraint

Next we will see how to add a check constraint to the cgpa column.
Type the code as shown. Type the code as shown.

Alter table students add constraint cgpa underscore chk check (cgpa >=0 and cgpa <=10)

Here I have given the constraint name as cgpa underscore chk.

It specifies a condition that the cgpa value should be between 0 and 10.

This restricts the user from entering invalid data.

Let’s now execute the query.
Now let us insert one more record to the students table with cgpa as 22 and see what happens.
Right click on students node and select View Data and then Views All Rows. Right-click on students node, select View Data and then Views All Rows.
Type the data for each column I'll enter the data as shown.
Type 22 in cgpa column and press Enter. In the cgpa column, I'll enter 22 and press Enter.
Point to the error message

Click on the “OK” button.

We can see an error message of check constraint violation.

This indicates that we have entered a invalid data.

Click on the “OK” button.

Enter '10' as cgpa. Now enter 10 as cgpa, CS as deptid and save the record.

The record is successfully saved.

Let us see the syntax for how to use constraints in the create table command.
switch to the pgAdmin main screen Switch to the pgAdmin main screen.
Click on the students node Click on the students node.
Highlight the code Now come to the sql pane.

Here we can see the create table syntax for the students table along with the constraints.

Highlight the code We can add constraints while creating a table if the table structure is properly designed initially.
With this we come to the end of this tutorial.

Let us summarize.

Slide 11:

Summary

In this tutorial, we have learnt about
  • Foreign key constraint
  • Alter table command
  • How to add a foreign key and
  • Check constraints
Slide 12:

Assignment

As an Assignment,
  1. Alter the students table to add a NOT NULL constraint to the column city
  2. Insert a record and leave the city column empty
  3. Save the record. Note the error and resolve it.
Slide 12:

About Spoken Tutorial project

The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

Slide 13:

Spoken Tutorial workshops

The Spoken Tutorial Project Team
  • conducts workshops
  • gives certificates

For more details, please write to us.

Slide 13:

Forum for specific questions

* Please post your timed queries in this forum.
Slide 14:

Acknowledgement

Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at

this link.

This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

Contributors and Content Editors

Nirmala Venkat