oracle - una - formula para buscar datos repetidos en excel

¿Cómo puedo restringir varias columnas para evitar duplicados, pero ignorar los valores nulos?

Question

Aquí hay un pequeño experimento que ejecuté en una base de datos Oracle (10g). Aparte de la conveniencia de la implementación de (Oracle), no puedo entender por qué algunas inserciones son aceptadas y otras rechazadas.

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

Suponiendo que tiene sentido ocasionalmente tener algunas filas con algunos valores de columna desconocidos, puedo pensar en dos posibles casos de uso que impliquen evitar duplicados:
1. Quiero rechazar duplicados, pero acepto cuando se desconoce el valor de cualquier columna restringida.
2. Deseo rechazar los duplicados, incluso en los casos en que se desconoce el valor de una columna restringida.

Aparentemente, Oracle implementa algo diferente:
3. Rechazar duplicados, pero aceptar (solo) cuando se desconocen todos los valores de columna restringidos.

Puedo pensar en maneras de hacer uso de la implementación de Oracle para llegar al caso de uso (2), por ejemplo, tener un valor especial para "desconocido" y hacer que las columnas no sean nulas. Pero no puedo entender cómo usar el caso (1).

En otras palabras, ¿cómo puedo hacer que Oracle actúe así?

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

Pruebe un índice basado en funciones:

crear un índice único sandbox_idx en sandbox (CASO CUANDO a IS NULL ENTONCES NULL WHEN b IS NULL THEN NULL ELSE a || '','' || b END);

Hay otras formas de despellejar a este gato, pero este es uno de ellos.

Answer 2

Supongo que puedes entonces.

Sin embargo, para que quede constancia, dejo mi párrafo para explicar por qué Oracle se comporta así si tiene un índice único simple en dos columnas:

Oracle nunca aceptará dos (1, nulo) pares si las columnas están indexadas de forma exclusiva.

Un par de 1 y un nulo, se considera un par "indexable". Un par de dos valores nulos no se puede indexar, es por eso que le permite insertar tantos pares nulos como quiera.

(1, nulo) se indexa porque 1 se puede indexar. La próxima vez que intente insertar (1, nulo) de nuevo, 1 es recogido por el índice y se viola la restricción única.

(nulo, nulo) no está indexado porque no hay ningún valor para indexar. Es por eso que no viola la restricción única.

Answer 3

No soy un tipo de Oracle, pero esta es una idea que debería funcionar, si puede incluir una columna calculada en un índice en Oracle.

Agregue una columna adicional a su tabla (y su índice UNIQUE) que se calcula de la siguiente manera: es NULL si tanto a como b no son NULL, y de lo contrario es la clave primaria de la tabla. Llamo a esta columna adicional "nullbuster" por razones obvias.

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);

Di este ejemplo varias veces alrededor de 2002 en el grupo Usenet microsoft.public.sqlserver.programming. Puede encontrar las discusiones si busca en groups.google.com la palabra "nullbuster". El hecho de que estés usando Oracle no debería importar mucho.

PS En SQL Server, esta solución es prácticamente reemplazada por índices filtrados:

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

El hilo al que hizo referencia sugiere que Oracle no le da esta opción. ¿Tampoco tiene la posibilidad de una vista indexada, que es otra alternativa?

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

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);

Un índice funcional! Básicamente solo necesitaba asegurarme de que todas las tuplas que quiero ignorar (es decir, aceptar) se traducen a todos los nulos. Feo, pero no feo. Funciona según lo deseado.

Lo resolvió con la ayuda de una solución a otra pregunta: ¿cómo restringir una tabla de base de datos para que solo una fila pueda tener un valor particular en una columna?

Entonces ve y dale puntos a Tony Andrews. :)