Index únic condicionat

Imaginem que tenim una taula on tenim les subscripcions de clients a
determinats productes i volem garantir que a la taula no hi ha més d’una subscripció alhora per un client i un producte. És a dir, tenim una taula SUBSCRIPCIONS amb aquesta estructura:

  ID_SUBSCRIPCIO ID_CLIENT ID_PRODUCTE DATA_ALTA DATA_BAIXA ID_MOTIU_BAIXA ...

Volem garantir que no no poguem tenir això:

  ID_SUBSCRIPCIO ID_CLIENT ID_PRODUCTE DATA_ALTA  DATA_BAIXA ID_MOTIU_BAIXA ...
  -------------- --------- ----------- ---------- ---------- --------------
  1000           50        10          18/12/2007   (null)     (null)
  1001           50        10          30/04/2008   (null)     (null)

En aquest cas, la solució consistiria en crear un índex únic de funció per aquestes columnes:

  ID_CLIENT, ID_PRODUCTE, NLV2(DATA_BAIXA, ID_SUBSCRIPCIO, -1)

És a dir, en Oracle hauriem d’executar això:

  create unique index IDX_UK_SUBSCRIPCIONS on SUBSCRIPCIONS (
      ID_CLIENT,
      ID_PRODUCTE,
      NVL2(ID_MOTIU_BAIXA, ID_SUBSCRIPCIO, -1)
  );

D’aquesta manera evitem fer servir triggers i podem aconseguir que la base de dades ens validi aquestes regles de negoci. A més, aquesta solució també es pot aplicar, amb les corresponents modificacions, a taules on no hi ha una clau primària per una sola columna o per als casos en que no fem servir el valor null.