6 Restricciones de columna

Ya hemos usado algunas, y ahora las repasaremos y veremos el resto.

Generalmente, las restricciones limitan los valores de las columnas, de modo que se asjuste a ciertas normas.

DEFAULT

La restricción DEFAULT establece un valor por defecto para una columna. Ese valor se usará cuando se inserten filas sin especificar un valor concreto para la columna:

sqlite> CREATE TABLE t(
   ...> nombre TEXT DEFAULT "desconocido",
   ...> cantidad INTEGER DEFAULT 0
   ...> );
sqlite> INSERT INTO t(nombre) VALUES("Fernando");
sqlite> INSERT INTO t(cantidad) VALUES(10);
sqlite> SELECT * FROM t;
nombre|cantidad
Fernando|0
desconocido|10
sqlite>

NOT NULL

La restricción NOT NULL impide que una columna pueda tomar el valor NULL. Cualquier intento de asignar un valor nulo a la columna producirá un error:

sqlite> CREATE TABLE t1 (c1 TEXT NOT NULL);
sqlite> INSERT INTO t1 (c1) VALUES(NULL);
Error: t1.c1 may not be NULL
sqlite>

UNIQUE

La restricción UNIQUE impide que existan dos filas con el mismo valor para la columna indicada.

sqlite> CREATE TABLE t2 (c1 TEXT UNIQUE, c2 INTEGER);
sqlite> INSERT INTO t2(c1,c2) VALUES("Patatas", 2);
sqlite> INSERT INTO t2(c1,c2) VALUES("Tomates", 6);
sqlite> INSERT INTO t2(c1,c2) VALUES("Pimientos", 4);
sqlite> INSERT INTO t2(c1,c2) VALUES("Tomates", 2);
Error: column c1 is not unique
sqlite>

Una restricción UNIQUE implica la creación de un índice sobre la columna a la que se aplica.

Una columna con esta restricción puede contener el valor NULL.

sqlite> INSERT INTO t2(c1,c2) VALUES(NULL, 9);
sqlite> INSERT INTO t2(c1,c2) VALUES(NULL, 15);
sqlite> .nullvalue "NULL"
sqlite> SELECT * FROM t2;
c1|c2
Patatas|2
Tomates|6
Pimientos|4
TOmates|2
NULL|9
NULL|15
sqlite>
Nota:

El comando de consola .nullvalue permite definir una cadena para mostrar valores NULL.

PRIMARY KEY

La restricción PRIMARY KEY limitan los valores de la columna de varias formas:

  • Se crea un índice sobre la columna a la que se aplica la restricción.
  • Se aplica la misma restricción que con UNIQUE, es decir, no pueden existir dos filas con el mismo valor para una columna a la que se aplica la restricción.
  • Se aplica la restricción NOT NULL.

Además, en el caso de SQLite, si la columna es entera, se asume la cláusula AUTOINCREMENT.

sqlite> CREATE TABLE t3 (id INTEGER PRIMARY KEY, nombre TEXT);
sqlite> INSERT INTO t3 (nombre) VALUES("Pedro");
sqlite> INSERT INTO t3 (nombre) VALUES("Antonio");
sqlite> INSERT INTO t3 (nombre) VALUES("Carlos");
sqlite> SELECT * FROM t3;
id|nombre
1|Pedro
2|Antonio
3|Carlos
sqlite>

FOREIGN KEY

Mediante la restricción FOREIGN KEY se vincula el valor de una columna con el de otra en otra tabla diferente, llamada tabla padre. Es decir, una columna con esta restricción sólo puede tomar valores que existan en la columna vinculada de la tabla padre. A esa columna se le denomina clave foránea.

Para que SQLite soporte claves foráneas debe estár compilado con las opciones adecuadas, y además se debe activar el PRAGMA foreign_keys:

sqlite> PRAGMA foreign_keys = ON;
sqlite>

Crearemos ahora dos tablas vinculadas con una clave foránea:

sqlite> CREATE TABLE artista(
   ...> idartista INTEGER PRIMARY KEY,
   ...> nombre TEXT
   ...> );
sqlite> CREATE TABLE cancion(
   ...> idcancion INTEGER PRIMARY KEY,
   ...> titulo TEXT,
   ...> idartista INTEGER REFERENCES artista(idartista)
   ...> );
sqlite> INSERT INTO artista (nombre) VALUES("Mike Oldfield");
sqlite> INSERT INTO artista (nombre) VALUES("The Beatles");
sqlite> SELECT * FROM artista;
idartista|nombre
1|Mike Oldfield
2|The Beatles
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Tubular Bells", 1);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Yellow Submarine", 2);
sqlite> INSERT INTO cancion (titulo,idtista) VALUES("Get Back", 2);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Yesterday", 2);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Sympathy For The Devil", 3);
Error: foreign key constraint failed
sqlite> SELECT * FROM cancion;
idcancion|titulo|idartista
1|Tubular Bells|1
2|Yellow Submarine|2
3|Get Back|2
4|Yesterday|2
sqlite>

