Unique index allows duplicate entries in case that one of entries is NULL

Question

I am using MySQL database.

I created database table called ACTIVITY_CATEGORY with columns: ID, ACTIVITY_CATEGORY_ID, NAME and with Unique key on NAME, ACTIVITY_CATEGORY_ID.

CREATE TABLE `activity_category` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(45) NOT NULL,
  `ACTIVITY_CATEGORY_ID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `NAME_ACTIVITY_CATEGORY_ID` (`NAME`,`ACTIVITY_CATEGORY_ID`),
  KEY `FK_2l8tu9lwnmh0909gf96ccevwu` (`ACTIVITY_CATEGORY_ID`),
  CONSTRAINT `FK_2l8tu9lwnmh0909gf96ccevwu` FOREIGN KEY (`ACTIVITY_CATEGORY_ID`) REFERENCES `ACTIVITY_CATEGORY` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8

When I execute following queries it behaves correctly and displays Duplicate entry error:

-- Executes OK    
INSERT INTO `ACTIVITY_CATEGORY` (`NAME`, `ACTIVITY_CATEGORY_ID`)
VALUES ('Sport', '1');

-- Error Duplicate entry 'Sport-1' for key 'NAME_ACTIVITY_CATEGORY_ID    
INSERT INTO `ACTIVITY_CATEGORY` (`NAME`, `ACTIVITY_CATEGORY_ID`)
VALUES ('Sport', '1');

When I execute following queries it behaves somehow wierd, because it does not display Duplicate entry error:

-- Executes OK  
INSERT INTO `ACTIVITY_CATEGORY` (`NAME`, `ACTIVITY_CATEGORY_ID`)
VALUES ('Sport', NULL);

-- This should display error, but executes OK instead  
INSERT INTO `ACTIVITY_CATEGORY` (`NAME`, `ACTIVITY_CATEGORY_ID`)
VALUES ('Sport', NULL);

How is it possible? It does not take the Unique key into consideration in case that I insert NULL value? Is there any way how to solve this?

I think it helpful for you: http://stackoverflow.com/questions/429805/mysql-question-unique-key-not-functioning-correctly-or-am-i-misunderstanding — Alexey Palamar, Apr 18 '14 at 13:33

score 1 · Accepted Answer · edited May 23 '17 at 12:11

1

NULL is not a value, so it can't used as part of a unique key.

You should avoid to use NULL and use a category for this case. I am pretty sure, you null-category has a meaing in your application and you can use a category to realize this.

Compare with an other answer at SO.

edited May 23 '17 at 12:11

Community

1
1

answered Apr 18 '14 at 13:33

tjati

5,761
4
41
56

Is this standard behaviour in other databases as well? Or is it just MySQL? – Apr 18 '14 at 13:42
Oh, the behaviour of `NULL` values depends very on your database. Oracle is special here, too. NEvertheless. I think the behaviour in this case - using a NULL-value as a unique key - is in all database engines the same. – tjati Apr 18 '14 at 13:46
@omeinusch: there is nothing special about the `NULL` handling in Oracle. What *is* special though is that it treats an empty string (`''`) as `NULL` as well under certain circumstances. – Apr 18 '14 at 13:52
@a_horse_with_no_name that was what I meant :D – tjati Apr 18 '14 at 13:53

Unique index allows duplicate entries in case that one of entries is NULL

1 Answers1