Handling error of foreign key

Question

How to set a FK value NULL upon receiving error for foreign key constraint?

Consider a simple table a

CREATE TABLE test
(
    id int(11) NOT NULL AUTO_INCREMENT
    ex_key int(11),
    FOREIGN KEY(ex_key) REFERENCES ex_table(ex_key) ON DELETE SET NULL,
    PRIMARY KEY (id)
)

If receiving FK error for an invalid ex_key

INSERT INTO test (id, ex_key) VALUES ('22', '22');

we can insert by setting NULL value for the foreign key

INSERT INTO test (id, ex_key) VALUES ('22', NULL);

It can be done by an IF statement, but is there a way to do so in the same query?

I hope to find a solution similar to available ON DUPLICATE KEY UPDATE for UNIQUE columns.

Answer 1

So, when you insert a row into test with an invalid ex_key value (one that isn't in the referenced column in the referenced table), you want that insert to succeed, instead of fail, and you want the invalid value inserted as NULL instead instead of the value you attempted to insert. Right?

A "before insert" trigger should allow you to accomplish that, by doing the lookup and changing the value of ex_key to NULL if ex_key isn't a legit value.

Of course, there's some overhead on processing any trigger, but if you need this functionality, this is probably the most painless way to do it.

Also, If you already have a "before insert" trigger on this table, you would just add the IF ... END IF block to the body of the existing trigger.

DELIMITER //

CREATE TRIGGER test_bi BEFORE INSERT ON test FOR EACH ROW
BEGIN
    IF (SELECT ex_key FROM ex_table WHERE ex_table.ex_key = NEW.ex_key) IS NULL THEN
        SET NEW.ex_key = NULL;
    END IF;
END;

//

DELIMITER ;

I built a proof of concept of this with MySQL 5.5.27, here: http://sqlfiddle.com/#!2/d0a8e/1/0

Note, this also completely prevents the foreign key error from even occurring, because the trigger replaces NEW.ex_key's incoming value with NULL (when appropriate) before the validity of the value is checked against the foreign key constraint by InnoDB.