Definición del esquema de una tabla

En el comando CREATE TABLE tenemos la opción de definir el esquema de una tabla de la siguiente forma:

... ( definición_campo [, definición_campo] [, limitaciones_tabla] )

donde los corchetes, [], expresan elementos opcionales. Esto es, en la definición de la tabla debe aparecer al menos un campo, si existen más, éstos están separados entre si con comas y pueden aparecer o no limitaciones de tabla a los registros.

La definición de un campo viene establecida por:

nombre_campo [tipo_dato] [limitaciones_campo]

Esto es, en la definición del campo, el único elemento obligatorio es el nombre del campo. En SQLite es válido el comando:

CREATE TABLE personas ( nombre, apellido_1, apellido_2);

Sin embargo, observando la documentación oficial:

lo normal es definir el nombre del campo, junto con el tipo de dato.

Conviene recordar que el ordenador almacena la información digitalmente (con 0 y 1), así que es importante conocer como interpretar esos ceros y unos. Por ejemplo, en un campo 'nota' me interesa que la información se interprete  como números si pretendo calcular medias, mientras que en  los campos de nuestra tabla, la información debe interpretarse como texto:

CREATE TABLE personas (

nombre TEXT,

apellido_1 TEXT,

apellido_2 TEXT);

Desconozco las razones exactas por las que SQLite permite definir un campo NULL, como en el primer caso. Pienso que los desarrolladores de SQLite quieren desarrollar un sistema amigable que permita una fácil transposición de las bases de datos de otros gestores al suyo, de tal suerte que reconoce tipos de datos definidos en otros gestores de base de datos, por ejemplo un varchar(10) -MYSQL- lo reconoce como text; los datos null quizás sean interesantes para poder transponer algún tipo de dato de otro gestor de datos, no implementado.

Como se observa en la definición de una columna pueden aparecer unas limitaciones (constraints), que deben verificarse al introducir los datos:

- NOT NULL (impide que se inserte un registro con ese campo vacío, por ejemplo, toda persona tendrá un nombre).
- DEFAULT  valor (si se inserta un registro con ese campo vacío, SQLite le asigna el valor establecido por defecto).
- UNIQUE ( impide que se inserte un registro con el mismo valor en ese campo que otro registro previo. Por ejemplo, si un campo fuese el DNI, sería lamentable que nuestra base de datos aceptará dos personas con el mismo DNI).
- PRIMARY KEY (sólo puede existir un campo por tabla con esta característica. En nuestro contexto, tiene la misma función que UNIQUE)

 Así, nuestra tabla podría crearse con el siguiente comando:

CREATE TABLE personas (

nombre TEXT NOT NULL,

apellido_1 TEXT NOT NULL,

apellido_2 TEXT

);

 

Observar que apellido_2 no se establece como NOT NULL, porque en muchos países sólo acompañan el nombre con un apellido.

NOT NULL, UNIQUE y PRIMARY KEY pueden utilizarse junto a la claúsula ON CONFLICT que específica la acción a realizar cuando las limitaciones de campo no son respetadas. Por ejemplo,

 dni TEXT UNIQUE ON CONFLICT REPLACE

introduce un campo dni, del tipo texto único. Si tratamos de añadir un nuevo registro con un dni ya existente en nuestra tabla, la orden se ejecuta 'borrando' el registro previo.

Otras posibilidades de ON CONFLICT son: ROLLBACK, ABORT, FAIL e IGNORE.

 

Las limitaciones las podemos establecer para un determinado campo, pero también existe la posibilidad de establecer limitaciones de tablas, para varios campos.

Por ejemplo, imaginemos que estamos realizando una base de datos de nuestra discoteca. En una tabla 'disco' podríamos almacenar el nombre del disco y el artista. Establecer el campo artista como UNIQUE es un error, pues un mismo artista puede tener varios discos distintos y, por otro lado, un disco puede tener varios artistas. Únicamente tengo la certeza de que estoy 'duplicando' información si intento añadir un registro con un artista y un disco, los cuáles ya existen en un mismo registro previo.

Para conseguir que SQLite no permita duplicar la información en este caso, debemos establecer limitaciones de tabla - no de campo- a los registros, de la siguiente forma:

CREATE TABLE discos (

artista TEXT,

album TEXT,

[otros campos],

UNIQUE (artista, album)

);