RDBMS-PostgreSQL/C2/Foreign-key-Constraint/English-timed
From Script | Spoken-Tutorial
| 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. |
