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

From Script | Spoken-Tutorial
Jump to: navigation, search
Time Narration
00:01 Welcome to the Spoken Tutorial on Foreign key constraint.
00:06 In this tutorial, we will learn about

Foreign key constraint,

Alter table command,

00:13 how to add a foreign key and

check constraint.

00:17 To record this tutorial, I am using: Ubuntu Linux 14.04 operating system,
00:24 PostgreSQL 9.3.x and

pgAdmin 1.18

00:32 To follow this tutorial, you should have basic understanding of database and tables.
00:39 For more details, refer to the RDBMS – PostgreSQL series on this website.
00:46 This is the students table which we created.
00:49 I want to add details such as department name and department head for each student as shown here.
00:58 As we can see, the department name and department head data is repeated.
01:04 So, we will separate these data into different tables and then relate them with foreign key.
01:11 It is important to separate student and department data into separate tables since one can exist without another.
01:20 What is a foreign key?
01:22 A foreign key represents a link between columns in two different tables.
01:28 A foreign key in a table points to a primary key in another table.
01:33 A table can contain more than one foreign key constraint.
01:38 The table that has the foreign key is called as the referencing table or child table.
01:45 The table to which the foreign key references is called the referenced table or parent table.
01:52 Let’s see an example of foreign key link between students and department tables.
01:59 Here, the department table is the parent table in which the departmentid is the primary key.

Students table is the child table.

02:09 Deptid column is the foreign key which references the departmentid of the department table.
02:17 Note that the foreign key column and the referenced key column must have similar data types.
02:24 Open the SQL editor window in the pgAdmin.
02:29 First let us create the parent table department i.e the referenced table.
02:35 Type the code.

Departmenttid is the primary key which uniquely identifies each department.

The parent table department is ready to execute.

02:49 Let's insert some data to the department table.
02:53 Clear the editor window.

Type the code and execute the query.

02:59 Now, let us retrieve the records.

Clear the window. Type: select asterisk from department.

03:10 This is the parent table. Each student must belong to one of the departments shown here.
03:19 Switch back to the pgAdmin main screen.
03:22 We have already created the students table with the 'Create table' command.
03:27 Next we need to add a new column deptid to the existing students table.

This will assign each student to a particular department.

03:39 To do so, we need to use the Alter table command.
03:43 Alter table command is used to change the structure of an existing table.
03:49 This command is used to add, delete or modify columns in an existing table.

It is used to add or drop constraints.

04:00 Using this command, even the data type of the columns can be changed.
04:06 The syntax to Alter table is ALTER TABLE table-name ADD new-column-name data type;
04:16 The syntax to add multiple columns at the same time is shown here.
04:22 Let’s switch back to the pgadmin SQL editor window for the demonstration.
04:28 Let us add a new column by the name deptid to the students table.

Type the code and execute.

04:37 Let us check this.

Go to the pgAdmin main screen.

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

We can see the deptid column.

04:52 Now, let us add the foreign key constraint.
04:56 Type the code as shown. Here, deptid column of students table is a foreign key.
05:05 This references the departmentid column of the department table.
05:11 The foreign key constraint maintains referential integrity between the child and parent tables.
05:18 This means that the values in a column must match the values in some row of another table.
05:25 Let us now execute the query.
05:28 Go to the pgAdmin main screen.

Right click on the students table node and click refresh.

05:36 In the SQL Pane, we can see a constraint name for each constraint automatically created.
05:43 We can also specify a constraint name while creating or altering a table.
05:49 Use this constraint name to drop the constraint if it is not required later.
05:55 Now right-click on students node, and then on View data and then on View All rows.
06:03 Deptid column is empty. Let us add department details to each student as shown.
06:12 Let me type 'MEC' for Mechanical department, for the student Pooja and press Enter.
06:20 We can see a error message of “foreign key violation”.

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

06:33 Foreign key constraint prevents invalid data from being inserted into the foreign key columns.
06:40 It has to be one of the values of the referencing column. I.e departmentid column value from the department table.
06:49 Click on the OK button.
06:52 Now, I'll enter 'MT' as department id and save the record.
06:58 Enter a valid departmentid for other students also as shown.
07:04 Let me open department and students table in 2 different windows.
07:09 These tables have One-to-Many relationship.

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

07:19 This relationship is created using Primary key and Foreign key constraints.
07:25 Let us see the advantages of using Foreign key.

It will enforce the constraint.

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

It controls redundancy and avoid inconsistency.

07:42 For example, it controls the entry of different names of head for the same department.
07:50 Next we will learn more about Alter table command.
07:54 We can use ALTER TABLE Alter column command to change the data type or length of a column in a table.
08:02 The syntax is ALTER TABLE table-name ALTER column-name TYPE data type;
08:11 Say, I want to change the cgpa column's length from 2,1 to 3,1.
08:18 Type the code as shown and then execute.
08:22 Now, the cgpa column length is altered in the students table.
08:27 Let's check. Refresh the students table and see the changes.
08:34 Next we will learn about constraints.
08:37 Constraints are the conditions or rules enforced on columns or tables.
08:43 These are used to prevent invalid data entry into the database.
08:48 Column level constraints are applied to only one column.

Table level constraints are applied to the whole table.

08:57 Some of the commonly used constraints in PostgreSQL are:

NOT NULL constraint,

UNIQUE constraint,

09:04 Primary key constraint,

Foreign key constraint,

and Check constraint.

09:11 Next, we will see how to add a check constraint to the cgpa column.
09:16 Type the code as shown.

Alter table students add constraint cgpa underscore chk check cgpa greater than or equal to zero and cgpa less than or equal to 10.

09:33 Here, I have given the constraint name as cgpa underscore chk.
09:39 It specifies a condition that the cgpa value should be between 0 and 10.

This restricts the user from entering invalid data.

09:51 Let’s now execute the query.
09:54 Now, let us insert one more record to the students table with cgpa as 22 and see what happens.
10:02 Right-click on students node, select View Data and then Views All Rows.
10:08 I'll enter the data as shown.
10:11 In the cgpa column, I'll enter 22 and press Enter.
10:17 We can see an error message of check constraint violation.
10:22 This indicates that we have entered a invalid data.

Click on the “OK” button.

10:29 Now enter 10 as cgpa, CS as deptid and save the record.

The record is successfully saved.

10:41 Let us see the syntax for how to use constraints in the create table command.
10:47 Switch to the pgAdmin main screen.
10:50 Click on the students node and then refresh.
10:54 Now come to the sql pane.

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

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

Let us summarize.

11:17 In this tutorial, we have learnt about

Foreign key constraint,

Alter table command,

11:23 how to add a foreign key and Check constraints.
11:28 As an Assignment, alter the students table to add a NOT NULL constraint to the column city.
11:35 Insert a record and leave the city column empty.

Save the record. Note the error and resolve it.

11:44 The video at the following link summarizes the Spoken Tutorial project.

Please download and watch it.

11:52 The Spoken Tutorial Project team conducts workshops gives certificates.

For more details, please write to us.

12:00 Please post your timed queries in this forum.
12:04 Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

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

Contributors and Content Editors

PoojaMoolya, Sandhya.np14