How to use Cascade in MySQL

If you are going to make a change in one table and you want to automatically pass on the change (delete, update) to its dependents, then we use Cascade in MySQL.

Let me explain this process by means of an example.

Step 1: Create a ‘person’ table. This ‘person’ table must be Innodb.
CREATE TABLE IF NOT EXISTS `persons` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(222) NOT NULL,
`first_name` varchar(222) NOT NULL,
`address` varchar(222) NOT NULL,
`city` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

And insert some data in this table

INSERT INTO `persons` (`id`, `last_name`, `first_name`, `address`, `city`)
VALUES (2, 'sharma', 'vikas', 'mohali', 'mohal'), (5, 'singh', 'jaskaran', 'mullanput', 'mohali');

Step 2: Now create an ‘order’ table
CREATE TABLE IF NOT EXISTS `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` int(11) NOT NULL,
`person_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `orders_ibfk_1` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Step 3: Next we create a relation between ‘person’ table and ‘order’ table
ALTER TABLE `orders`
ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

As you can see here –

• “orders_ibfk_1″ is the constraint name
• “person_id” is the foreign key in ‘orders’ table
• foreign key references the primary key id in ‘persons’ table
• and cascade has been set on delete and on update

Step 4: After we’ve created a relation between them, let’s insert data in ‘order’ table
INSERT INTO `orders` (`id`, `order_no`, `person_id`)
VALUES (1, 23233223, 5), (2, 232323, 2);

Now, when you update in ‘person’ table such that the primary key is updated then there is no need to update in ‘order’ table as the foreign key person_id will be automatically updated. And when you delete a record in ‘person’ table then there is no need to delete in ‘order’ as it would automatically be performed by cascading.

Enjoy