4

I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL.

My table looks like this:

CREATE TABLE `my_table` (
  `id` bigint(20) NOT NULL,
  `col1` bigint(20) NOT NULL,
  `col2` bigint(20) NOT NULL,
  `col3` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

I want the three columns col1, col2 and col3 to be unique. But a normal unique constraint

UNIQUE KEY `unique_constraint` (`col1`,`col2`,`col3`)

still allows me to insert identical rows whose col3 is NULL. I want to forbid entries like these:

1  | 1 | 1 | NULL
2  | 1 | 1 | NULL

I was searching for a partial unique constraint in MySQL as proposed in the post above but apparently there are not supported in MySQL 5.5.

Can somebody give me a hint how to solve this best in mysql, I am also hoping there will be a performant solution!

Thanks from a newbie in mysql (coming from PostgreSQL)!

  • 1
    I guess you'll have to live with that limitation (or go back to Postgres) – a_horse_with_no_name Apr 26 '13 at 19:17
  • Should (1,1,NULL) and (1,1,5) rows be allowed together or not? – ypercubeᵀᴹ Apr 26 '13 at 21:59
  • @ypercube yes they should be allowed together. – linqu Apr 27 '13 at 9:18
6

The only option that comes to mind is a BEFORE INSERT trigger. In 5.5 you could use SIGNAL:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SIGNAL SQLSTATE '45000'
     SET MESSAGE_TEXT = 'Cannot insert this item due to unique constraint', 
         MYSQL_ERRNO = 1001;
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

But pre 5.5, you would do this something like this:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SET NEW='Error: Cannot insert this item due to unique constraint';
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

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.