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.

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)!

share|improve this question
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

1 Answer 1

up vote 1 down vote accepted

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]

share|improve this answer
    
OMG you actually answered this? The concept partial unique constraint seems convoulted to me. But you actually wrapped your head around it. +1 !!! –  RolandoMySQLDBA Apr 26 '13 at 22:08
    
@RolandoMySQLDBA: a partial unique constraint does make sense. Think about a "projects" table. It contains "active" and "inactive" projects. The name of a project must be unique for all active projects. For old (inactive) projects you might allow duplicate names. There are several other scenarios where this does make sense, e.g. like this: bit.ly/fVqmEX –  a_horse_with_no_name Apr 27 '13 at 17:07
1  
In addition to the insert trigger, you need BEFORE UPDATE trigger to prevent a subsequent updates from violating the constraint. –  Michael - sqlbot Apr 28 '13 at 14:07
    
Thanks derek-downey and @michael-sqlbot for your time and helping me solving my problem! –  linqu Apr 28 '13 at 17:06

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.