When can a unique index allow duplicate values in MySQL?

Question

I have an intersecton table to record many-to-many relationships among three tables. Each combination must be unique, so I have a unique index on the three fields. However, using the PHP application I wrote to edit this data, a user can insert the same combination multiple times. No exception is thrown even though the SQL insert is in a try-catch block, and the records are inserted with no enforcement of the unique key.

Is there something wrong with my table DLL?

CREATE TABLE `diagnosis_x_category` (
  `diagnosis_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `subcategory_id` int(11) DEFAULT NULL,
  UNIQUE KEY `uniq_diagnosis_x_category_key` (`diagnosis_id`,`category_id`,`subcategory_id`) USING BTREE,
  CONSTRAINT `diagnosis_x_category_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `nd_diagnosis` (`id`),
  CONSTRAINT `diagnosis_x_category_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `nd_category` (`id`),
  CONSTRAINT `diagnosis_x_category_ibfk_3` FOREIGN KEY (`subcategory_id`) REFERENCES `nd_subcategory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Answer 1

(This builds on the Comments so far.)

I recommend

CREATE TABLE `diagnosis_x_category` (
  `diagnosis_id` int(11)   NOT NULL,
  `category_id` int(11)    NOT NULL,
  `subcategory_id` int(11) NOT NULL DEFAULT (''),
  PRIMARY KEY `uniq_diagnosis_x_category_key`
       (`diagnosis_id`, `category_id`,`subcategory_id`) -- for going one way
  INDEX(`category_id`,`subcategory_id`, `diagnosis_id`) -- the other way
  CONSTRAINT `diagnosis_x_category_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `nd_diagnosis` (`id`),
  CONSTRAINT `diagnosis_x_category_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `nd_category` (`id`),
  CONSTRAINT `diagnosis_x_category_ibfk_3` FOREIGN KEY (`subcategory_id`) REFERENCES `nd_subcategory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Notes:

• This allows for an empty subcategory_id but turns it into '' -- This may impact your app code.
• NOT NULL is required for the PK (but not for UNIQUE).
• I assume you might looking things up by diagnosis to get cat+subcat, and also by cat+subcsat to get diagnosis.
• Consider whether a "subcategory" is unique to a "category" or can be reused in multiple "categories". The decision may impact the last two FKs.

Answer 2

So here is what I ended up doing:

Originally, some fields had a default value of NULL because they do not always contain a value.

There are more fields in this table, I just wanted to keep things simple for my question here. However, as pointed out by mustaccio in the comments, NULL allows violations of the unique key index on multiple fields.

So I got rid of the foreign keys and made the default values zero.

A value of zero would violate the foreign key. It's a compromise, but joining tables still works when there is no foreign key.

Too many indexes can slow things down anyway.

The unique key index is important here. I do not want any duplicate records.

I can avoid any data anomalies with restrictions in the application, such as having the user select values from dropdown fields populated with database values.