Sandhya.np14 at 17:20, 5 February 2019

2019-02-05T17:20:32Z

PoojaMoolya: 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..."

2019-01-30T11:05:49Z

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..."

New page

{| border=1
| '''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 summarises 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.

For More information on this mission is available at this link.

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

|}

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

Sandhya.np14 at 17:20, 5 February 2019

PoojaMoolya: 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..."