Cuelogic Blog Icon
Cuelogic Career Icon
Home > Blog > All > Automize Your Database – MySQL

Automize Your Database – MySQL

Databases are incredibly powerful. Many developers prefer to re-invent functionality in PHP rather than using database applications. But using the database applications will increase performance and saving development time.

Today we will discuss about the Triggers & Cascade Delete. In this blog my intention is to help developers those who don’t know about this MYSQL applications with some examples. I hope it will help you to understand and use these in you projects.

Cascade Delete:

Most of the time we are dealing with entity those have multiple attributes stored in different key mapping tables. While removing record from main entity table, need to clear relevant data from other tables as well.  This feature provides ability to remove all dependent data automatically when you delete entity from main table.

Common example is User. When user registers on site, we store his information in various other tables. When there is need to remove any user from the database. It must to remove all other information related to that user. In such case you can use cascade delete. Check the following example.

Requirements: Just remember that you need to use the InnoDB storage engine. MyISAM storage engine doesn’t support foreign keys.

  1. We are creating user table which will store  login information.
//Create User table
CREATE TABLE IF NOT EXISTS `user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(32) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB;

Add dummy data into the user table.

INSERT INTO `user` (`uid`, `login`, `password`) VALUES ('', 'user1', '123456');
INSERT INTO `user` (`uid`, `login`, `password`) VALUES ('', 'user2', '123456');
INSERT INTO `user` (`uid`, `login`, `password`) VALUES ('', 'user3', '123456');

2.  User has his other personal information for which we are creating user_info table. If you check the following table structure.  I have maintained  uid as foreign key from user table.

CREATE TABLE IF NOT EXISTS `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `firstName` varchar(32) NOT NULL,
  `lastName` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_uid` (`uid`) 
) ENGINE=InnoDB;

Set cascade delete  relation between users and user_info using the FOREIGN key.

ALTER TABLE `user_info` ADD CONSTRAINT `FK_userInfo_id` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;

Add dummy data into the user_info table.

INSERT INTO `user_info` ( `id` , `uid` , `firstName` , `lastName` ) VALUES ("", "1", 'user_fname_1', 'user_lname_1');
INSERT INTO `user_info` ( `id` , `uid` , `firstName` , `lastName` ) VALUES ("", "2", 'user_fname_2', 'user_lname_2');
INSERT INTO `user_info` ( `id` , `uid` , `firstName` , `lastName` ) VALUES ("", "3", 'user_fname_3', 'user_lname_3');

3. We create one more table to store user address information.  As you seen above, this table also maintain foreign key uid from user table.

CREATE TABLE IF NOT EXISTS `user_address_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `city` varchar(32) NOT NULL,
  `State` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_uid` (`uid`) 
) ENGINE=InnoDB;

Set cascade delete  relation between users and user_address_info using the FOREIGN key.

ALTER TABLE `user_address_info` ADD CONSTRAINT `FK_userAddressInfo_id` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;

Add dummy data into the user_address_info table.

//Dumping data for table `user`
INSERT INTO `user_address_info` ( `id` , `uid` , `city` , `State` ) VALUES ("", "1", 'city_1', 'State_1');
INSERT INTO `user_address_info` ( `id` , `uid` , `city` , `State` ) VALUES ("", "2", 'city_2', 'State_2');
INSERT INTO `user_address_info` ( `id` , `uid` , `city` , `State` ) VALUES ("", "3", 'city_3', 'State_3');

         As you can see every cascade script has unique name. It is referring foreign key uid from user table. So when you delete the user from user table, it will delete all related information from other reference tables.

You can also remove Cascade delete with deleting foreign key from table. Check following SQL.

ALTER TABLE `user_info` DROP FOREIGN KEY `FK_userInfo_id` ;
ALTER TABLE `user_address_info` DROP FOREIGN KEY `FK_userAddressInfo_id`

Triggers:

In MySQL, a trigger is a set of SQL statements that is invoked automatically when a change is made to the data on the associated table. A trigger can be defined to be invoked either BEFORE or AFTER data change by INSERT, UPDATE and DELETE statements. MySQL allows you to define maximum six triggers for each table.

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE
  • AFTER DELETE

To understand this we take shopping cart example. Suppose we have order table and another is product _stock table. Order table has the information related to order placed by user. Once order is confirmed, the available product stock will be reduced.

CREATE TABLE `oder` (
`order_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`order_qty` INT NOT NULL ,
`prod_id` INT NOT NULL ,
`order_date` TIMESTAMP NOT NULL) ENGINE = InnoDB;

Product Stock information stored in product_stock table.  We have maintained product id (prod_id) as foreign key.

CREATE TABLE `product_stock` (
`prod_id` INT NOT NULL ,
`stock` INT NOT NULL ,
UNIQUE (`prod_id` ),
  KEY `ix_prod_id` (`prod_id`),
  KEY `ix_stock` (`stock`)
) ENGINE = InnoDB;

Add dummy data for product_stock table.

INSERT INTO `product_stock` (`prod_id`, `stock`) VALUES
(1, 50),
(2, 50);

Now we set the trigger on product_stock table. So when order has been placed by user then available product quantity will be automatically reduced. If you check the following script. `update_stock_after_conf-order` is the trigger name. Which is must be unique.

DELIMITER $$ CREATE TRIGGER `update_stock_after_conf-order` AFTER INSERT ON `oder` 
FOR EACH
ROW 
BEGIN 
UPDATE `product_stock` SET `stock` = ( `stock` - NEW.order_qty ) WHERE prod_id = NEW.prod_id;

END$$DELIMITER;

For checking above trigger functionality fire following SQLs.

//Add Order for product and check then check the product stock
INSERT INTO `oder` (`order_id`, `order_qty`, `prod_id`, `order_date`) VALUES
(1, 2, 1, '2013-04-24 17:46:09'),
(2, 3, 2, '2013-04-24 17:47:18');

If you cross check the Product quantity from  product_stock table,  you will  find product 1 & 2 quantity will be reduced.

You can also remove trigger. Check following SQL for it.

//Remove Trigger Syntax
DROP TRIGGER `update_stock_after_conf-order`;