Thesql UNIQUE KEY multiple columns with one column as NULL

MySQLnull

I understand the way UNIQUE KEY works in Mysql 5.5+, and understand that NULL allows duplicate entries to be created.

In following schema and I would finally want that family_relation should have only a unique (father, mother, kid) relationship with optionally kid (as kid_id can be null). In the INSERT INTO ON DUPLICATE KEY at the end, you will see that two family_relation entries are created for same father and mother due to kid_id=null. How can I prevent this and enforce mysql to have only one relationship?

DROP TABLE IF EXISTS family_relation;
DROP TABLE IF EXISTS person;

CREATE TABLE person (
    person_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

CREATE TABLE family_relation (
    pk INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    father_id INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (father_id) REFERENCES person(person_id) ON DELETE CASCADE,
    mother_id INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (mother_id) REFERENCES person(person_id) ON DELETE CASCADE,
    kid_id INTEGER UNSIGNED COMMENT 'family may or may not have kids',
    FOREIGN KEY (kid_id) REFERENCES person(person_id) ON DELETE CASCADE,
    UNIQUE KEY one_father_mother_with_possible_kids (father_id, mother_id, kid_id)
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;


INSERT INTO person (name) VALUES ('john the father');
INSERT INTO person (name) VALUES ('ashley the mother');
INSERT INTO person (name) VALUES ('dennis the kid');

INSERT INTO family_relation (father_id, mother_id) VALUES((SELECT person_id from person where name like '%john%'), (SELECT person_id from person where name like '%ashley%'))
ON DUPLICATE KEY UPDATE father_id=VALUES(father_id), mother_id=VALUES(mother_id);

-- This creates a duplicate entry of family_relation due to kid_id = NULL. HOW DO I PREVENT THIS?
INSERT INTO family_relation (father_id, mother_id) VALUES((SELECT person_id from person where name like '%john%'), (SELECT person_id from person where name like '%ashley%'))
ON DUPLICATE KEY UPDATE father_id=VALUES(father_id), mother_id=VALUES(mother_id);
SELECT * from family_relation;

/* Result is as
"pk" "father_id" "mother_id" "kid_id"
"1" "1" "2" \N
"2" "1" "2" \N
*/

here is http://sqlfiddle.com/#!2/834d6/1

Best Answer

Possibilities, you can:

  • use the BDB-Storage engine in MySQL prior 5.1. which treats nulls as non-distinct to each other. Later versions don't support BDB
  • introduce a special value for "no kid_id", so you won't have nulls
  • add a not-null pseudo column, which is filled by triggers and has to be in the constraint instead of the original kid_id
  • use a before-insert trigger instead of the constraint, which checks if there is a row containing all the 'unqiue' fields (use <=> for also compare nulls) and changes the PK of the to-be-inserted-row to the one of the existing one so it results in a "Duplicate key" error.
Related Question