Indices condicionales

Hay tablas para las cuales desearíamos hacer valer condiciones de unicidad pero dadas ciertas condiciones que dependan de alguna otra columna de control.

Un ejemplo típico se da cuando mantenemos registros en una tabla con borrado lógico, y queremos que exista un único registro activo (pudiendo aparecer el registro inactivo más de una vez). Digamos que tenemos la tabla t, con dos columnas fundamentales: un id y un indicador de registro activo Y/N. Queremos que el Id sea único solamente cuando Activo es 'Y'.

Muchas veces, mucha gente se enfrenta a este problema y lo resuelve creando una tabla auxiliar para hacer valer la condición de unicidad, almacenando en ella las tuplas que no desea repetir para lograr dicho control.
Todo el problema que implica crear (y mantener) estructuras adicionales y la complejidad de manipulación, puede evitarse utilizando lo que siempre estuvo al alcance: el índice de función y la sentencia CASE.

SQL> CREATE TABLE t (sec int primary key, id VARCHAR2(100), activo VARCHAR2(1));

Table created.

SQL> CREATE UNIQUE INDEX ix_t ON t
2 (CASE WHEN activo='Y' THEN id ELSE NULL END);

Index created.

SQL> insert into t values (1, 'COD1', 'Y');

1 row created.

SQL> insert into t values (2, 'COD1', 'N');

1 row created.

SQL> insert into t values (3, 'COD1', 'N');

1 row created.

SQL> insert into t values (4, 'COD1', 'Y');
insert into t values (4, 'COD1', 'Y')
*
ERROR at line 1:
ORA-00001: unique constraint (LFER.IX_T) violated

Los índices condicionales no son un feature especial de Oracle, sino que se construyen utilizando los mismos índices de función que usualmente definimos para todas las filas de una tabla. En este caso, la función CASE permite que el índice único se construya sobre un subconjunto de registros, aquellos en el que el campo activo sea igual a 'Y'.

Ver también:
11g y sus índices invisibles
Mis índices no funcionan!
Indices de función para mejorar un LIKE