SQL delete simultaneously deletes records associated with multiple tables

SQL delete simultaneously deletes records associated with multiple tables

Sqlserver supports cascade update and deletion.
Oracle only supports cascading Deletion

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)

 

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

000
(Please comment on the article.) Delete From UserInfo Where UserId = 1 to Delete the UserId = 1 in the UserInfo and UserDetails tables.

Let's take a look at how to delete multiple tables in the MySql database tutorial,

Sometimes we can use delete directly to delete

Delete Syntax:
DELETE [LOW_PRIORITY] [QUICK] FROM table_name
[WHERE where_definition]
[Order by...]
[LIMIT rows]

Or

DELETE [LOW_PRIORITY] [QUICK] table_name [. *] [, table_name [. *]...]
FROM table-references
[WHERE where_definition]

Or

DELETE [LOW_PRIORITY] [QUICK]
FROM table_name [. *] [, table_name [. *]...]
USING table-references
[WHERE where_definition]

The sample code is as follows:

1. delete data from a table
Delete from department where name = 'asset management ';

2. delete data from two tables
Delete employee, employeeSkills
From employee, employeeSkills, department
Where employee. employeeID = employeeSkills. employeeID
And employee. interval mentid = department. interval mentid
And department. name = 'finance ';

3. Delete the data in the two tables and use the using syntax.
Delete from employee, employeeSkills
Using employee, employeeSkills, department
Where employee. employeeID = employeeSkills. employeeID
And employee. interval mentid = department. interval mentid
And department. name = 'finance ';