mysql добавляет уникальный многоколоночный без проверки фактических данных

#mysql #sql #unique-constraint #unique-index

#mysql #sql #уникальное ограничение #уникальный индекс

Вопрос:

Для проекта mysql версии 8.0.18 с MariaDB 10.4.10

Я хотел бы добавить в свою существующую таблицу уникальное ограничение для нескольких столбцов

 ALTER TABLE  'new_purchasseorder' ADD UNIQUE ('created', 'fk_customer_id', 'fk_removal_id', 'fk_recipient_id')

но не хотелось бы проверять старые данные

что-то вроде этого:

 where id > 3869

я также пробовал SET FOREIGN_KEY_CHECKS=0; , но не работал в этом случае.

возможно ли это?

Моя таблица выглядит так:

1. Вы не можете этого сделать. Добавьте его как INDEX . Добавление UNIQUE ключа означает, что он вернется и проверит ваши данные, чтобы применить это.

2. В конце концов, представьте себе разочарование в вашей таблице, где результаты ниже этого идентификатора не будут иметь UNIQUE ключа. Расслабьтесь и тщательно продумайте, как структура вашей базы данных будет основываться на потребностях вашего проекта. Добавление правильных ключей в таблицы имеет решающее значение для производительности, и не только это.

Ответ №1:

Насколько я знаю, вы не можете сделать это с уникальным ограничением, потому что, как вы уже обнаружили, такое ограничение будет применено ко всей таблице, независимо от id значения. Одним из обходных путей может быть использование триггера before insert, который выполняет утверждение:

 DELIMITER //
CREATE TRIGGER contacts_before_insert
BEFORE INSERT ON new_purchasseorder FOR EACH ROW

BEGIN

IF EXISTS (SELECT 1 FROM new_purchasseorder
           WHERE created = NEW.created AND
                 fk_customer_id = NEW.fk_customer_id AND
                 fk_removal_id = NEW.fk_removal_id AND
                 fk_recipient_id = NEW.fk_recipient_id)
THEN
    signal sqlstate '45000';
END IF;

END; //

DELIMITER ;

Этот триггер вставки приведет к тому, что любая входящая вставка с тем, что ваш уникальный индекс определяет как повторяющиеся данные, завершится ошибкой с ошибкой, эффективно блокируя эту вставку.

Лучшей долгосрочной (и более простой) стратегией может быть просто исправление ваших старых данных, чтобы они могли соответствовать требованиям ограничения unique.

Ответ №2:

Начиная с версии 8.0.13, MySQL поддерживает функциональные ключевые части — в основном индексы по выражению. Предполагая, что все 4 столбца не обнуляются, вы можете сделать:

 create unique index idx_new_purchaseorder on new_purchaseorder (
    (
        case when id > 3869 
            then concat_ws('$$', created, fk_customer_id, fk_removal_id, fk_recipient_id) 
         end
    )
)

case Выражение фильтрует id значения и генерирует объединенную строку, которая должна быть уникальной для строк, соответствующих фильтру. Я использовал несколько необычных символов, чтобы избежать «поддельных» дубликатов.

Демонстрация на скрипке DB