En este ejemplo, cada canción debe tener un autor, que se indica añadiendo una clave de la tabla artista. La restricción de clave foránea impide asignar un valor de identificador de artista que no exista.

Acciones ON UPDATE y ON DELETE

Pero, ¿qué pasa si borramos un artista o modificamos su identificador? Esto puede provocar una violación de restricción de clave foránea. De hecho, eso es lo que ocurrirá tal como están creadas las tablas anteriores:

  • No podremos eliminar filas de la tabla de artistas si existen canciones que usen su identificador de artista.
  • No podremos modificar el identificador de artista si existen canciones que usen ese identificador.

SQL permite definir acciones que se tomarán automáticamente en el caso de que se eliminen o modifiquen valores que se usen como claves foráneas en otras tablas. Para ello se añaden las cláusulas opcionales ON DELETE y ON UPDATE. Para cada una se puede tomar una de las siguientes acciones:

NO ACTION
No hacer nada, es decir, dejar las claves foráneas sin modificar. Esto, evidentemente, poduce violaciones de la restricción de clave foránea. Se supone que el usuario o la aplicación que usa la base de datos tomará las acciones necesarias para mantener las restricciones.
RESTRICT
Indica que está prohibido hacer la modificación o borrado de la clave, si eso va a provocar una violación de restricción. Esto se parece a la acción por defecto, pero la diferencia es que no se espera a que termine la ejecución de la sentencia o de la transacción.
SET NULL
Cualquier columna en la tabla hija que haga referencia a la clave modificada o borrada se le asignará el valor NULL.
SET DEFAULT
Cualquier columna en la tabla hija que haga referencia a la clave modificada o borrada se le asignará el valor por defecto.
CASCADE
La acción se propaga a las claves de las tablas hijas. Si es una modificación, los valores de la columna en la tabla hija se modifican al mismo valor. Si es un borrado, las filas de las tablas hijas se borran.

Borremos las tablas anteriores y empecemos de nuevo:

sqlite> DROP TABLE cancion;
sqlite> DROP TABLE artista;
sqlite> CREATE TABLE artista(
   ...> idartista INTEGER PRIMARY KEY,
   ...> nombre TEXT
   ...> );
sqlite> CREATE TABLE cancion(
   ...> idcancion INTEGER PRIMARY KEY,
   ...> titulo TEXT,
   ...> idartista INTEGER REFERENCES artista(idartista) ON UPDATE CASCADE ON DELETE SET NULL
   ...> );
sqlite> INSERT INTO artista (nombre) VALUES("Mike Oldfield");
sqlite> INSERT INTO artista (nombre) VALUES("The Beatles");
sqlite> SELECT * FROM artista;
idartista|nombre
1|Mike Oldfield
2|The Beatles
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Tubular Bells", 1);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Yellow Submarine", 2);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Get Back", 2);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Yesterday", 2);
sqlite> SELECT * FROM cancion;
idcancion|titulo|idartista
1|Tubular Bells|1
2|Yellow Submarine|2
3|Get Back|2
4|Yesterday|2
sqlite> UPDATE artista SET idartista=3 WHERE idartista=1;
sqlite> SELECT * FROM cancion;
idcancion|titulo|idartista
1|Tubular Bells|3
2|Yellow Submarine|2
3|Get Back|2
4|Yesterday|2
sqlite> DELETE FROM artista WHERE idartista=3;
sqlite> SELECT * FROM artista;
idartista|nombre
2|The Beatles
sqlite> SELECT * FROM cancion;
idcancion|titulo|idartista
1|Tubular Bells|NULL
2|Yellow Submarine|2
3|Get Back|2
4|Yesterday|2
sqlite>

Vemos que las modificaciones en el identificador de artista se propagan a la tabla hija, y que cuando se borran artistas, las claves en la tabla hija toman el valor NULL.

CHECK

La restricción CHECK se usa para verificar si el valor de una columna está en el dominio especificado. Esto impide que se asignen valores no válidos a determinadas columnas.

sqlite> CREATE TABLE t4(
   ...> nombre TEXT,
   ...> cantidad INTEGER CHECK(cantidad>0)
   ...> );
sqlite> INSERT INTO t4(nombre,cantidad) VALUES("Tornillo M3", 32);
sqlite> INSERT INTO t4(nombre,cantidad) VALUES("Tornillo M4", 12);
sqlite> INSERT INTO t4(nombre,cantidad) VALUES("Tornillo M5", 18);
sqlite> INSERT INTO t4(nombre,cantidad) VALUES("Tornillo M6", 0);
Error: constraint failed
sqlite>

La verificación también se hace al actualizar las filas:

sqlite> UPDATE t4 SET cantidad=cantidad-15;
Error: constraint failed
sqlite> SELECT * FROM t4;
nombre|cantidad
Tornillo M3|32
Tornillo M4|12
Tornillo M5|18
sqlite>