CODEWITHSUNDEEP
mysql
Jad Ayash
Jad Ayash

Reputation: 73

Mysql Deleting all records when deleting a foreign key

I have two MySQL Tables

CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` int(5) NOT NULL AUTO_INCREMENT,
  `order_address` varchar(255) NOT NULL,
  `order_paymentmethod` varchar(50) NOT NULL,
  `coupon_code` varchar(50) NOT NULL,
  `user_id` int(5) NOT NULL,
  PRIMARY KEY (`order_id`),
  KEY `fk_orderuser` (`user_id`),
  KEY `fk_ordercoupon` (`coupon_code`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


CREATE TABLE IF NOT EXISTS `coupons` (
  `coupon_code` varchar(50) NOT NULL,
  `coupon_discount` int(255) NOT NULL,
  PRIMARY KEY (`coupon_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When am deleting the coupon code from the coupon table the order record that the coupon is related too is also deleted. And i just want to delete the coupon code without any effect on the table orders

Is their any solution for that please?

Regards

Upvotes: 0

Views: 60

Answers (2)

Kostas Kryptos
Kostas Kryptos

Reputation: 4111

There are three options here

  1. Don't delete the coupon, use another boolean field (eg deleted) with default = true, but set it to false when you want to remove it (it doesn't actually remove it but you can handle deleted coupons using this field). This way you can also track the orders initiated with a coupon that on the way this was deleted.

  2. Remove the not null constraint from coupon_code varchar(50) NOT NULL (in orders table) and add a foreign key constraint to ON DELETE SET NULL. *For orders without a coupon set it to null from the beginning.

  3. Using the known as NULL pattern. Create a dummy coupon (ZERO discount) in your db and instead of deleting coupons, assign this dummy coupon to orders that do not require a real coupon.

*depending on the "tracking" requirements, a combination of the above approaches may be required

Upvotes: 1

Ulug'bek
Ulug'bek

Reputation: 2832

In this case you can solve that problem by using mysql trigger. Create trigger for coupons table

CREATE TRIGGER `coupons_before_delete` AFTER DELETE ON `coupons` FOR EACH ROW BEGIN
--    INSERT INTO add_inout_to_error_log(msg) VALUES(old.coupon_code);
DELETE FROM orders WHERE orders.coupon_code = old.coupon_code;
END

in this code old.coupon_code is current deleted coupon code. You can get access to any field of deleted item

Upvotes: 1

Related Questions