CODEWITHSUNDEEP
mysqlconstraints
PLM57
PLM57

Reputation: 1296

MySQL: unique index not respecting `null` values

I am having trouble creating a unique index respecting null-values in MySQL v5.6.20. I checked similar answers here. but couldn't solve my problem with them.

Desired behavior

I want a table which has references to three other table (date_list_assignment). The purpose of the table is the mapping of date_list-entries to course categories and/or date_list categories. The first column is therefore mandatory, while the latter two are not. If the latter two are null the date list entries is declared global. If a date list entry has no entry within this table, it is not shown anywhere.

Here are some examples of entries and their meaning:

# entry which is global within course category 2
date_list_id: 1, course_category_id: 2, date_list_category_id: null

# entry which is global
date_list_id: 1, course_category_id: null, date_list_category_id: null

# entry which is only visible within course category 2 and date list category 17
date_list_id: 1, course_category_id: 2, date_list_category_id: 17

Short version: I want to make sure, that any combination of the three columns stays unique within the table...no matter if the values are null or not.

Table schema

I have the following table:

CREATE TABLE `date_list_assignment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_list_id` int(11) NOT NULL,
  `course_category_id` int(11) DEFAULT NULL,
  `date_list_category_id` int(11) DEFAULT NULL,
  `created` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `IN_relation_unique` (`date_list_id`,`course_category_id`,`date_list_category_id`),

  KEY `FK_date_list_assignment_user_created` (`created_by`),
  KEY `FK_date_list_assignment_user_updated` (`updated_by`),
  KEY `FK_date_list_assignment_course_category` (`course_category_id`),
  KEY `FK_date_list_assignment_date_list_category` (`date_list_category_id`),

  CONSTRAINT `FK_date_list_assignment_course_category` FOREIGN KEY (`course_category_id`) REFERENCES `course_category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_date_list_assignment_date_list` FOREIGN KEY (`date_list_id`) REFERENCES `date_list` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_date_list_assignment_date_list_category` FOREIGN KEY (`date_list_category_id`) REFERENCES `date_list_category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_date_list_assignment_user_created` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_date_list_assignment_user_updated` FOREIGN KEY (`updated_by`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Problem

As you can see, I declared a unique index (IN_relation_unique) on the three columns. However, I still can create to identical rows having for example these values:

date_list_id: 1, course_category_id: 2, date_list_category_id: null

I am aware that some of this behavior changed in current MySQL-versions which is also the reason I use an index and not a composite PK allowing null-values.

For exmaple this answer states, that this is expected behavior in MySQL. If so, how can you achieve this since its also no longer possible with composite PKs allowing null-values either!?

Thanks for your help!

Upvotes: 2

Views: 3913

Answers (1)

Pred
Pred

Reputation: 9042

Yes, this is the expected behaviour in MySQL (in fact in ANSI-92 too). NULL values are not treated as equal values in unique keys and primary keys can not contain NULL values by definition.

A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. In addition, if the unique constraint was defined with PRIMARY KEY, then it requires that none of the values in the specified column or columns be the null value.

(http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)

Since you have the foreign key constraint on the nullable column, I would suggest to add a dummy value to the parent tables which states the fact that the parent is irrelevant or not determined (the records where ID = 0 maybe) and add the NOT NULL constraint to the column. (Additionally you can add the dummy value as DEFAULT).

Upvotes: 6

Related Questions