2 Tablas

En este capítulo haremos hincapié en las peculiaridades de SQLite con respecto a la creación, modificación y borrado de tablas. SQLite no sigue todas las especificaciones del SQL estándar, aunque las diferencias son mínimas, conviene tenerlas en cuenta a la hora de trabajar con este motor de bases de datos.

Crear tablas

A pesar de que SQLite acepta las definiciones de columna estándar en lo que respecta a los tipos, en realidad esas definiciones son sólo una declaración de intenciones, cualquier columna de una tabla SQLite puede contener cualquier tipo de datos de cualquier longitud. De hecho, sólo existen cinco tipos de datos de columnas:

• INTEGER: un valor entero con signo.
• REAL: un valor en coma flotante.
• TEXT: una cadena de texto.
• BLOB: datos abstractos.
• NULL: el valor nulo.

Cualquier otra definición de tipo de dato se convierte a uno de estos. Por ejemplo, VARCHAR(43) equivale a TEXT, INT(11) a INTEGER, etc.

Esto significa que en una columna definida como VARCHAR(10) podremos almacenar cadenas de más de 10 caracteres sin problemas.

Por otra parte, la definición hecha al crear la tabla puede ser ignorada si se insertan datos de otro tipo.

En este ejemplo vemos como no se producen errores al insertar columnas de tipos diferentes a los indicados:

sqlite> CREATE TABLE prueba2 (
   ...> texto TEXT,
   ...> numero INTEGER,
   ...> real REAL);
sqlite> INSERT INTO prueba2 VALUES(23, 43.2, "hola");
sqlite> SELECT * FROM prueba2;
23|43.2|hola
sqlite>

En cualquier caso, no conviene abusar de esta característica de SQLite, sobre todo porque es probable que los programas que trabajen con los datos almacenados en las tablas no sean tan tolerantes con estos cambios de tipo. En lo posible, intentaremos que los datos almacenados en una columna sean del tipo adecuado.

Identificadores de fila

SQLite siempre crea una columna de tipo entero autoincrementada como identificador de fila, si no se indica otra cosa.

Podemos referirnos a esa columna con los nombres "rowid", "oid" o "_rowid_". Por ejemplo:

sqlite> SELECT rowid,texto,numero,real FROM prueba2;
1|23|43.2|hola
sqlite>

En nuestras bases de datos podremos usar ese identificador como clave para relacionar tablas o para identificar filas para cualquier propósito.

Es posible obviar este comportamiento automático, indicando una columna de tipo INTEGER que sea una clave primaria. En realidad, lo que pasará en ese caso, es que esa columna será un alias para rowid:

sqlite> CREATE TABLE compra (id INTEGER PRIMARY KEY, nombre TEXT, cantidad INTEGER);
sqlite> INSERT INTO compra (nombre,cantidad) VALUES ('Tomates',2);
sqlite> INSERT INTO compra (id,nombre,cantidad) VALUES (4,'Patatas',5);
sqlite> SELECT * FROM compra;
1|Tomates|2
4|Patatas|5
sqlite> SELECT rowid,id FROM compra;
1|1
4|4
sqlite>

Tablas temporales

Es posible crear tablas temporales, para ello basta con añadir la palabra TEMP o TEMPORARY entre CREATE y TABLE. Las tablas temporales se destruyen cuando se cierra la conexión con la base de datos.

Por lo demás, podemos usar esas tablas igual que las otras, cuando los datos almacenados en ellas no tengan que ser preservados para su uso posterior, por ejemplo, en tablas para hacer cálculos, tablas intermedias entre procesos, etc.

sqlite> CREATE TEMP TABLE compratemp (id INTEGER PRIMARY KEY, nombre TEXT, cantidad INTEGER);
sqlite>

Copiar tablas

La sintaxis de CREATE TABLE nos permite crear tablas a partir de una consulta SELECT. Esta selección puede ser cualquier selección válida: de constantes, de una tabla, de parte de una tabla, de composiciones de tablas, etc.

sqlite> CREATE TEMP TABLE x AS SELECT DATE('now');
sqlite> CREATE TEMP TABLE y AS SELECT * FROM compra;
sqlite> CREATE TEMP TABLE z AS SELECT id,nombre FROM compra WHERE id=1;
sqlite> SELECT * FROM x;
2012-03-09
sqlite> SELECT * FROM y;
1|Tomates|2
4|Patatas|5
sqlite> SELECT * FROM z;
1|Tomates
sqlite>

Modificar tablas

La sentencia ALTER TABLE es una de las más limitadas con respecto al estándar en SQLite. Sólo es posible cambiar el nombre de la tabla y añadir columnas. No se pueden modificar columnas, ni su nombre ni el tipo de datos que contienen, ni eliminarlas, ni modificar sus restricciones.

sqlite> ALTER TABLE prueba2 RENAME TO prueba3;
sqlite> .tables
prueba    prueba3   telefono  usuario
sqlite> ALTER TABLE prueba3 ADD COLUMN datos BLOB;
sqlite>

Eliminar tablas

Eliminar tablas es sencillo, basta con usar la sentencia DROP TABLE. Esta sentencia elimina la tabla, pero no la borra del fichero de base de datos. Si pretendemos compactar ese fichero después de eliminar una tabla con muchos datos, podremos usar la sentencia VACUUM, que vaciará del fichero de base de datos todos aquellos que no pertenezcan a ninguna tabla, es decir, datos de tablas borradas o de registros eliminadas.

sqlite> DROP TABLE prueba3;
sqlite> .tables
prueba    telefono  usuario
sqlite> VACUUM;
sqlite>