De Unique keys y Foreign keys.

Las ‘unique keys’ (claves únicas) son unas restricciones (‘constraints’) que, sorprendentemente, son relativamente poco conocidas en el mundo de las bases de datos. Tanto es así que hay gente que implementa la unicidad de columnas (o de grupos de columnas) mediante índices únicos (‘unique indexes’) ignorando que un índice y una restricción de clave (‘constraint primary key’, ‘unique key’) son cosas muy diferentes (pero esa es otra historia).

En general se puede decir que la principal diferencia de una ‘unique key’ con una ‘primary key’ es que las primera admite nulos, mientras que la segunda no, y esto supone una gran diferencia: con nulos no podemos identificar unívocamente una fila (1FN).

Pero hay más cosas respecto de las ‘unique keys’. Vamos a ver unas cuantas de ellas con Oracle. ¿Por qué con Oracle? Pues porque, parafraseando a Sean Connery (James Bond) en “OO7 contra el Dr. No” (“-Hoy me siento italiano y musical“): Hoy me siento oraclero y relacional.

Como siempre, tabla de prueba al canto:

CARLOS@XE.localhost> CREATE TABLE PRUEBA01 ( ID_N INTEGER NOT NULL PRIMARY KEY,
  2  ID_N2 INTEGER NULL,
  3  ID_N3 INTEGER NULL)
  4  ;

Tabla creada.

CARLOS@XE.localhost> ALTER TABLE PRUEBA01
  2  ADD CONSTRAINT PRUEBA01UK UNIQUE (ID_N2, ID_N3);

Tabla modificada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA01 VALUES (1,1,1);

1 fila creada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA01 VALUES (2,2,NULL);

1 fila creada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA01 VALUES (3,NULL,NULL);

1 fila creada.

CARLOS@XE.localhost> SELECT * FROM PRUEBA01 ORDER BY ID_N;

      ID_N      ID_N2      ID_N3
---------- ---------- ----------
         1          1          1
         2          2
         3

3 filas seleccionadas.

Una simple tabla con su ‘primary key’ y con una ‘unique key’ sobre columnas que admiten nulos (esto es importante).

La ‘unique key’ definida en (ID_N2, ID_N3) impedirá valores repetidos para esas columnas:

CARLOS@XE.localhost> INSERT INTO  PRUEBA01 VALUES (4,1,1);
INSERT INTO  PRUEBA01 VALUES (4,1,1)
*
ERROR en línea 1:
ORA-00001: restricción única (CARLOS.PRUEBA01UK) violada

Pero, ¿qué pasa si la inserción es con nulos (ya existe una fila con ID_N2 e ID_N3 a nulos)?

CARLOS@XE.localhost> INSERT INTO  PRUEBA01 VALUES (4,NULL,NULL);

1 fila creada.

CARLOS@XE.localhost> SELECT * FROM PRUEBA01 ORDER BY ID_N;

      ID_N      ID_N2      ID_N3
---------- ---------- ----------
         1          1          1
         2          2
         3
         4

En efecto, se permite dicha inserción: la ‘unicidad’ no tiene en cuenta los nulos.

Pero, ¿Y si sólo una de las columnas es nula, pero la otra no?

CARLOS@XE.localhost> INSERT INTO  PRUEBA01 VALUES (5,NULL,NULL);

1 fila creada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA01 VALUES (6,2,NULL);
INSERT INTO  PRUEBA01 VALUES (6,2,NULL)
*
ERROR en línea 1:
ORA-00001: restricción única (CARLOS.PRUEBA01UK) violada

Ahí lo tenemos: en cuanto una de las columnas no es nula se respeta la unicidad (aquí el nulo de la segunda columna funciona como un valor mas, lo que es un poco ‘contraintuitivo’). Si damos un paso más, veremos qué pasa si la columna nula es la primera y no la segunda:

CARLOS@XE.localhost> INSERT INTO  PRUEBA01 VALUES (6,NULL,2);

1 fila creada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA01 VALUES (7,NULL,2);
INSERT INTO  PRUEBA01 VALUES (7,NULL,2)
*
ERROR en línea 1:
ORA-00001: restricción única (CARLOS.PRUEBA01UK) violada

Lo mismo: la unicidad se respeta igual.

CARLOS@XE.localhost> SELECT * FROM PRUEBA01 ORDER BY ID_N;

      ID_N      ID_N2      ID_N3
---------- ---------- ----------
         1          1          1
         2          2
         3
         4
         5
         6                     2

6 filas seleccionadas.

La explicación está en que Oracle no indexa los valores nulos, y las unicidades (y aquí se incluyen también las ‘primary keys’) se implementan mediante índices (lo que no significa que ambas cosas sean lo mismo -la prueba es que dichos índices no tienen por qué ser ‘unique indexes’  y sí: se pueden definir ‘primary keys’ implementadas sobre índices ‘non-unique’-).

