Foreign Key with Cascade Delete in SQL Server

This article explains how to use Foreign Key with Cascade Delete in SQL Server with syntax and examples.

What is foreign key with Cascade DELETE in SQL Server?

Foreign key with Cascade Delete means that if a record in the parent table is deleted, the corresponding record in the child table will also be automatically deleted.

Foreign keys with automatic deletion constraints are created using the CREATE TABLE or ALTER TABLE statement.

Create foreign keys with automatic deletion with the CREATE TABLE command

Syntax

 CREATE T ABLE bang_con 
 ( 
  cot1 kieudulieu [ NULL | NOT NULL ], 
  cot2 kieudulieu [ NULL | NOT NULL ], 
  … 
 
  CONSTRAINT fk_ten 
  FOREIGN KEY (cot_con1, cot_con2, … cot_con_n) 
  REFERENCES bang_me (cot_me1, cot_me2, … cot_me_n) 
  ON DELETE CASCADE 
  [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
 ); 

bang_con

The name of the child table you want to create.

cot1, cot2

The column you want to create in the table. Each column has 1 data type, must be specified to contain NULL or NOT NULL values, otherwise default is NULL.

Data types in SQL Server

fk_ten

The name of the foreign key constraint to create.

cot_con1, cot_con2, . cot_con_n

The column in bang_con wants to refer to the primary key in bang_me.

bang_me

The name of the parent table contains the primary key used in bang_con.

cot_me1, cot_me2, . cot_me_n

The column that makes up the primary key in bang_me. Foreign keys will create constraints between data and columns cot_con1, cot_con2, . cot_con_n in bang_con.

ON DELETE CASCADE

Specifying the child data will be deleted when the parent data is deleted.

ON UPDATE

Option. Indicates what to do with the child data when the parent data is updated. There are options NO ACTION, CASCADE, SET NULL and SET DEFAULT.

NO ACTION

Use with ON DELETE or ON UPDATE, ie do nothing with child data when parent data is deleted or updated.

CASCADE

Use with ON DELETE or ON UPDATE, meaning that the child data is deleted or updated when the parent data is deleted or updated.

SET NULL

Use with ON DELETE or ON UPDATE, meaning that the child data is set to NULL when the parent data is deleted or updated.

SET DEFAULT

Use with ON DELETE or ON UPDATE, meaning that the child data is set to the default value when the parent data is deleted or updated.

For example

 CREATE T ABLE sanpham 
 ( id_sanpham INT PRIMARY KEY, 
  ten_sanpham VARCHAR(50) NOT NULL, 
  phan_loai VARCHAR(25) 
 ); 
 
 CREATE TABLE hangtonkho 
 ( id_hangtonkho INT PRIMARY KEY, 
  id_sanpham INT NOT NULL, 
  soluong INT, 
  luong_toithieu INT, 
  luong_toida INT, 
  CONSTRAINT fk_htk_id_sanpham 
  FOREIGN KEY (id_sanpham) 
  REFERENCES sanpham (id_sanpham) 
  ON DELETE CASCADE 
 ); 

In this example, we created the parent table, sanpham, with the primary key including the information field id_sanpham. Then there is a child table named hangtonkho with a foreign key with deletion constraint. The CREATE TABLE statement creates a foreign key on the hangtonkho table named fk_htk_id_sanpham. The foreign key forms the relationship between the id_sanpham column in the hangtonkho table and id_sanpham in the sanpham table.

This foreign key is assigned ON DELETE CASCADE tells SQL Server that it must delete the corresponding record in the child table when the data in the parent table is deleted. In this example, the id_sanpham value is deleted from the sanpham table, the corresponding record in this hangtonkho user id_sanpham table will also be deleted.

Create a foreign key with the delete constraint with the ALTER TABLE statement

Syntax

 ALTER TAB LE bang_con 
 ADD CONSTRAINT fk_ten 
  FOREIGN KEY (cot_con1, cot_con2, … cot_con_n) 
  REFERENCE S bang_me (cot_me1, cot_me2, . cot_me_n) 
 ON DELETE CASCADE; 

bang_con

The name of the child table you want to create.

fk_ten

The name of the foreign key constraint to create.

cot_con1, cot_con2, . cot_con_n

The column in bang_con wants to refer to the primary key in bang_me.

bang_me

The name of the parent table contains the primary key used in bang_con.

cot_me1, cot_me2, . cot_me_n

The column that makes up the primary key in bang_me. Foreign keys will create constraints between data and columns cot_con1, cot_con2, . cot_con_n in bang_con.

ON DELETE CASCADE

Indicates that the data in the child table will be deleted when the data in the parent table is deleted.

For example

 ALTER TABLE hangton kho 
 ADD CONSTRAINT fk_htk_id_sanpham 
  FOREIGN KEY (id_sanpham) 
  REFERENCES sanpham (id_sanpham) 
  ON DELETE CASCAD E; 

In this foreign key example, the hangtonkho sub table is created with a foreign key named fk_htk_id_sanpham, referring to the parent table sanpham based on id_sanpham.

Because of the ON DELETE CASCADE designation, if the id_sanpham value is deleted from the parent table, the corresponding record in the child table will also be deleted.

Last lesson: Foreign Key foreign key in SQL Server

The following article: Foreign Key (Set Null) foreign key in SQL Server