DELETE CASCADE AND UPDATE CASCADE rules in SQL Server foreign key
Cyrus Hardison
Posted on March 23, 2023
Cascade
When a "CASCADE" option is used, it means that when a row in the parent table is updated or deleted, the corresponding rows in the child table(s) will also be updated or deleted automatically. For example, if a row in a parent table is deleted, then all the related rows in the child table will also be deleted automatically. This ensures that there are no orphaned rows in the child table(s).
Restrict
On the other hand, when a "RESTRICT" option is used, it means that when a row in the parent table is updated or deleted, the corresponding rows in the child table(s) will not be updated or deleted automatically. The database system will prevent such changes and raise an error, unless the related rows in the child table(s) are first updated or deleted manually.
No action
When a "NO ACTION" option is used, it means that when a row in the parent table is updated or deleted, the corresponding rows in the child table(s) will not be updated or deleted automatically, just like in the case of "RESTRICT". However, unlike "RESTRICT", "NO ACTION" does not raise an error if a related row in the child table(s) exists that would violate the referential integrity constraint. Instead, it simply allows the update or deletion of the row in the parent table without affecting the child table(s).
sample code
CREATE TABLE Q_list.dbo.software_tb (
sn int IDENTITY(1,1) NOT NULL,
id AS ('so'+right('000000'+CONVERT([varchar](6),[sn]),(6))) PERSISTED NOT NULL,
name varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
sw_attributes varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
sw_license varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
version varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
description varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
vendor varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
network varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
eol_date date NULL,
eos_date date NULL,
authorized_quantity varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
server_name varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
installation_status varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
installation_environment varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
ip varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
port int NULL,
department_of_use varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
in_charge varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
remarks varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
server_id varchar(8) NOT NULL,
FOREIGN KEY (server_id) REFERENCES server_tb(id)
ON DELETE CASCADE
ON UPDATE CASCADE
Auto ID increment with sequence No.
...
sn int IDENTITY(1,1) NOT NULL,
id AS ('so'+right('000000'+CONVERT([varchar](6),[sn]),(6))) PERSISTED NOT NULL,
...
| sn | id |
|---|---|
| 1 | so000001 |
| 2 | so000002 |
Posted on March 23, 2023
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.