Por otra parte, las ‘foreign keys’ (claves foráneas) también presentan ‘zonas de sombra’ en lo que respecta al conocimiento que se tiene de ellas en el mundo de las bases de datos. Por ejemplo, aquí hemos hablado de la conveniencia de indexar las columnas que conforman la ‘foreign key’. Como en el caso de las ‘unique keys’, un índice y una restricción de clave foránea (‘constraint foreign key’) son cosas totalmente diferentes.

Una cosa muy importante -y que es menos conocida de lo que se debería- esque las ‘foreign keys’ pueden definirse no sólo contra las ‘primary keys’, sino que también pueden definirse contra ‘unique keys’.

Vamos a indagar sobre este aspecto creando una segunda tabla de pruebas con ‘foreign key’ sobre la primera creada. Aquí también crearemos las columnas de la restricción para que admitan nulos (‘NULL’).

CARLOS@XE.localhost> CREATE TABLE PRUEBA02 ( ID_N INTEGER NOT NULL PRIMARY KEY,
  2  ID_N2 INTEGER NULL,
  3  ID_N3 INTEGER NULL);

Tabla creada.

CARLOS@XE.localhost> ALTER TABLE PRUEBA02
  2  ADD CONSTRAINT PRUEBA02FK FOREIGN KEY (ID_N2, ID_N3)
  3  REFERENCES PRUEBA01 (ID_N2, ID_N3);

Tabla modificada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA02 VALUES (1,1,1);

1 fila creada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA02 VALUES (2,2,NULL);

1 fila creada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA02 VALUES (3,NULL,2);

1 fila creada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA02 VALUES (4,NULL,NULL);

1 fila creada.

CARLOS@XE.localhost> SELECT * FROM PRUEBA02 ORDER BY ID_N;

      ID_N      ID_N2      ID_N3
---------- ---------- ----------
         1          1          1
         2          2
         3                     2
         4

Otra vez todo parece coherente… pero es sólo un espejismo. Si efectuamos:

CARLOS@XE.localhost>  DELETE FROM PRUEBA01
  2  WHERE ID_N2 IS NULL AND ID_N3 IS NULL;

2 filas suprimidas.

Así que en la foreign key no se está verificando nada cuando los dos valores son NULL (si no fuera así habría saltado un error al borrar la filas ‘padres’ con NULL, NULL).

Pero es que hay más:

CARLOS@XE.localhost> SELECT * FROM PRUEBA01 ORDER BY ID_N;

      ID_N      ID_N2      ID_N3
---------- ---------- ----------
         1          1          1
         2          2
         6                     2

CARLOS@XE.localhost> INSERT INTO  PRUEBA02 VALUES (9,NULL,3);

1 fila creada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA02 VALUES (10,NULL,4);

1 fila creada.

Los valores con una sola columna NULL tampoco son verificados. Esto es: una columna NULL basta para que la foreign key no fuerce la integridad referencial de la fila.

Y esto es así sin importar el orden de las columnas:

CARLOS@XE.localhost> INSERT INTO  PRUEBA02 VALUES (5,3,NULL);

1 fila creada.

CARLOS@XE.localhost> INSERT INTO  PRUEBA02 VALUES (6,4,NULL);

1 fila creada.

En resumen: la integridad referencial de las filas sólo puede verificarse cuando TODAS las columnas que constituyen la foreign key tienen valores no-nulos:

CARLOS@XE.localhost> INSERT INTO  PRUEBA02 VALUES (11,3,1);
INSERT INTO  PRUEBA02 VALUES (11,3,1)
*
ERROR en línea 1:
ORA-02291: restricción de integridad (CARLOS.PRUEBA02FK) violada - clave principal no encontrada

CARLOS@XE.localhost> SELECT * FROM PRUEBA02 ORDER BY ID_N;

      ID_N      ID_N2      ID_N3
---------- ---------- ----------
         1          1          1
         2          2
         3                     2
         4
         5          3
         6          4
         9                     3
        10                     4

10 filas seleccionadas.

La explicación es diferente a la de las ‘unique keys’: aquí se verifica la integridad como si fuera una cláusula ‘WHERE’. Algo como: “PRUEBA02.ID_N2=PRUEBA01.ID_N2 AND PRUEBA02.ID_N3=PRUEBA01.ID_N3” y la lógica trivaluada es una vez más la que lo explica todo.

Hay que tener en cuenta que ocurriría lo mismo si las columnas de la tabla ‘padre’ con la ‘unique key’ fueran ‘NOT NULL’ (o que se definiese contra una ‘primary key’ compuesta): las columnas de la ‘foreign key’ de la tabla ‘hija’ a nulos harían imposible la verificación de integridad referencial para esas filas.

Saludos.

Carlos.

Anuncios

2 Responses to De Unique keys y Foreign keys.

  1. […] que Tom Kyte ha descubierto algo que yo ya sabía […]

  2. […] que Tom Kyte ha descubierto algo que yo ya sabía […]

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: