Difference between revisions of "RDBMS-PostgreSQL/C2/Foreign-key-Constraint/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with "{| border=1 | '''Time''' | '''Narration''' |- |00:01 | Welcome to the''' Spoken Tutorial''' on''' Foreign key constraint.''' |- | 00:06 | In this tutorial we will lea...")
 
 
Line 9: Line 9:
 
|-  
 
|-  
 
| 00:06
 
| 00:06
−
| In this tutorial we will learn about  
+
| In this tutorial, we will learn about  
  
−
'''Foreign key constraint'''  
+
'''Foreign key constraint''',
  
−
'''Alter table''' command  
+
'''Alter table''' command,
  
 
|-  
 
|-  
 
| 00:13
 
| 00:13
−
| How to add a '''foreign key''' and  
+
| how to add a '''foreign key''' and  
  
−
Check '''constraint'''  
+
check '''constraint'''.
  
 
|-  
 
|-  
 
| 00:17
 
| 00:17
−
| To record this tutorial, I am using  '''Ubuntu Linux 14.04''' operating system  
+
| To record this tutorial, I am using: '''Ubuntu Linux 14.04''' operating system,
  
 
|-  
 
|-  
Line 33: Line 33:
 
|-  
 
|-  
 
| 00:32
 
| 00:32
−
| To follow this tutorial, you should have  Basic understanding of '''database''' and '''tables'''  
+
| To follow this tutorial, you should have  basic understanding of '''database''' and '''tables'''.
  
 
|-  
 
|-  
 
| 00:39
 
| 00:39
−
| For more details, refer to the '''RDBMS – PostgreSQL''' series on this website  
+
| For more details, refer to the '''RDBMS – PostgreSQL''' series on this website.
  
 
|-  
 
|-  
Line 53: Line 53:
 
|-  
 
|-  
 
| 01:04
 
| 01:04
−
| So we will separate these data into different tables and then relate them with '''foreign key.'''  
+
| So, we will separate these data into different tables and then relate them with '''foreign key.'''  
  
 
|-  
 
|-  
Line 65: Line 65:
 
|-  
 
|-  
 
| 01:22
 
| 01:22
−
| A '''foreign key''' represents a link between columns in two different '''tables'''.  
+
| A '''foreign key''' represents a link between '''column'''s in two different '''tables'''.  
  
 
|-  
 
|-  
Line 77: Line 77:
 
|-  
 
|-  
 
| 01:38
 
| 01:38
−
| The '''table '''that has the '''foreign key''' is called as the '''referencing''' '''table''' or '''child table'''.  
+
| The '''table '''that has the '''foreign key''' is called as the '''referencing''' table or '''child table'''.  
  
 
|-  
 
|-  
 
| 01:45
 
| 01:45
−
| The '''table''' to which the''' foreign key''' references is called the '''referenced''' '''table''' or '''parent table'''.  
+
| The '''table''' to which the''' foreign key''' references is called the '''referenced''' table or '''parent table'''.  
  
 
|-  
 
|-  
 
| 01:52
 
| 01:52
−
| Let’s see an example of '''foreign key''' link between '''students''' and '''department tables'''.  
+
| Let’s see an example of '''foreign key''' link between '''students''' and '''department''' tables.  
  
 
|-  
 
|-  
 
| 01:59
 
| 01:59
−
| Here the '''department table''' is the '''parent table''' in which the '''departmentid''' is the '''primary key.'''  
+
| Here, the '''department table''' is the '''parent table''' in which the '''departmentid''' is the '''primary key.'''  
  
 
'''Students table''' is the '''child table.'''  
 
'''Students table''' is the '''child table.'''  
Line 115: Line 115:
 
'''Departmenttid''' is the '''primary key '''which uniquely identifies each department.  
 
'''Departmenttid''' is the '''primary key '''which uniquely identifies each department.  
  
−
The '''parent table department '''is ready to execute.  
+
The '''parent table department '''is ready to '''execute'''.  
  
 
|-  
 
|-  
 
| 02:49
 
| 02:49
−
| Let's insert some data to the '''department table.'''  
+
| Let's insert some data to the '''department''' table.
  
 
|-  
 
|-  
Line 129: Line 129:
 
|-  
 
|-  
 
|02:59
 
|02:59
−
| Now, let us retrieve the records.  
+
| Now, let us retrieve the '''record'''s.  
  
−
Clear the window. Type '''select asterisk from department.'''  
+
Clear the window. Type: '''select asterisk from department.'''  
  
 
|-  
 
|-  
 
| 03:10
 
| 03:10
−
| This is the '''parent table'''. Each student must belong to one of the departments shown here.  
+
| This is the '''parent''' table. Each student must belong to one of the departments shown here.  
  
 
|-  
 
|-  
Line 143: Line 143:
 
|-  
 
|-  
 
| 03:22
 
| 03:22
−
| We have already created the '''students table '''with the ''''Create''' '''table'''' command.  
+
| We have already created the '''students''' table with the ''''Create table'''' command.  
  
 
|-  
 
|-  
 
| 03:27
 
| 03:27
−
| Next we need to add a new column '''deptid''' to the existing '''students table.'''  
+
| Next we need to add a new column '''deptid''' to the existing '''students''' table.
  
 
This will assign each student to a particular department.  
 
This will assign each student to a particular department.  
Line 175: Line 175:
 
|-  
 
|-  
 
| 04:16
 
| 04:16
−
| The syntax to add multiple columns at the same time, is shown here.  
+
| The syntax to add multiple columns at the same time is shown here.  
  
 
|-  
 
|-  
Line 201: Line 201:
 
|-  
 
|-  
 
| 04:52
 
| 04:52
−
| Now let us add the '''foreign key constraint'''.  
+
| Now, let us add the '''foreign key constraint'''.  
  
 
|-  
 
|-  
Line 213: Line 213:
 
|-  
 
|-  
 
| 05:11
 
| 05:11
−
| The '''foreign key constraint '''maintains '''referential''' '''integrity''' between the '''child '''and '''parent tables.'''  
+
| The '''foreign key constraint '''maintains '''referential integrity''' between the '''child '''and '''parent tables.'''  
  
 
|-  
 
|-  
Line 227: Line 227:
 
| Go to the '''pgAdmin''' main screen.  
 
| Go to the '''pgAdmin''' main screen.  
  
−
Right click on the '''students table '''node and click '''refresh'''.  
+
Right click on the '''students''' table node and click '''refresh'''.  
  
 
|-  
 
|-  
Line 235: Line 235:
 
|-  
 
|-  
 
| 05:43
 
| 05:43
−
| We can also specify a '''constraint''' name while creating or '''altering '''a '''table'''.  
+
| We can also specify a '''constraint''' name while creating or altering a table.  
  
 
|-  
 
|-  
 
| 05:49
 
| 05:49
−
| Use this '''constraint''' name to drop the '''constraint''' if it is not required later.  
+
| Use this '''constraint''' name to drop the constraint if it is not required later.  
  
 
|-  
 
|-  
 
| 05:55
 
| 05:55
−
| Now right-click on '''students''' node, and then on '''View data''' and then on '''View''' '''All rows.'''  
+
| Now right-click on '''students''' node, and then on '''View data''' and then on '''View All rows.'''  
  
 
|-  
 
|-  
Line 251: Line 251:
 
|-  
 
|-  
 
| 06:12
 
| 06:12
−
| Let me type ''''MEC'''' for '''Mechanical department '''for the student '''Pooja''' and press '''Enter'''.  
+
| Let me type ''''MEC'''' for '''Mechanical department, '''for the student '''Pooja''' and press '''Enter'''.  
 
   
 
   
 
|-  
 
|-  
Line 257: Line 257:
 
| We can see a error message of '''“foreign key violation”.'''  
 
| We can see a error message of '''“foreign key violation”.'''  
  
−
It says ''''deptid='mec' '''is not present in '''table department.'''  
+
It says '''deptid='mec' '''is not present in table '''department.'''  
  
 
|-  
 
|-  
Line 273: Line 273:
 
|-  
 
|-  
 
| 06:52
 
| 06:52
−
| Now, I'll enter ''''MT'''' as '''department id''' and save the record.  
+
| Now, I'll enter 'MT' as '''department id''' and '''save''' the '''record'''.  
  
 
|-  
 
|-  
Line 285: Line 285:
 
|-  
 
|-  
 
| 07:09
 
| 07:09
−
| These '''tables '''have '''One-to-Many relationship'''.  
+
| These tables have '''One-to-Many relationship'''.  
  
−
A row from one '''table '''can have multiple matching rows in another '''table'''.  
+
A row from one table can have multiple matching rows in another table.  
  
 
|-  
 
|-  
 
| 07:19
 
| 07:19
−
| This '''relationship '''is created using '''Primary key and Foreign key constraints.'''  
+
| This relationship is created using '''Primary key''' and '''Foreign key''' constraints.
  
 
|-  
 
|-  
 
| 07:25
 
| 07:25
−
| Let us see the advantages of using Foreign key.  
+
| Let us see the advantages of using '''Foreign key'''.  
  
 
It will enforce the constraint.  
 
It will enforce the constraint.  
Line 307: Line 307:
 
|-  
 
|-  
 
| 07:42
 
| 07:42
−
| For example, it controls the entry of different names of head for the same department  
+
| For example, it controls the entry of different names of head for the same department.
  
 
|-  
 
|-  
Line 327: Line 327:
 
|-  
 
|-  
 
| 08:18
 
| 08:18
−
| Type the code as shown and then execute.  
+
| Type the '''code''' as shown and then '''execute'''.  
  
 
|-  
 
|-  
 
| 08:22
 
| 08:22
−
| Now the '''cgpa''' column length is altered in the '''students table.'''  
+
| Now, the '''cgpa''' column length is altered in the '''students table.'''  
  
 
|-  
 
|-  
 
| 08:27
 
| 08:27
−
| Let's check. Refresh the '''students table '''and see the changes.  
+
| Let's check. '''Refresh''' the '''students table '''and see the changes.  
  
 
|-  
 
|-  
Line 359: Line 359:
 
| Some of the commonly used constraints in '''PostgreSQL''' are:  
 
| Some of the commonly used constraints in '''PostgreSQL''' are:  
  
−
'''NOT NULL constraint'''  
+
'''NOT NULL constraint''',
  
−
'''UNIQUE constraint'''  
+
'''UNIQUE constraint''',
  
 
|-  
 
|-  
 
| 09:04
 
| 09:04
−
| '''Primary key constraint'''  
+
| '''Primary key constraint''',
  
−
'''Foreign key constraint'''  
+
'''Foreign key constraint''',
  
−
and '''Check constraint'''  
+
and '''Check constraint'''.
  
 
|-  
 
|-  
 
| 09:11
 
| 09:11
−
| Next we will see how to add a '''check constraint '''to the '''cgpa''' column.  
+
| Next, we will see how to add a '''check constraint '''to the '''cgpa''' column.  
  
 
|-  
 
|-  
Line 379: Line 379:
 
| Type the code as shown.  
 
| 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'''  
+
'''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
 
| 09:33
−
| Here I have given the '''constraint '''name as '''cgpa underscore chk.'''  
+
| Here, I have given the '''constraint '''name as '''cgpa underscore chk.'''  
  
 
|-  
 
|-  
Line 397: Line 397:
 
|-  
 
|-  
 
| 09:54
 
| 09:54
−
| Now let us insert one more record to the students table with cgpa as 22 and see what happens.  
+
| Now, let us insert one more record to the students table with '''cgpa''' as '''22''' and see what happens.  
  
 
|-  
 
|-  
Line 413: Line 413:
 
|-  
 
|-  
 
| 10:17
 
| 10:17
−
| We can see an error message of '''check constraint violation.'''  
+
| We can see an '''error''' message of '''check constraint violation.'''  
  
 
|-  
 
|-  
Line 423: Line 423:
 
|-  
 
|-  
 
| 10:29
 
| 10:29
−
| Now enter 10 as '''cgpa''', '''CS''' as '''deptid''' and save the record.  
+
| Now enter 10 as '''cgpa''', '''CS''' as '''deptid''' and '''save''' the '''record'''.  
  
 
The record is successfully saved.  
 
The record is successfully saved.  
Line 437: Line 437:
 
|-  
 
|-  
 
| 10:50
 
| 10:50
−
| Click on the '''students''' node and then refresh.  
+
| Click on the '''students''' node and then '''refresh'''.  
  
 
|-  
 
|-  
Line 447: Line 447:
 
|-  
 
|-  
 
|11:04
 
|11:04
−
| We can add '''constraints '''while creating a '''table '''if the '''table '''structure is properly designed initially.  
+
| We can add '''constraints '''while creating a table if the table structure is properly designed initially.  
  
 
|-  
 
|-  
 
| 11:11
 
| 11:11
−
| With this we come to the end of this tutorial.  
+
| With this, we come to the end of this tutorial.  
  
 
Let us summarize.  
 
Let us summarize.  
Line 459: Line 459:
 
| In this tutorial, we have learnt about  
 
| In this tutorial, we have learnt about  
 
   
 
   
−
'''Foreign key constraint'''  
+
'''Foreign key constraint''',
  
−
'''Alter table '''command  
+
'''Alter table '''command,
  
 
|-  
 
|-  
 
| 11:23
 
| 11:23
−
| How to add a '''foreign key '''and '''Check constraints'''  
+
| how to add a '''foreign key '''and '''Check constraints'''.
  
 
|-  
 
|-  
 
|11:28
 
|11:28
−
| As an Assignment, Alter the '''students''' table to add a '''NOT NULL constraint''' to the column '''city'''  
+
| As an Assignment, alter the '''students''' table to add a '''NOT NULL constraint''' to the column '''city'''.
  
 
|-  
 
|-  
 
| 11:35
 
| 11:35
−
|  Insert a record and leave the '''city''' column empty  
+
|  Insert a record and leave the '''city''' column empty.
  
 
Save the record. Note the error and resolve it.  
 
Save the record. Note the error and resolve it.  
Line 479: Line 479:
 
|-  
 
|-  
 
| 11:44
 
| 11:44
−
| The video at the following link summarises the Spoken Tutorial project.  
+
| The video at the following link summarizes the '''Spoken Tutorial''' project.  
  
 
Please download and watch it.  
 
Please download and watch it.  
Line 485: Line 485:
 
|-  
 
|-  
 
| 11:52
 
| 11:52
−
| The''' Spoken Tutorial Project''' Team
+
| The''' Spoken Tutorial Project''' team conducts workshops gives certificates.
−
 
+
−
conducts workshops gives certificates  
+
  
 
For more details, please write to us.  
 
For more details, please write to us.  
Line 497: Line 495:
 
|-  
 
|-  
 
| 12:04
 
| 12:04
−
| Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.  
+
| Spoken Tutorial project is funded by '''NMEICT, MHRD''', Government of India.  
  
−
For More information on this mission is available at this link.  
+
More information on this mission is available at this link.  
  
 
|-  
 
|-  
 
| 12:15
 
| 12:15
−
| This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.  
+
| This is Nirmala Venkat from '''IIT Bombay''', signing off. Thanks for watching.  
  
 
|}
 
|}

Latest revision as of 22:50, 5 February 2019

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

Retrieved from "https://script.spoken-tutorial.org/index.php?title=RDBMS-PostgreSQL/C2/Foreign-key-Constraint/English-timed&oldid=45704"