Разрешить null в уникальном столбце

Ответ 1

Это недоразумение.
Ограничение UNIQUE делает именно то, что вы хотите. Несколько значений NULL могут сосуществовать в определенном столбце UNIQUE.

Цитата руководство по ограничениям UNIQUE:

В общем случае уникальное ограничение нарушается, когда есть больше, чем одна строка в таблице, где указаны значения всех столбцов в ограничении равны. Однако два нулевых значения не являются считается равным в этом сравнении. Это означает даже в присутствии единственного ограничения можно сохранить повторяющиеся строки, которые содержат нулевое значение по меньшей мере в одном из ограниченных столбцов. Эта поведение соответствует стандарту SQL, но мы слышали, что другие Базы данных SQL не могут следовать этому правилу. Поэтому будьте осторожны, когда разрабатывая приложения, предназначенные для переноски.

Смелый акцент мой.

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

Ответ 2

Нет такой проблемы в Postgres

В Erwin Brandstetter правильный ответ , он объясняет, что вы действительно должны видеть поведение, которое вы хотите (множественные NULL разрешены в уникальном ограничении). Вы должны увидеть это поведение в Postgres, в частности, а также любую стандартную SQL-совместимую базу данных в целом.

Обходной путь для других баз данных

Однако документ Postgres предостерегает о переносимости, поскольку некоторые базы данных, как известно, нарушают эту функцию. Для такой несоответствующей системы я предлагаю заменить использование значения NULL в таких полях фиктивным значением. Поддельным значением будет строка, такая как "unknown_" плюс какое-то произвольное значение, которое практически наверняка будет уникальным. Это произвольное значение может быть чем-то вроде текущей даты и случайного числа.

UUID

Но вместо того, чтобы сворачивать собственное произвольное значение, создайте UUID. Первоначальный UUID версии 1 действительно представляет собой комбинацию текущего времени-времени, случайного числа и компьютера практически уникального MAC-адрес.

UUID, представленный как шестнадцатеричная строка с каноническим форматированием с использованием дефиса, выглядит так:

93e6f268-5c2d-4c63-9d9c-40e6ac034f88

Итак, мое предложение состоит в объединении произвольной строки, такой как "unknown_" плюс UUID, чтобы выглядеть так:

unknown_93e6f268-5c2d-4c63-9d9c-40e6ac034f88

Поэтому мое предложение для несовместимых баз данных состоит в том, чтобы генерировать такое значение и использовать его вместо NULL, использовать его там, где у вас еще нет известного значения в этом столбце для определенной строки. Вместо написания запросов, которые ищут строки, которые имеют (или не имеют) значение NULL в этом столбце, пишут запросы, которые ищут строки, которые имеют (или не имеют) значение, начинающееся с произвольной строки, "unknown_" в этом пример. Каждая строка затем удовлетворяла бы ограничению наличия уникального значения.

В самом деле, я бы присвоил это значение "unknown_" + UUID как значение по умолчанию для этого столбца.

Вы также можете добавить ограничение NOT NULL в этот столбец.

Генерация значений UUID

Postgres имеет встроенную поддержку типа данных UUID, но это не имеет значения в этом ответе. Вам нужно создать значение UUID.

Для создания UUID вам необходимо расширение (плагин), которое добавляет эту возможность в Postgres. Большинство установщиков Postgres включают такое расширение. Это расширение называется uuid-ossp. Обычно расширение не активируется по умолчанию. Чтобы сделать это в последних версиях Postgres, используйте команду CREATE EXTENSION. Для получения инструкций см. Мое сообщение в блоге установка в Postgres 9.1 и более поздних версиях или мой другой пост в Postgres 9.0 и более ранних версиях. Как новый, так и старый способ установки легко обеспечивается, если расширение/плагин был скомпилирован и связан с установкой Postgres.

Резюме

Позвольте мне пояснить, что для Postgres один не нуждается в этом обходном пути, потому что Postgres соответствует стандарту SQL. Но если:

• Вы обеспокоены переносимостью вашего кода на какую-то другую несопоставимую систему баз данных или
• Вам необходимо обмениваться данными с несовместимой системой баз данных или
• Вы согласны с Dr. Chris Date, что NULL - это работа дьявола, и его следует избегать.

... тогда необходимо обходное решение, такое как это.

Ответ 3

Уникальные и нулевые не получают многого, так как null - это undefined по определению - вы не можете знать, являются ли два нуля одинаковыми неизвестными.

В этом смысле ваше текущее уникальное ограничение на электронную почту - это то, что нужно делать и должно работать как есть.

Если вам когда-либо понадобится сделать это иначе, работает неполный индекс:

create unique index on MMCompany((email is null)) where (email is null);

Другой подход - определить триггер ограничения. Что-то вроде:

create function email_chk() returns trigger as $$
begin
  if exists (
    select 1 from mmcompany where email is null and companyuniqueid <> new.id
  ) then
    raise 'dup null found';
  end if;
  return null;
end;
$$ language plpgsql;

create constraint trigger after insert or update on mmcompany
for each row when (new.email is null)
execute procedure email_chk();

Ответ 4

Отбросьте столбец электронной почты из таблицы. Поместите его в новую таблицу, где она может быть NOT NULL и UNIQUE:

CREATE TABLE CompanyEmail
 (
    CompanyUniqueID INT NOT NULL PRIMARY KEY
       REFERENCES MMCompany (CompanyUniqueID),
    Email VARCHAR(75) NOT NULL UNIQUE
 );

Избегайте обнуляемых ограничений UNIQUE.

Ответ 5

В некоторых базах данных не допускается множество нулевых значений, например, документация SQL Server утверждает, что "несколько нулевых значений считаются дублирующими". В базах данных, которые не допускают ограничения NULL UNIQUE, вы можете попробовать это (от GuidoG answer к другому вопросу):

CREATE UNIQUE NONCLUSTERED INDEX IDX_Email
ON MMCompany (Email)
WHERE Email IS NOT NULL;

Ответ 6

И на всякий случай, когда вы создаете свои таблицы таблиц с помощью EF Code First, отредактируйте свой метод класса миграции "Up" следующим образом, чтобы принудительно использовать ограничение UNIQUE KEY для игнорирования NULL.

migrationBuilder.Sql(@"CREATE UNIQUE NONCLUSTERED INDEX[IX_Employees_TaskId] ON[dbo].[Employees]([TaskId] ASC)
                                WHERE [TaskId] IS NOT NULL"
                                );

И затем вы можете протестировать свое уникальное ограничение, войдя в вашу БД через SQL Server Management Studio или что-то подобное. Как и в этом случае, Employee Table счастливо принимает 2 значения NULL в TaskId, хотя его столбец UNIQUE.