Mysql 简明教程

MySQL - On Delete Cascade

The MySQL ON DELETE CASCADE Constraint

MySQL ON DELETE CASCADE 约束确保当父表中的行被删除时，子表中的所有相关行也会自动删除。此约束有助于维护通过外键关系连接的两个表之间的引用完整性。

The MySQL ON DELETE CASCADE constraint ensures that when a row in the parent table is deleted, all related rows in the child table are automatically deleted as well. This constraint helps maintain referential integrity between two tables that are connected through a foreign key relationship.

如果我们不使用此约束，则数据库服务器默认情况下会阻止我们删除表中的数据（如果它被其他表引用）。

If we do not use this constraint, the database server will, by default, prevent us from deleting data in a table if it is referenced by other tables.

Example

Creating the Parent Table −

首先，让我们使用以下查询创建名称为 PERSONS 的父表——

First, let us create the parent table with the name PERSONS using the following query −

CREATE TABLE PERSONS(
   P_ID int primary key,
   P_NAME varchar(40),
   P_AGE int
);

现在，让我们使用INSERT语句将一些值插入到上面创建的表中，如下所示——

Now, let us insert some values into the above created table using the INSERT statement as shown below −

INSERT INTO PERSONS VALUES
(1, "Priya", 29),
(2, "Sarah", 20),
(3, "Varun", 26),
(4, "Dev", 25),
(5, "Ram", 31),
(6, "Aarohi", 34);

获得的PERSONS表如下所示——

The PERSONS table obtained is as shown below −

Creating the Child Table −

现在，让我们创建一个名为 Films_watched 的子表，其中包含 ON DELETE CASCADE 约束。在这个表中，P_ID 列是外键，引用 Persons 表中的 P_ID 列 −

Now, let us create a child table named Films_watched with the ON DELETE CASCADE constraint. In this table, the P_ID column is a foreign key referencing the P_ID column in the Persons table −

CREATE TABLE Films_watched (
   P_ID INT,
   F_NO INT,
   F_NAME varchar(40),
   PRIMARY KEY(P_ID,F_NO),
   FOREIGN KEY(P_ID)
   REFERENCES PERSONS(P_ID)
   ON DELETE CASCADE
);

现在，我们向 Films_watched 表中插入行 −

Now, we are inserting rows into the Films_watched table −

INSERT INTO Films_watched VALUES
(1, 130, "RRR"),
(2, 131, "Bahubali"),
(3, 132, "Pushpa"),
(3, 133, "KGF"),
(3, 134, "Salaar"),
(6, 135, "Karthikeya");

生成的 Films_watched 表如下 −

The Films_watched table produced is as follows −

Deleting a Record from the Parent Table −

正如我们在上面的表中看到的，P_ID = 3 的人已经观看了三部电影。在此，我们从 PERSONS（父）表中删除 P_ID = 3 的人 −

As we can see in the above table, we have three films that has been watched by the person with P_ID = 3. Here, we are deleting the person with P_ID = 3 from the PERSONS (parent) table −

DELETE FROM PERSONS WHERE P_ID = 3;

以下是所获得的输出 −

Following is the output obtained −

Query OK, 1 row affected (0.01 sec)

删除后，让我们检查 Persons 和 Films_watched 表中的数据。

After this deletion, let us check the data in both the Persons and Films_watched tables.

Querying Data from the Persons Table −

要查看 Persons 表中的剩余记录，请使用以下 SELECT 查询 −

To see the remaining records in the Persons table, use the following SELECT query −

SELECT * FROM PERSONS;

我们可以在下面的表中看到，P_ID = 3 的行已被删除 −

We can see in the table below, the row with P_ID = 3 is deleted −

Querying Data from the Films_watched Table −

最后，你可以检查 Films_watched 表中的数据 −

Lastly, you can check the data in the Films_watched table −

SELECT * FROM Films_watched;

Output

我们可以在下面的输出中看到，所有与 P_ID = 3 相关的记录都已自动删除 −

We can see in the output below that all related records with P_ID = 3 have been automatically deleted −