MySQL ON DELETE CASCADE

In the previous tutorial, we saw how to delete rows from a table using the DELETE statement. MySQL provides an easier way that allows you to automatically delete data from child tables when you delete data from the parent table using ON DELETE CASCADE.
 

Example: ON DELETE CASCADE

We will use a simple database that consists of two tables:

• The customers table stores the client data with the client’s ID, name, age and address.
• The orders table stores the orders made by clients.

 

 
 
“Clients” table
 

 
“Orders” table
 

 
The link between the “Clients” table and “Orders” table is “ClientID”.
 
 
When you delete a row from the “Clients” table, you also want to delete all rows in “Orders” table that reference the row in “Clients” table. To do this, the foreign key “ClientID”, in “Orders” table must have the ON DELETE CASCADE clause at the end, as follows:

CREATE TABLE Orders (
    OrderID int AUTO_INCREMENT PRIMARY KEY,
    OrderNbr int NOT NULL,
    Totale DECIMAL(12,2) NOT NULL,
    ClientID int,
    FOREIGN KEY (ClientID)
        REFERENCES Clients (ClientID)
        ON DELETE CASCADE
);

 

 
Now when you delete a row from “Clients” table for example the client with id = 5. Automatically, all rows from the “Orders” table that refer to the row from “Clients” table will be deleted.

DELETE FROM Clients WHERE ClientID = 5;

Now we check if the client with id = 5 has been deleted from the “Clients” and “Orders” table.
 

 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More