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

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