Cascade SQL example

You all know, we can't delete record or records of master table if it references to child table because you know primary and foreign key rule, if you did't know about these rule you call see my last article  in given below link

Referential Integrity Constraint

If you know these rules then I think you faced the given below "Note" problems

Note

•  We can’t delete or update any record from the Master table if at all it has corresponding child records in the child table.
•  Because by default we can’t delete a record from the Master table when it has corresponding child records. To delete them we are provided with cascading rules.

Cascading Rules :

Cascading rules are of 2 types, they are: -

(1) Delete Rules: -

• On Delete No Action (Default)
• On Delete cascade
• On Delete Set Null
• On Delete Set Default
   

(2) Update Rules: -

• On Update No Action (Default)
• On Update cascade
• On Update Set Null
• On Update Set Default

All these rules has to be applied while creating the child tables. We can choose one rule from each set & apply on the child tables.

On Delete No Action and On Update No Action:

These are the default rule that gets applied on the child table. When these are applied we can’t delete or update a record in a Master table if at all it has corresponding child records.

On Delete Cascade and On Update Cascade:

If these rules are applied on the child table it will allow us to delete a record from the Master table, so that corresponding child records get deleted as well as allow us to update a record in the Master table, so that the corresponding child records foreign key also gets updated.

CREATE TABLE [dbo].[Employee](      [EmpID] [int] IDENTITY(1,1) NOT NULL primary key,      [EmpName] [varchar](150) NOT NULL,      [RefDeptID] [int] NULL,      CONSTRAINT [dno_fk] FOREIGN KEY([RefDeptID]) REFERENCES Department ([DeptID])     ON UPDATE CASCADE ON DELETE CASCADE)

On Delete Set Null and On Update Set Null:

If these rules are applied while creating the child tables it will allow us to delete as well as update a record from the master table so that the corresponding child records foreign key value changes to NULL provides the column is not imposed with a not NULL constraint on it. It is newly added in SQL Server.

CREATE TABLE [dbo].[Employee](      [EmpID] [int] IDENTITY(1,1) NOT NULL primary key,      [EmpName] [varchar](150) NOT NULL,      [RefDeptID] [int] NULL,      CONSTRAINT [dno_fk] FOREIGN KEY([RefDeptID]) REFERENCES Department ([DeptID])    On Delete Set Null On Update Set Null)

On Delete Set Default and On Update Set Default:

If these rules are applied on the child table while creating it will allow us to delete as well as update a record in a master table do that changes to the default value specified for the foreign key column. If no default value is specified Null is taken as default which is equal to the On Delete Set Null.

CREATE TABLE [dbo].[Employee](

     [EmpID] [int] IDENTITY(1,1) NOT NULL primary key,

     [EmpName] [varchar](150) NOT NULL,

     [RefDeptID] [int] NULL,

     CONSTRAINT [dno_fk] FOREIGN KEY([RefDeptID]) REFERENCES Department ([DeptID])

   On Delete Set Default On Update Set Default)