Blog

MySQL ON DELETE CASCADE

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table (using the foreign key) will automatically be deleted.

Example:

DROP TABLE IF EXISTS inventory,products;

CREATE TABLE products
( product_id  INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (product_id)
);

CREATE TABLE inventory
( inventory_id  INT NOT NULL AUTO_INCREMENT,
  product_id INT NOT NULL,
  quantity INT,
  PRIMARY KEY (inventory_id),
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id) ON DELETE CASCADE
);

So the product_id in the inventory table references the product_id in the products table.

What this means is that the product_id in the inventory table must be a value found in the product_id of the products table. This creates a relation between these two tables.

Fill with some values:

INSERT INTO products (product_name) VALUES ('toy car');
INSERT INTO products (product_name) VALUES ('flying turtle');
INSERT INTO products (product_name) VALUES ('pink bus');

We see in products table:

So now we can do:

INSERT INTO inventory (product_id,quantity) VALUES ('2','1400');

Note: we cannot do:

INSERT INTO inventory (product_id,quantity) VALUES ('6','1400');

This happens because ‘6’ is not a value of product_id in the parent table products.

Let’s execute the following:

INSERT INTO inventory (product_id,quantity) VALUES ('2','1400');
INSERT INTO inventory (product_id,quantity) VALUES ('3','7888');

inventory table now reads

If we now delete the flying turtle (product_id = 2) from the products list, the row with the foreign key ‘product_id’ from the inventory table will automatically be deleted too.

If deleting the tables:

DROP TABLE IF EXISTS inventory,products;

will work, but

DROP TABLE IF EXISTS products,inventory;

won’t. The order of deletion must be UP the chain, from child to parent, not the other way around.

drop table products;

Once again won’t work. The reason being is that table inventory has foreign key from table products. It is dependent on products and table products is the parent of the child table inventory.

Relation tables must be CREATED from the TOP DOWN and must be deleted from the BOTTOM up

Tags: , ,

Leave a Reply