In order to demonstrate the Cascade Referential Constraint we need to refer below two table which are 1 to many relationship. We have table OrderDetails which contains all order related information and another table Product table which will contain product information. Below are the structure and records present in both the table:

OrderDetails table refers the ProductId column which is a foreign key in Order Details table.

If the business team want the records of the Product Description of the particular OrderId. We will certainly check the Product Id mentioned in OrderDetails table and then search the same ProductId in the Product table.

What if we have deleted 101 product Nokia N91 from our Inventory. So than OrderId 2 will have no meaning as there are no corresponding records present in Product table for the same. By default SQL server doesn’t allow this kind of delete.

If we go and check our foreign key

We have two options in Insert and Update specification as shown above:

1. Delete Rule
   
2. Update Rule
   

Options:

• No Action: By default both the rules are set to No Action which means if the user wants to delete the Foreign key records and those Id are being referenced in the foreign key table, it will not allow deleting the same.
  
• Cascade: If an attempt is made to delete or update the row with a key referenced by the foreign key in another table, all rows containing those foreign key are also deleted or updated.
  
• Set Null: If an attempt is made to delete or update the row with a key referenced by the foreign key in existing rows, all rows containing the foreign key are set to null.
  
• Set Default: If an attempt is made to delete or update the foreign key rows just set them to default values.
  

Cascade Example:

Configuring Cascade deletes in relationship option:

Once we are done with cascade delete let’s try to delete the product from the Product table and check the OrderDetails table as well.

As soon as I delete the product from the product table, it also deletes the corresponding referenced foreign key in Order Details tables as shown below.

Set Null Example:

So now when I try to delete the product from the product table as shown below the foreign key row will be set to null.

Set Default Example:

Before going ahead let’s talk about Default Constraints. Default constraints are constraints which are set the default when no value is passed for the desired Column.

In our case table, OrderDetails has ProductId as Nullable which means if we don’t pass any value for the ProductId it will take by default as null.

insert
into
OrderDetails(quantity,price)
values(10,22220)

What if I want to restrict that if any order comes without productId I will consider it as Unknown Product. So I will first create a new product with description as “Unknown”

Now instead of Storing null in ProductId, we will set ProductId of Unknown. So in order to enabled lets create a default Constraint.

Using Sql:

ALTER
TABLE
OrderDetails

ADD
CONSTRAINT
CS_ORDERDETAILS_PRODUCTID

DEFAULT(105) FOR
PRODUCTID

Adding new column with default constraint to a table

ALTER
TABLE
CUST

ADD
GENDERID
INT
NOT
NULL

CONSTRAINT
CS_GENDER_GENDER
DEFAULT (10)

Drop Constraint

ALTER
TABLE
CUST

DROP
CONSTRAINT
CS_GENDER_GENDER

So now we have set the default constraint to the order details table, Now insert the order in the orderdetails table without ProductId as shown below:

INSERT
INTO
ORDERDETAILS
(QUANTITY,PRICE)
VALUES(3,40000)

After insertion of order without ProductId. Let now test the referential integrity constraint for SetDefault:

Now if I delete the product from the product table which is being referenced in the Order details table, it will update to the default productId as shown below:

I Hope this learning will be helpful.