ON DELETE CASCADE

ON DELETE CASCADE
ON UPDATE CASCADE

Cascading allows one to delete or update the child table once the master table gets affected.

SQL Server has CASCADE options for delete and update.

• [ ON DELETE { CASCADE | NO ACTION } ]
• [ ON UPDATE { CASCADE | NO ACTION } ]

• ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.
• ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables

Note:
CASCADE cannot be applied to a timestamp column.

Here is the sample for Cascading

CREATE TABLE staff
(staff_id INT NOT NULL CONSTRAINT p_staff_id PRIMARY KEY,
staff_name VARCHAR(50) NOT NULL,
staff_role VARCHAR(10) NULL )

CREATE TABLE job
(job_id INT NOT NULL CONSTRAINT p_job_id PRIMARY KEY,
job_name VARCHAR(15) NOT NULL )

CREATE TABLE staff_job
(staff_id INT NOT NULL,
job_id INT NOT NULL,
comments VARCHAR(200) NULL,
created_date DATETIME NULL,
CONSTRAINT p_staff_job PRIMARY KEY(staff_id, job_id),
CONSTRAINT f_staff_job1 FOREIGN KEY(staff_id) REFERENCES staff(staff_id)ON DELETE CASCADE,
CONSTRAINT f_staff_job2 FOREIGN KEY(job_id) REFERENCES job(job_id)ON UPDATE CASCADE
)


insert into staff values(1,'Staff1' ,'DBA')
insert into staff values(2,'Staff2' ,'DBA')
insert into staff values(3,'staff3','UI')


insert into job values(1,'Backup' )
insert into job values(2,'Restore' )

insert into staff_job values(1,1,'created a backup',getdate())
insert into staff_job values(2,2,'Restored the Backup',getdate())
insert into staff_job values(3,2,'workdone',getdate())

select * from staff
select * from job
select * from staff_job




here we are going to update and delete the master record.

update job set job_id = 3 where job_name = 'Restore'

delete from staff where staff_id = 1
GO

select * from staff
select * from job
select * from staff_job





GO
drop table staff_job
drop table job
drop table staff
GO