SQL Foreign Key

SQL Foreign Key with Cascade Option

The SQL FOREIGN KEY constraint is used to link two tables together. It ensures that the value in one table matches a value in another table, enforcing referential integrity between the tables.

We can modify the foreign key constraint to include options such as ON DELETE CASCADE, which will automatically delete rows in the dependent table when a row in the parent table is deleted.

Tables Used

Products Table:

ProductID	ProductName
1	Laptop
2	Phone

Orders Table:

OrderID	ProductID	Quantity
1	1	2
2	2	1
3	1	5

Example with Cascade Option

Let's modify the foreign key to delete all orders associated with a product if the product is deleted.

Code Example


-- Create the Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100)
);

-- Create the Orders table with ON DELETE CASCADE
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE
);

-- Insert data into Products table
INSERT INTO Products (ProductID, ProductName) VALUES (1, 'Laptop');
INSERT INTO Products (ProductID, ProductName) VALUES (2, 'Phone');

-- Insert data into Orders table
INSERT INTO Orders (OrderID, ProductID, Quantity) VALUES (1, 1, 2);
INSERT INTO Orders (OrderID, ProductID, Quantity) VALUES (2, 2, 1);
INSERT INTO Orders (OrderID, ProductID, Quantity) VALUES (3, 1, 5);

-- View the Products and Orders tables
SELECT * FROM Products;
SELECT * FROM Orders;

-- Now, delete the Laptop from Products table
DELETE FROM Products WHERE ProductID = 1;

-- View the resulting Orders table
SELECT * FROM Orders;

Output Tables

Products Table (Before Deletion):

ProductID	ProductName
1	Laptop
2	Phone

Orders Table (Before Deletion):

OrderID	ProductID	Quantity
1	1	2
2	2	1
3	1	5

After Deleting Product 'Laptop' (ProductID = 1)

After deleting the `Laptop` product from the Products table, we can see that the associated orders in the Orders table have also been deleted automatically, thanks to the ON DELETE CASCADE option.

Orders Table (After Deletion):

OrderID	ProductID	Quantity
2	2	1

Explanation

- The ON DELETE CASCADE option ensures that when a product is deleted from the Products table, all orders associated with that product are automatically deleted from the Orders table.
- In this example, after deleting the `Laptop` (ProductID = 1) from the Products table, the orders that referenced this product (OrderID 1 and OrderID 3) were deleted from the Orders table.
- The remaining order for `Phone` (ProductID = 2) is unaffected by the deletion.