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

>>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 Basic understanding of database and tables For more details, refer to the RDBMS – PostgreSQL series on http://spoken-tutorial.org/	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, Alter the students table to add a NOT NULL constraint to the column city Insert a record and leave the city column empty 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