Blog

How-to-use-cascade-in-SQL

How to use Cascade in MySQL

  |   MySQL   |   2 Comments

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

2 Comments
  • Employment Regulations | Dec 28, 2015 at 6:13 pm

    Hi there! I juswt wish to offer you a big thumbs up for your great information you’ve got here on this
    post. I am returning to your website for more soon.

  • Nibby | Dec 15, 2016 at 6:33 am

    Woot, I will celaitnry put this to good use!

Post A Comment

Things we have Done

Got an Idea ? Lets make it happen and Get a Free Quote of your website.Get a Free Quote