Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Cascading Referential Actions

Explore how cascading referential actions ensure database consistency in MySQL. Learn to implement ON UPDATE CASCADE and ON DELETE CASCADE rules, test their behavior through SQL queries, and understand alternate methods like triggers or views to maintain data integrity when parent records change or are deleted.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
CREATE TABLE CustomerAddresses (
    AddressID     INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID    INT NOT NULL,
    AddressLine1  VARCHAR(100) NOT NULL,
    AddressLine2  VARCHAR(100),
    City          VARCHAR(50),
    PostalCode    VARCHAR(20),
    Country       VARCHAR(50),
    CONSTRAINT fk_customeraddresses_customer
        FOREIGN KEY (CustomerID)
        REFERENCES Customers(CustomerID)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
/* Verification code for ON UPDATE CASCADE and ON DELETE CASCADE.
   Run these statements after the table has been created successfully. */
-- Insert a customer
INSERT INTO Customers (CustomerName, Email)
VALUES ('John Smith', 'john@example.com');
-- Capture the generated CustomerID
SET @old_id := LAST_INSERT_ID();
-- Insert an address for this customer
INSERT INTO CustomerAddresses (CustomerID, AddressLine1, City, Country)
VALUES (@old_id, '123 Main St', 'Boston', 'USA');
-- Update CustomerID to simulate a migration
UPDATE Customers
SET    CustomerID = @old_id + 100
WHERE  CustomerID = @old_id;
-- Verify that the address followed the new ID
SELECT *
FROM   CustomerAddresses
WHERE  CustomerID = @old_id + 100;
/* Uncomment the following block to verify delete; 
   Don't forget to remove the previous query */
/*-- Delete the customer to test ON DELETE CASCADE
DELETE FROM Customers
WHERE  CustomerID = @old_id + 100;
-- Verify that the address was also removed
SELECT *
FROM   CustomerAddresses
WHERE  CustomerID = @old_id + 100; */

Explanation

The explanation of the solution code is given below:

  • Lines 1–14: Create the CustomerAddresses table with columns for address details and a foreign key column CustomerID.

    • Lines 9–13: Define the foreign key constraint fk_customeraddresses_customer referencing Customers(CustomerID) with ON UPDATE CASCADE and ON DELETE CASCADE, ensuring related rows follow parent changes.

  • Lines 20–21: Insert a customer record to generate a new CustomerID for testing cascade behavior.

  • Line 24: Store the inserted customer’s CustomerID in the session variable @old_id for reuse.

  • Lines 27–28: Insert a matching address into CustomerAddresses using the captured ID.

  • Lines 31–33: Update the parent CustomerID to simulate a migration scenario. Because of ON UPDATE CASCADE, all dependent rows update ...