Как я могу ограничить несколько столбцов, чтобы избежать дублирования, но игнорировать нулевые значения?

Question

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

create table sandbox(a number(10,0), b number(10,0));
create unique index sandbox_idx on sandbox(a,b);

insert into sandbox values (1,1); -- accepted
insert into sandbox values (1,2); -- accepted
insert into sandbox values (1,1); -- rejected

insert into sandbox values (1,null); -- accepted
insert into sandbox values (2,null); -- accepted
insert into sandbox values (1,null); -- rejected

insert into sandbox values (null,1); -- accepted
insert into sandbox values (null,2); -- accepted
insert into sandbox values (null,1); -- rejected

insert into sandbox values (null,null); -- accepted
insert into sandbox values (null,null); -- accepted

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

Очевидно, Oracle реализует что-то другое, хотя:
3. Отклоните дубликаты, но принимайте (только), когда все ограниченные значения столбца неизвестны.

Я могу придумать способы использовать реализацию Oracle, чтобы получить возможность использовать case (2) - например, иметь специальное значение для "unknown" и сделать столбцы необнуляемыми. Но я не могу понять, как получить вариант использования (1).

Другими словами, как я могу заставить Oracle действовать так?

create table sandbox(a number(10,0), b number(10,0));
create unique index sandbox_idx on sandbox(a,b);

insert into sandbox values (1,1); -- accepted
insert into sandbox values (1,2); -- accepted
insert into sandbox values (1,1); -- rejected

insert into sandbox values (1,null); -- accepted
insert into sandbox values (2,null); -- accepted
insert into sandbox values (1,null); -- accepted

insert into sandbox values (null,1); -- accepted
insert into sandbox values (null,2); -- accepted
insert into sandbox values (null,1); -- accepted

insert into sandbox values (null,null); -- accepted
insert into sandbox values (null,null); -- accepted

Answer 1

create unique index sandbox_idx on sandbox
 (case when a is null or b is null then null else a end,
  case when a is null or b is null then null else b end);

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

Разобрался с помощью решения другого вопроса: как ограничить таблицу базы данных, чтобы только одна строка могла иметь конкретное значение в столбце?

Так что иди туда и дай Тони Эндрюсу очки тоже.:)

Answer 2

Попробуйте индекс на основе функций:

создать уникальный индекс sandbox_idx в песочнице (СЛУЧАЙ, КОГДА БУДЕТ НУЛЬ, ТОГДА НУЛЬ, КОГДА Б НУЛ, ТОГДА НЕДЕЙСТВИТЕЛЬНО, ДРУГОЙ a||','||b END);

Есть и другие способы снять шкуру с этой кошки, но это один из них.

Answer 3

Я не парень Oracle, но вот идея, которая должна работать, если вы можете включить вычисляемый столбец в индекс в Oracle.

Добавьте дополнительный столбец к вашей таблице (и вашему индексу UNIQUE), который вычисляется следующим образом: он равен NULL, если и a, и b не равны NULL, а в противном случае это первичный ключ таблицы. Я называю эту дополнительную колонку "нольбастер" по понятным причинам.

alter table sandbox add nullbuster as 
  case when a is null or b is null then pk else null end;
create unique index sandbox_idx on sandbox(a,b,pk);

Я приводил этот пример несколько раз в 2002 году или около того в группе Usenet microsoft.public.sqlserver.programming. Вы можете найти обсуждения, если вы будете искать groups.google.com по слову "nullbuster". Тот факт, что вы используете Oracle, не должен иметь большого значения.

PS В SQL Server это решение в значительной степени заменено отфильтрованными индексами:

create unique index sandbox_idx on sandbox(a,b)
(where a is not null and b is not null);

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

create view sandbox_for_unique as
select a, b from sandbox
where a is not null and b is not null;

create index sandbox_for_unique_idx on sandbox_for_unique(a,b);

Answer 4

Я думаю, что вы можете тогда.

Просто для справки, я оставляю свой параграф, чтобы объяснить, почему Oracle ведет себя так, если у вас есть простой уникальный индекс на две колонки:

Oracle никогда не примет две (1, нулевые) пары, если столбцы имеют уникальную индексацию.

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

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

(NULL, NULL) не индексируется, потому что нет значения для индексации. Вот почему это не нарушает уникальное ограничение.