Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
yes, this is exactly what I wanted. Of course, a you quoted, this cannot be a universal solution due to significant overhead in write-intensive tables; but this can be useful for some cases. –  All Oct 19 '12 at 18:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.