SQL cascade deletion of databases

Function: automatically deletes the content of a sub-table (foreign key constraint) When deleting a primary table.

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)
 
 

SQL Server supports cascade update and deletion.

Oracle only supports cascading Deletion

 
 

  
  
Alter table name
  
  
Add constraint foreign key name
  
  
Foreign key (field name) references main table name (field name)
  
  
On delete cascade
 
 

Syntax:

 
 

  
  
Foreign Key 
  
  
(column[,...n]) 
  
  
references referenced_table_name[(ref_column[,...n])] 
  
  
[on delete cascade] 
  
  
[on update cascade] 
 
 

Note:

• Column: column name
• Referenced_table_name: name of the primary key table referenced by the foreign key
• Ref_name: primary key column of the Table to be referenced by the foreign key
• On delete: deletes a cascading.
• On update: update Cascade