1

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

  • What exactly is your requirements? Do you want to restrict every family to (max) one kid only? – ypercubeᵀᴹ May 15 '15 at 9:30
2

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.
-4

NULL should also be seen as a value, so I also say it's a bug. I will make my NULL column a NOT NULL column with default value 0 to get around this bug.

  • 2
    Please, this is not a bug. This behaviour is according to the SQL standard. – ypercubeᵀᴹ Dec 19 '16 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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