Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Cascading referential integrity constraint - Part 5

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.

15 comments:

  1. Hi Venkat,

    I 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

    ReplyDelete
  2. Hi Rohan,
    1- 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,

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. Hameed & Venkat,

      Thanks a lot...

      --Rohan

      Delete
  3. can you please explain the term truncate query????

    ReplyDelete
    Replies
    1. TRUNCATE removes all rows from a table. The operation cannot be rolled back.

      Delete
    2. Hi Meenakshi.. look at the below code which clears your doubt.. I given the syntax for Truncate statement

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

      Delete
  4. Hi Venkat, Can we do this cascading through writing query ?

    ReplyDelete
  5. Hi chandrakant Kumar,

    Yes, 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

    ReplyDelete
  6. hii
    if i create two foreign keys on the same column.what occurs if collision happens ??

    ReplyDelete
    Replies
    1. 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.

      Delete
  7. Hi,

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

    ReplyDelete
    Replies
    1. 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.
      In order for the cascade default action to take, the FK column should have Default constraint.

      Delete
  8. Hi kudvenkat Sir,
    I 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.

    ReplyDelete
  9. Yes, it's possible Kiran. It is also explain in video.
    For 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'.

    ReplyDelete

It would be great if you can help share these free resources