Добавление новой записи несмотря на Update on duplicate key и UNIQUE INDEX

201

08 января 2020, 07:30

Есть следующая таблица (с названием table0):

id : integer, autoincrement, primary key
table1_id : integer, nullable, foreign key -> table1(id), on delete = cascade, on update = cascade
table2_id : integer, nullable, foreign key -> table2(id), on delete = cascade, on update = cascade
table3_id : integer, nullable, foreign key -> table3(id), on delete = cascade, on update = cascade
smth_field : string

По своей сути таблица является промежуточной для связи "многие-ко-многим". Создал UNIQUE INDEX table_unique_constraint (table1_id, table2_id, table3_id).

Пытаюсь сделать:

INSERT INTO `table0` (`table1_id`,`table2_id`,`table3_id`,`smth_field`) 
VALUES (NULL,54069,3240349,'2') 
ON DUPLICATE KEY UPDATE table1_id=VALUES('table1_id'), table2_id('table2_id'), table3_id = VALUES('table3_id'), smth_field = VALUES('smth_field');

Ожидание:
Обновление записи с такими же table1_id, table2_id, table3_id на ту, что передал в запросе (по сути обновление поле smth_field).

Реальность:
Добавление новой записи несмотря на то, что стоит уникальный индекс. Все запросы к СУБД по созданию таблиц и добавлению данных делаю через Sequelize.

Подскажите, в чём дело и как это исправить?

Answer 1

Внешние ключи допускают NULL в значениях NULLable полей. Однако такие значения не являются дубликатами, ибо один NULL не равен другому NULL.

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

COALESCE(fieldname, constant)

где:
fieldname - имя поля
constant - замещающая значение NULL константа

При этом следует гарантировать, что значение-заместитель отсутствует в таблице, на которую ссылается поле. Это можно сделать, например, триггером, запрещающим вставку такого значения.

Для поля, которое ссылается на поле-автоинкремент, удобным значением-заместителем является значение 0. В автоинкрементное поле нельзя вставить ноль, он, как и NULL, вызывает генерацию следующего значения. Соответственно и в триггере потребности нет.