Равен ли NULL значению NULL в уникальном ключе: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
Есть таблица с телефонами:
create table phone (
client_id number(6) not null,
active number(1) not null check (active in (0, 1)),
value varchar2(15)
);
insert into phone (client_id, active, value) values (10, 0, '1111');
insert into phone (client_id, active, value) values (10, 1, '3333');
insert into phone (client_id, active, value) values (15, 0, '5555');
insert into phone (client_id, active, value) values (15, 1, '6666');
insert into phone (client_id, active, value) values (15, 0, '7777'); --ошибочная запись
Попытался обеспечить только один активный телефон на клиента, используя уникальный индекс, но не смог заставить его работать.
Когда я пытаюсь создать индекс:
create unique index ix1 on phone (client_id, case when active = 1 then active end);
Получаю ошибку, потому что запись в индексе не уникальна:
Error: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
SQLState: 72000
Но это ведь отлично работает в PostgreSQL.
Почему тогда не работает в Oracle?
Две неактивные записи получат из выражения CASE значения NULL, разве они будут равны в Oracle? Если удалить последнюю запись со значением 7777, то индекс будет создан.
Свободный перевод вопроса Is null equal to null in Oracle database? от участника @The Impaler
Ответы (1 шт):
Сделайте так:
create unique index ix1 on phone (case active when 1 then client_id end);
Уникальный индекс не сохраняет записи только, если значения всех столбцов NULL. Однако, в данном случае индекс на основе функции приводит к тому, что значение одного из двух столбцов NULL, поэтому запись будет проиндексирована, что приведет к дублированию значения в индексе.
Если изменить индекс так, чтобы индексировалось только одно значение, полученное из столбцов active и client_id, то есть, чтобы записи, где active = 0, приводили к значению NULL из выражения CASE, тогда эти записи не будут включены в индекс, и только активные записи будут проверяться на наличие дубликатов.
Об этом упомянуто в подгл. Unique Constraints документации:
To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls. To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.