In Part 3 of this video series, we have seen how to create tables (tblPerson and tblGender) and enforce primary and foreign key constraints. In Part 4, we have learnt adding a default constraint. Please watch Parts 3 and 4, before continuing with this session.
In this video, we will learn about Cascading referential integrity constraint
Cascading referential integrity constraint allows to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys points.
For example, consider the 2 tables shown below. If you delete row with ID = 1 from tblGender table, then row with ID = 3 from tblPerson table becomes an orphan record. You will not be able to tell the Gender for this row. So, Cascading referential integrity constraint can be used to define actions Microsoft SQL Server should take when this happens. By default, we get an error and the DELETE or UPDATE statement is rolled back.
However, you have the following options when setting up Cascading referential integrity constraint
1. No Action: This is the default behaviour. No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.
2. Cascade: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.
3. Set NULL: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.
4. Set Default: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.
In this video, we will learn about Cascading referential integrity constraint
Cascading referential integrity constraint allows to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys points.
For example, consider the 2 tables shown below. If you delete row with ID = 1 from tblGender table, then row with ID = 3 from tblPerson table becomes an orphan record. You will not be able to tell the Gender for this row. So, Cascading referential integrity constraint can be used to define actions Microsoft SQL Server should take when this happens. By default, we get an error and the DELETE or UPDATE statement is rolled back.
However, you have the following options when setting up Cascading referential integrity constraint
1. No Action: This is the default behaviour. No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.
2. Cascade: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.
3. Set NULL: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.
4. Set Default: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.
Hi Venkat,
ReplyDeleteI have two queries here :
1.what will happen if i have set the action to Default. but i don't have any default constraint set for a particular column.
2. Will the types 2,3 and 4 work for truncate query.
Thanks,
Rohan
Hi Rohan,
ReplyDelete1- if you set the action to default and you don't have default constraint - you will get an error (The statement has been terminated.)
2- if you try to truncate you will get the below error
Cannot truncate table 'gender' because it is being referenced by a FOREIGN KEY constraint.
Thanks,
Hi Hameed, thank you very much for answering Rohan's question. Really appreciate your time. Very few people like you take time to help others. May god bless you.
DeleteHameed & Venkat,
DeleteThanks a lot...
--Rohan
can you please explain the term truncate query????
ReplyDeleteTRUNCATE removes all rows from a table. The operation cannot be rolled back.
DeleteHi Meenakshi.. look at the below code which clears your doubt.. I given the syntax for Truncate statement
DeleteFirst Create the table..
create table Test
(
Id int,
Name varchar(20),
Gender varchar(20)
)
Now insert the values
insert into Test values(1,'Rajendra','Male')
insert into Test values(2,'Vani Siri','Female')
insert into Test values(3,'Mohan Reddy','Male')
insert into Test values(4,'Ragini','Female')
Now execute the below statement..
select * from Test
The Output is :
Id Name Gender
----------- -------------------- --------------------
1 Rajendra Male
2 Vani Siri Female
3 Mohan Reddy Male
4 Ragini Female
(4 row(s) affected)
Now execute the Truncate query..
Truncate table Test
Now look at output:
Id Name Gender
----------- -------------------- --------------------
(0 row(s) affected)
I hope ur doubt got cleared...
Hi Venkat, Can we do this cascading through writing query ?
ReplyDeleteHi chandrakant Kumar,
ReplyDeleteYes, You can write a query for cascading, but you can not alter the constraint,so if your table already have a foreign key and you want to set cascade, then you have to first drop that constraint and then add foreign key with cascade, below is the code.
ALTER TABLE tblemployee ADD CONSTRAINT FK_OrderDetails_Orders
FOREIGN KEY(GenderID)
REFERENCES tblgender(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
Venkat Sir, Please correct me if i am wrong.
Thanks and Regards,
Akshey Goyal
hii
ReplyDeleteif i create two foreign keys on the same column.what occurs if collision happens ??
No. You cannot have more than one foreign keys on same column. Even if it was possible how is it supposed to know which table to point for the key value.
DeleteHi,
ReplyDeleteHaving issues in Set Default.
>Please let me know if default value can be added to primary key.
>How a default value is assigned when there is only column in the table. eg
Create Table tbParent
(
parentID int Primary Key
)
> I am unable to set Default value in the child table when the data in primary table is deleted.
Primary Key having default value doesn't make sense. What if you you don't provide value to PK for two records, Default constraint will kick in and try to give them default and two PK records will have same value contradicting PK constraint itself. So PK cant have default constraint.
DeleteIn order for the cascade default action to take, the FK column should have Default constraint.
Hi kudvenkat Sir,
ReplyDeleteI have small doubt,can you possible cascading rule using in table on delete one row in parent table,i want to default value in child table.
please tell me anybody.
Yes, it's possible Kiran. It is also explain in video.
ReplyDeleteFor Eg. IF I want to delete a row from gender table(Which is parent table), then respectively rows in child table(person table ) have settled
default value(3) 'UNKNOWN'.