Pietro Toniolo

2006-10-12

Oracle DB: unique index su colonne nullable

Filed under: Oracle, Software — ptoniolo @ 19:49:21

Stavo verificando il comportamento di Oracle nel caso di unique indexes su colonne nullable, e finalmente ho capito qual è la logica. Io non la sapevo, e quindi vi rendo partecipi.

Questo brano viene dal Database Concepts della 9.2:

Indexes and Nulls
NULL values in indexes are considered to be distinct except when all the non-NULL values in two or more rows of an index are identical, in which case the rows are considered to be identical. Therefore, UNIQUE indexes prevent rows containing NULL values from being treated as identical.
This does not apply if there are no non-NULL values—in other words, if the rows are entirely NULL.

In pratica il senso è questo. Viene verificata l’unicità dei valori, considerando i valori NULL come veri valori, a meno che le colonne non siano tutte nulle, in tal caso l’unicità non viene verificata.

Un esempio è questo. Se si pone un indice unique su due colonne (a,b), ambedue nullabili, Oracle consente di inserire nella tabella tante righe (NULL,NULL), ma una sola riga (1,NULL).

In qualche modo, mi sembra che questo contrasti con il comportamento delle foreign keys composte da più colonne nullable. In quel caso, la validità della fk viene controllata solo quando tutte le colonne della fk sono non-nulle! Questo significa che se la fk è stato-provincia-città, ci si può mettere dentro ITALIA-BAVIERA-NULL anche se la combinazione ITALIA-BAVIERA non è presente in nessuna delle possibili combinazioni della pk cui si riferisce la fk. Questo comportamento non è molto intuitivo… tenetene conto!

Create a free website or blog at WordPress.com.