Unique index with one nullable key and foreign key

Question

We have the following table and we ran into an issue that our unique key is not working (as expected).

CREATE TABLE `documentation_photos` (
  `commissionID` smallint(5) unsigned NOT NULL,
  `periodID` smallint(5) unsigned NOT NULL,
  `carreirID` int(11) NOT NULL,
  `groupID` smallint(5) unsigned DEFAULT NULL,
  `order` tinyint(4) NOT NULL,
  `file` varchar(200) NOT NULL,
  UNIQUE KEY `idZak_UNIQUE` (`commissionID`,`periodID`,`carreirID`,`groupID`,`order`),
  KEY `ncis & obdobi & idZak` (`commissionID`,`periodID,`carreirID``),
  KEY `fotodoc_skupina` (`idSkup`),
  CONSTRAINT `fotodoc_` FOREIGN KEY (`groupID`) REFERENCES `groups` (`groupID`),
  CONSTRAINT `fotodoc_zaknos` FOREIGN KEY (`commissionID`, `periodID`, `carreirID`) REFERENCES `carrier_of_commission` (`commissionID`, `periodID`, `carreirID`) ON DELETE CASCADE
);

I saw the issue, that describes that NULL is considered unique every time it occurs, but it causes a lot of problems for us. The only one I found was for Postgres...

|--------------|----------|-----------|---------|-------|---------|
| commissionID | periodID | carrierID | groupID | order | file    |
|--------------|----------|-----------|---------|-------|---------|
| 1140         | 117      | 2235      | null    | 1     | photo-1 |
|--------------|----------|-----------|---------|-------|---------|
| 1140         | 117      | 2235      | null    | 1     | photo-1 | -- This is duplicate
|--------------|----------|-----------|---------|-------|---------|
| 1140         | 117      | 2235      | null    | 2     | photo-2 |
|--------------|----------|-----------|---------|-------|---------|
| 1140         | 117      | 2235      | null    | 1     | photo-1 | -- This is duplicate
|--------------|----------|-----------|---------|-------|---------|

I want to treat null as a single value.

We would like to have working unique/primary key and keep the foreign key if possible. Is there some known solution for this in MySQL?

[What to do with null values when modeling and normalizing?](https://stackoverflow.com/a/40733625/3404097) — philipxy, Jun 23 '20 at 13:00
Each NULL is unique (two NULLs are not equal) - the row which contains NULL in index expression is not checked for uniqueness, the row which contains NULL in FK expressions is not checked for reference integrity. *Is there some known solution for this in MySQL?* Use generated column which replaces NULL with some definite but logically impossible literal value, use this column in index/FK expression instead of nullale column. — Akina, Jun 23 '20 at 13:31
It's not clear what you want. What do "expected" & "have working unique/primary key" & "keep the foreign key" mean? Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. (Use SQL terms like FK, PK, unique & match properly.) When a SQL FK REFERENCES a NULLable UNIQUE, a NULL FK column value satisfies the constraint, regardless of the referenced column's values. (Beware, MySQL does not enforce all related constraints, read the manual re FKs.) PS Constraints are not indexes, learn the difference. And MySQL KEY without P or F is a synonym for INDEX. — philipxy, Jun 23 '20 at 13:36
Clarify via edits, not comments. PS You still aren't clear. Solution to what? Saying what SQL does doesn't say what you want. You don't say what you want to have in the database & how it is to be constrained. Don't use "unique" or "checked for integrity" unless you mean the SQL meaning, find another word otherwise--we don't know which you mean. Etc. PS Once you're clear we can expect it to be a duplicate. PS That linked Q&A seems irrelevant, it's about non-UNIQUE, but you seem to be talking about UNIQUE but NULL. — philipxy, Jun 23 '20 at 13:42

score 1 · Accepted Answer · answered Jun 23 '20 at 16:06

Each NULL is unique (two NULLs are not equal) - the row which contains NULL in index expression is not checked for uniqueness, the row which contains NULL in FK expressions is not checked for reference integrity.

Is there some known solution for this in MySQL?

Use generated column which replaces NULL with some definite but logically impossible literal value, use this column in index/FK expression instead of nullale column.

Unique index with one nullable key and foreign key

1 Answers1