StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I perform exception handling in MySQL (referential integrity)
text
Body
copied!<p><code>MySQL version: 5.6.12</code></p> <p>Hi all,</p> <p>I have a MySQL database with several tables and foreign keys underneath them. Because of revisioning purposes I need to do a soft delete on a record by filling the the timestamp of deletion in the Deleted field of a table (null means it is not deleted). Now I want to create a trigger that unlinks deleted items if their parent is being deleted. To clarify I have made a small demonstration script:</p> <p>Table arnold_test.Customer:</p> <pre><code>CREATE SCHEMA IF NOT EXISTS `arnold_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; USE `arnold_test` ; -- ----------------------------------------------------- -- Table `arnold_test`.`Customer` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `arnold_test`.`Customer` ( `CustomerId` INT UNSIGNED NOT NULL AUTO_INCREMENT, `Name` VARCHAR(45) NULL, `Email` VARCHAR(45) NULL, `Phone` VARCHAR(45) NULL, `Deleted` DATETIME NULL, PRIMARY KEY (`CustomerId`)) ENGINE = InnoDB; </code></pre> <p>Table arnold_test.Address</p> <pre><code>-- ----------------------------------------------------- -- Table `arnold_test`.`Address` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `arnold_test`.`Address` ( `AddressId` INT UNSIGNED NOT NULL AUTO_INCREMENT, `CustomerId` INT UNSIGNED NOT NULL, `Street` VARCHAR(45) NULL, `ZipCode` VARCHAR(45) NULL, `State` VARCHAR(45) NULL, `City` VARCHAR(45) NULL, `Country` VARCHAR(45) NULL, `Deleted` DATETIME NULL, PRIMARY KEY (`AddressId`), INDEX `fk_Address_Customer_idx` (`CustomerId` ASC), CONSTRAINT `fk_Address_Customer` FOREIGN KEY (`CustomerId`) REFERENCES `arnold_test`.`Customer` (`CustomerId`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; </code></pre> <p>The delete trigger: </p> <pre><code>USE `arnold_test`; DELIMITER $$ USE `arnold_test`$$ CREATE DEFINER=`root`@`%` TRIGGER `arnold_test`.`NinjaDeleteTrigger_Customer` BEFORE UPDATE ON `arnold_test`.`Customer` FOR EACH ROW BEGIN IF OLD.Deleted IS NULL AND NEW.Deleted IS NOT NULL THEN BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN UPDATE `arnold_test`.`Address` R SET R.Deleted = NEW.Deleted WHERE OLD.CustomerId = R.CustomerId AND R.Deleted IS NULL; END; UPDATE `arnold_test`.`Address` R SET R.CustomerId = NULL WHERE NEW.CustomerId = R.CustomerId AND R.Deleted IS NULL; END; END IF; END$$ DELIMITER ; </code></pre> <p>Now the problem is that when I try to delete a record from the Customer table (by using <code>UPDATE .. SET Deleted = UTC_TIMESTAMP ..;</code>), the query fails because of the following error:</p> <pre><code>ERROR 1452: 1452: Cannot add or update a child row: a foreign key constraint fails (`arnold_test`.`Address`, CONSTRAINT `fk_Address_Customer` FOREIGN KEY (`CustomerId`) REFERENCES `Customer` (`CustomerId`) ON DELETE NO ACTION ON UPDATE NO ACTION) </code></pre> <p>When I retrieve the SQLSTATE code with <code>GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE; SELECT @p1;</code> the response is <code>23000</code> (which references the sqlstate in my continue handler in the trigger). I can't figure out what I am doing wrong.</p> <p>I searched the internet on exception handling in MySQL but couldn't find the key answer. Does anyone have some ideas?</p> <p>Thanks for your help! :-)</p> <p>Kind regards, Arnold Pistorius</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload