SQL Server cascading deletion: When you delete a primary table, the content of the secondary table (foreign key constraint) is automatically deleted.
The SQL Server cascade operation to delete a row that contains the primary key value. This value is referenced by a foreign key column in an existing row of another table. In cascading deletion, all rows whose foreign key value references the deleted primary key value are also deleted.
For example:
Create Database temp
Go
Use temp
Go
Create Table userinfo
(
Userid int identity (1, 1) primary key,
Username varchar (20), -- User Name
Password varchar (20) not null -- Password
)
Create Table userdetails
(
Id int identity (1, 1) primary key,
Name varchar (50) not null, -- real name
Userid int,
Foreign key (userid) References userinfo (userid) on Delete Cascade
)
Insert userinfo values ('ly ', 'jeff ')
Insert userinfo values ('wzq', 'wzqwzq ')
Insert userinfo values ('lg ', 'lglg ')
Insert userdetails values ('Li si', 1)
Insert userdetails values ('wang wu', 2)
Insert userdetails values ('Liu liu', 3)
Delete from userinfo where userid = 1 to delete the userid = 1 in the userinfo and userdetails tables.
Insert userinfo values ('ly ', 'jeff ')
Insert userinfo values ('wzq', 'wzqwzq ')
Insert userinfo values ('lg ', 'lglg ')
Insert userdetails values ('Li si', 1)
Insert userdetails values ('wang wu', 2)
Insert userdetails values ('Liu liu', 3)
Delete from userinfo where userid = 1 to delete the userid = 1 in the userinfo and userdetails tables.