Составной уникальный ключ не работает для данных с NULL в MySQL

Составной уникальный ключ и null показывают невероятное поведение. В популярной СУБД MySQL составной уникальный ключ не работает для данных со значением NULL. Составной (i.e. наложенный на несколько полей одновременно) UNIQUE ключ не работает для записей (строк), содержащих NULL.

E.g., есть уникальный ключ на несколько полей и при попытке добавления дублирующихся данных он не срабатывает, если в одном из полей стоит null. Это документированное поведение сервера: «Индекс UNIQUE создает ограничение, так что все значения в индексе должны быть разными. Произойдёт ошибка, если вы попытаетесь добавить новую строку с ключевым значением, которое соответствует существующей строке. Для всех движков таблиц индекс UNIQUE допускает множественные значения NULL для столбцов, которые могут содержать NULL. Если вы укажете префиксное значение для столбца в индексе UNIQUE, значения столбца должны быть уникальными внутри префикса.»

Решение

Как быть? Писать триггер, проверяющий уникальность записи? Можно и так, ведь в подобной ситуации UNIQUE KEY из нескольких полей просто не срабатывает при наличии NULL в одном из полей, входящих в составной UNIQUE. Однако куда проще использовать другое значение по-умолчанию, i.e. не хранить в таких полях NULL. Использовать вместо Null такое значение по-умолчанию, которое не может быть в принципе заданным, либо использовать специальное значение как заменитель Null, e.g., значение ‘unknown’.

UNIQUE по-прежнему работает, когда в поле пустота. I.e. вместо Null в поля можно вставлять просто пустоту, если тип данных поля это допускает. Для полей, которые будут участвовать в составном UNIQUE KEY надо запрещать использование NULL (NOT NULL) и не выставлять значения по-умолчанию.

Если возникает необходимость сделать составной уникальный ключ на полупустые данные (одно из полей может не быть заданным), то это прямое указание на ошибку в проектировании структуры таблиц.