Остановить MySQL, допустив несколько NULL в ограничении UNIQUE

Моя схема SQL

CREATE TABLE Foo (
 `bar` INT NULL ,
 `name` VARCHAR (59) NOT NULL ,
 UNIQUE ( `name`, `bar` )
) ENGINE = INNODB;

MySQL позволяет повторить следующий оператор, что приводит к дублированию.

INSERT INTO Foo (`bar`, `name`) VALUES (NULL, 'abc');

несмотря на то, что

UNIQUE ( `name`, `bar` )

Почему это переносится и как я могу остановить его?

Ответ 1

Предупреждение: этот ответ устарел. Начиная с MySQL 5.1, BDB не поддерживается.

Это зависит от MySQL Engine Type. BDB не позволяет использовать несколько значений NULL, используя UNIQUE, но MyISAM и InnoDB допускает несколько NULL даже с UNIQUE.

Ответ 2

В общем, в зависимости от механизма хранения NULL может быть или не быть уникальным. Вы должны использовать механизм хранения, который не распознает NULL как уникальное значение, например. InnoDB или MyISAM.

Чтобы обойти это, вы можете создать "нулевое значение", например 99999999, которое вы можете распознать как NULL, так как нет способа изменить то, как ваш механизм хранения решает разрешить нули в уникальных ключах.

Ответ 3

UPDATE. Вместо этого вы должны использовать идею, предложенную @greenoldman. Создайте логическое поле с триггерами, чтобы установить значение в зависимости от того, является ли ваше поле с нулевым значением NULL или нет, а затем объединить логическое поле в уникальном ограничении с другими полями, которые определяют уникальность.

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

Основная концепция заключается в том, что вам нужно создать другое поле, не имеющее значения NULL, которое будет иметь значение вашего поля с нулевым значением с внешним ключом, дублируемым в нем с помощью триггера. Тогда уникальное ограничение будет принудительно применено для поля с недействительным дублированием. Для этого вам нужно определить поле с нулевым значением со значением по умолчанию 0, аналогичным этому:

ALTER TABLE `my_table` ADD  `uniq_foo` int(10) UNSIGNED NOT NULL DEFAULT '0';

Тогда вам просто нужно определить некоторые триггеры вроде этого:

DROP TRIGGER IF EXISTS `my_table_before_insert`;
DELIMITER ;;
CREATE TRIGGER `my_table_before_insert` BEFORE INSERT ON `my_table`
FOR EACH ROW
BEGIN
    SET NEW.uniq_foo = IFNULL(NEW.foo_id, 0);
END;;
DELIMITER ;

DROP TRIGGER IF EXISTS `my_table_before_update`;
DELIMITER ;;
CREATE TRIGGER `my_table_before_update` BEFORE UPDATE ON `my_table`
FOR EACH ROW
BEGIN
    SET NEW.uniq_foo = IFNULL(NEW.foo_id, 0);
END;;
DELIMITER ;

Ответ 4

BDB не допускает множественные значения NULL, используя UNIQUE. Но MySQL отбрасывает механизм BDB (http://dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-12.html).

Итак, теперь: http://dev.mysql.com/doc/refman/5.5/en/create-index.html

Для всех двигателей индекс UNIQUE допускает несколько значений NULL для столбцов, которые могут содержать NULL. Если вы укажете префиксное значение для столбца в индексе UNIQUE, значения столбца должны быть уникальными внутри префикса.