View source for 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'''

{| style="border-spacing:0;"
| style="background-color:#c0c0c0;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Visual Cue'''
| style="background-color:#c0c0c0;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Narration'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 1:'''

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Welcome to the''' Spoken Tutorial''' on''' Foreign key constraint.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 2:'''

'''Learning Objectives'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| In this tutorial we will learn about

* '''Foreign key constraint'''
* '''Alter table''' command
* How to add a '''foreign key''' and
* Check '''constraint'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 3:'''

'''System requirement'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| To record this tutorial, I am using

* '''Ubuntu Linux 14.04''' operating system
* '''PostgreSQL 9.3.x'''
* '''pgAdmin 1.18'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 4:'''

'''Pre-requisites'''
* Basic understanding of database and tables 
* For more details, refer to the '''RDBMS – PostgreSQL''' series on [http://spoken-tutorial.org/ http://spoken-tutorial.org/]

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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 
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Show image of the students table
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| This is the '''students''' table which we created.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Show image of the students table along with dept details ( students-dept.png)
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| I want to add details such as '''department name '''and '''department head '''for each student as shown here.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Point to the repeating column
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| As we can see, the '''department name '''and '''department head '''data is repeated.
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Show image of the two tables department and student.


| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| What is a '''foreign key'''?

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 5(a)'''

'''Foreign key:'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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'''.
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 5(b)'''

'''Foreign key: '''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let’s see an example of '''foreign key''' link between '''students''' and '''department tables'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Show the image of students and department table (student-dept-link.p ng) 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on the SQL editor icon
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Open the '''SQL editor window''' in the '''pgAdmin'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Now let us first create the '''parent table department''' i.e the '''referenced table. '''
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''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)

)
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code.

'''Departmenttid''' is the '''primary key '''which uniquely identifies each department.

The '''parent table department '''is ready to execute.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let's insert some data to the '''department table.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on the clear icon
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Clear the editor window.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''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'); '''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code and execute the query.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Show the inserted data'''

'''Type,'''

'''Select * from department'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Switch back to the '''pgAdmin''' main screen.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Point to the students table'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| To do so, we need to use the '''Alter table''' command.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''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

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 7:'''

'''Syntax: Alter table'''

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Switch back to the '''pgadmin SQL editor''' window 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let’s switch back to the '''pgadmin SQL editor''' window for the demonstration.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Type the code '''

'''ALTER TABLE students ADD deptid varchar(5) '''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us add a new column by the name '''deptid''' to the '''students table'''.

Type the code and execute.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Switch to pgAdmin main screen
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us check this.

Go to the '''pgAdmin''' main screen. 

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Right click on '''students''' column node and select '''Refresh'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Right-click on '''students''' column node and select '''Refresh''' to update the changes.

We can see the '''deptid''' column.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Now let us add the '''foreign key constraint'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''ALTER TABLE students ADD foreign key(deptid) references department(departmentid)'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on execute query button
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us now execute the query.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Go to the '''pgAdmin''' main screen. Click on the '''students''' table.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Go to the '''pgAdmin''' main screen. 

Right click on the '''students table '''node and click '''refresh'''.
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Highlight the script according to narration
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Right click on students node>> View data >>View All rows.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Now right-click on '''students''' node, then on '''View data''' and then on '''View''' '''All rows.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Point to the deptdid column
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Deptid''' column is empty. Let us add department details to each student as shown.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Type 'MEC''''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let me type ''''MEC'''' for '''Mechanical department '''for the student '''Pooja''' and press '''Enter'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Point to the error message
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| We can see a error message of '''“foreign key violation”.''' 

It says ''''deptid='mec' '''is not present in '''table department.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Highlight '''departmentid '''>> Click on '''OK'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type 'MT' in the deptid column.

Click on the save icon.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Now, I'll enter ''''MT'''' as '''department id''' and save the record.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Enter departmentid for other records as well.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Enter a valid '''departmentid''' for other students also as shown.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Open the department and students table
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let me open '''department''' and '''students table '''in 2 different windows.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Point to the one to many records
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 8:'''

'''Advantages of using Foreign Key:'''

* Enforce the constraint 

* Controls redundancy and avoid inconsistency

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Next we will learn more about '''Alter table''' command.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''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;

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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;'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Point to the cgpa column
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Say, I want to change the '''cgpa''' column's length from 2,1 to 3,1. 

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| ALTER TABLE students ALTER cgpa type numeric(3,1)
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code as shown and then execute.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Now the '''cgpa''' column length is altered in the '''students table.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Point to the cgpa column in the SQLpane script.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let's check.

Refresh the '''students table '''and see the changes.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Next we will learn about '''constraints'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 9''':

Constraints:
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Constraints''' are the conditions or rules enforced on '''columns''' or '''tables'''.


These are used to prevent invalid data entry into the '''database.'''
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 9''':

Constraints:
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Column level '''constraints '''are applied to only one column. 

'''Table '''level constraints are applied to the whole '''table.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 10:'''

Constraints: 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Some of the commonly used constraints in '''PostgreSQL''' are:

'''NOT NULL constraint'''

'''UNIQUE constraint'''

'''Primary key constraint'''

'''Foreign key constraint'''

'''Check constraint'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Next we will see how to add a '''check constraint '''to the '''cgpa''' column.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code as shown.

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| It specifies a condition that the '''cgpa''' value should be between 0 and 10.

This restricts the user from entering invalid data.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let’s now execute the query.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Now let us insert one more record to the students table with cgpa as 22 and see what happens.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Right click on '''students''' node and select '''View Data''' and then '''Views All Rows.'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Right-click on '''students''' node, select '''View Data''' and then '''Views All Rows.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the data for each column
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| I'll enter the data as shown.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type 22 in cgpa column and press Enter.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| In the '''cgpa''' column, I'll enter 22 and press '''Enter.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Point to the error message

Click on the “'''OK'''” button.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| We can see an error message of '''check constraint violation.'''

This indicates that we have entered a invalid data.

Click on the “'''OK'''” button.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Enter '10' as cgpa.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Now enter 10 as '''cgpa''', '''CS''' as '''deptid''' and save the record.

The record is successfully saved.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us see the syntax for how to use '''constraints''' in the '''create table''' command.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| switch to the pgAdmin main screen
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Switch to the '''pgAdmin''' main screen.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on the students node
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on the '''students''' node.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Highlight the code
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Now come to the '''sql''' pane.

Here we can see the '''create table''' syntax for the '''students table''' along with the constraints.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Highlight the code
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| We can add '''constraints '''while creating a '''table '''if the '''table '''structure is properly designed initially.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| With this we come to the end of this tutorial.

Let us summarize.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 11:

Summary
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| In this tutorial, we have learnt about

* '''Foreign key constraint'''
* '''Alter table '''command
* How to add a '''foreign key '''and
* '''Check constraints'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 12:

Assignment

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 12:

About Spoken Tutorial project

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 13:

Spoken Tutorial workshops

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| The''' Spoken Tutorial Project''' Team

* conducts workshops 
* gives certificates 

For more details, please write to us.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 13:

Forum for specific questions
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| * Please post your timed queries in this forum.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 14:

Acknowledgement

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at

this link.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

|}