CREATE TABLE

Definición de columna:

Nombre de tipo:

Restricciones de columna:

Restricciones de tabla:

Cláusula de clave foránea:

La sentencia CREATE TABLE se usa para crear una nueva tabla en una base de datos SQLite. Esta sentencia especifica los siguientes atributos de la nueva tabla:

• El nombre de la tabla.
• La base de datos en la que se crea la nueva tabla. Las tablas se pueden crear en la base de datos principal, en la temporal o en cualquier base de datos conectada.
• El nombre de cada columna en la tabla.
• En tipo declarado de cada columna de la tabla.
• Un valor por defecto o expresión para cada columna de la tabla.
• Una secuencia de clasificación por defecto para cada columna.
• Opcionalmente, una clave primaria para la tabla. Están soportadas tabto las claves primarias de una columna como las compuestas (múltiples columnas).
• Un conjunto de restricciones SQL para cada tabla. SQLite soporta las restricciones UNIQUE, NOT NULL, CHECK y FOREIGN KEY.

Cada sentencia CREATE TABLE debe especificar un nombre para la nueva tabla. Los nombres de tabla que comiencen con "sqlite_" están reservados para uso interno. Es un error intentar crear una tabla cuyo nombre comience con "sqlite_".

Si se especifica un <nombre-base-datos>, este debe ser "main", "temp", o el nombre de una base de datos conectada. En este caso, la nueva tabla se crea en la base de datos indicada. Si la palabra clave TEMP o TEMPORARY aparece entre CREATE y TABLE entonces la nueva tabla se crea en la base de datos temporal. Es un error especificar ambos, un <nombre-base-datos> y la palabra clave TEMP o TEMPORARY, a menos que <nombre-base-datos> sea "temp". Si no se especifica un nombre de base de datos y la palabra calve TEMP no está presente, entonces la tabla se crea en la base de datos principal.

Por lo general, es un error intentar crear una nueva tabla en una base de datos que ya contenga una tabla, índice o vista con el mismo nombre. Sin embargo, si se especifica la cláusula IF NOT EXISTS como una parte de la sentencia CREATE TABLE y ya existe una tabla o vista con el mismo nombre, la sentencia CREATE TABLE sencillamente no tiene efecto (y no se devuelve nigún mensaje de error). Se sigue retornando un error si la tabla no puede ser creada porque existe un índice, aunque se haya especificado la cláusula IF NOT EXISTS.

No se produce un error al crear la tabla con el mismo nombre que un disparador (trigger) existente.

Las tablas se eliminan usando la sentencia DROP TABLE.

Sentencias CREATE TABLE ... AS SELECT

Una sentencia CREATE TABLE ... AS SELECT crea y rellena una tabla de base de datos con los resultados de una sentencia SELECT. La tabla tiene el mismo número de columnas que las filas devueltas por la sentencia SELECT. El nombre de cada columna es el mismo que el nombre de la columna correspondiente en el conjunto de resultados de la sentencia SELECT. El tipo decarador de cada columna se determina por la afinidad de expresión de la expresión correspondiente en el conjunto de resultados de la sentencia SELECT, del siguiente modo:

Una tabla creada usando CREATE TABLE AS no tiene clave primaria ni restricciones de ningún tipo. El valor por defecto de cada columna es NULL. La secuencia de clasificación por defecto para cada columna de la nueva tabla es BINARY.

Las tablas creadas usando CREATE TABLE AS se rellenan inicialmente con las filas de datos retornadas por la sentencia SELECT. A cada fila se le asignan valores de identificación (rowid) crecientes y contiguos, comenzando en 1, en el orden en que fueron retornados por la sentencia SELECT.

Definiciones de columna

Salvo cuando se usa la forma CREATE TABLE ... AS SELECT, la sentencia CREATE TABLE incluye uno o más definiciones de columna, seguidas opcionalmente por una lista de restricciones de tabla. Cada definición de columna consiste en un nombre de columna, seguido opcionalemnte por una declaración de tipo de columna, y después una o más restricciones de columna. Dentro de la definión de "restricciones de columna" se incluyen las cláusulas COLLATE y DEFAULT, aunque no se trata realmente de restricciones en el sentido de que no restringen los datos que la tabla puede contener. Las otras restricciones - NOT NULL, CHECK, UNIQUE, PRIMARY KEY y FOREIGN KEY - imponen limitaciones en los datos de las tablas, y se describen bajo las Restricciones de Datos SQL, más abajo.

Al contrario que muchas bases de datos SQL, SQLite no restringe el tipo de datos que pueden ser insertados en una columna al tipo declarado para la columna. En lugar de eso, SQLite usa asignación de tipos dinámico. El tipo declarado para una columna se usa sólo para determinar la afinidad de tipo de la columna.

La cláusula DEFAULT especifica el valor por defecto a usar para la columna si no se especifica explícitamente un valor por el usuario cuando hace un INSERT. Si no hay una cláusula DEFAULT explícita en la definición de una columna, entonces el valor por defecto para la columna es NULL. Una cláusula explícita DEFAULT puede especificar que el valor por defecto es NULL, una constante de cadena, una constante blob, un número con signo o cualquier expresión constante entre paréntesis. Un valor por defecto explícito puede ser también una de las palabras clave especiales, independientes del tipo, CURRENT_TIME, CURRENT_DATE o CURRENT_TIMESTAMP. Desde el punto de vista de la cláusula DEFAULT, una expresión se considera constante si no contiene ninguna subconsulta o cadenas constantes entre dobles comillas.

Cada vez que se inserta una fila en una tabla mediante una sentencia INSERT que no proporcione valores explícitos para todas las columnas de la tabla, los valores almacenados en la nueva fila se determinan mediante sus valores por defecto, como sigue:

• Si el valor por defecto de la columna es una constante NULL, texto, blob o número con signo, entonces el valor se usa directamente en la nueva fila.
• Si el valor por defecto de la columna es una expresión entre paréntesis, entonces la expresión es evaluada cada vez que se inserta una fila y el resultado se usa en la nueva fila.
• Si el valor por defecto de una columna es CURRENT_TIME, CURRENT_DATE o CURRENT_TIMESTAMP, entonces el valor usado en la nueva fila es una representación en texto de la fecha y/o hora UTC. Para CURRENT_TIME, el formato del valor es "HH:MM:SS". Para CURRENT_DATE, "YYYY-MM-DD". El formato para CURRENT_TIMESTAMP es "YYYY-MM-DD HH:MM:SS".

La cláusula COLLATE especifica el nimbre de una secuencia de clasificación para usar por defecto para la columna. Si no se especifica la cláusula COLLATE, la secuencia de clasificación por defecto es BINARY.

El número de columnas en una tabla está limitado por el parámetro de compilación SQLITE_MAX_COLUMN. Una fila de una tabla no puede almacenar más de SQLITE_MAX_LENGTH bytes de datos. Ambos límites pueden ser disminuídos durante la ejecución usando la función del API C sqlite3_limit().

Restricciones de datos SQL

Cada tabla en SQLite debe tener al menos una PRIMARY KEY. Si se añaden las palabras clave PRIMARY KEY a una definición de columna, entonces la clave primaria de la tabla consiste en esa columna. O, si se especifica una cláusula PRIMARY KEY como una restricción de tabla, entonces la clave primaria de la tabla consiste en la lista de columnas especificada como la parte de cláusula PRIMARY KEY. Si hay más de una cláusula PRIMARY KEY en una sentencia CREATE TABLE, es un error.

Si una tabla tiene una clave primaria de una única columna, y el tipo declarado para esa columna es "INTEGER", entonces la columna será conocida como una INTEGER PRIMARY KEY. Más abajo hay una descripción de las propiedades especiales y los comportamientos asociados con una INTEGER PRIMARY KEY.

Cada fila en una tabla con una clave primaria debe estar relacionada con una combinación única de valores en sus columnas de clave primaria. Con el fin de determinar el carácter único de los valores de clave primaria, los valores NULL se consideran distintos de todos los demás valores, incluyendo otros NULLs. Si una sentencia INSERT o UPDATE intenta modificar el contenido de la tabla de modo que dos o más filas tengan valores idénticos de clave primaria, se trata de una violación de restricción. De acuerdo con el estándar de SQL, PRIMARY KEY implica siempre NOT NULL. Desafortunadamente, debido a un error de codificación, esto no es así en SQLite. A no ser que la columna sea un INTEGER PRIMARY KEY SQLite permite valores NULL en una columna PRIMARY KEY. Se podría modificar SQLite para que se ajustase al estándar (y puede que se haga en el futuro), pero para cuando esto se descubrió, SQLite era de uso tan general que se temía romper la compatibilidad de código si se solucionaba este problema. De modo que por ahora se ha optado por continuar permitiendo valores NULL en columna PRIMARY KEY. Sin embargo, los desarrolladores deben tener en cuenta que SQLite se puede modificar en el futuro para amoldarse al SQL estándar y en consecuencia elaborar nuevos programas.

Una restricción UNIQUE es similar a una PRIMARY KEY, excepto que una tabla puede tene cualquier número de restricciones UNIQUE. Para cada restricción UNIQUE de una tabla, cada fila debe estar relacionada con una combinación única de valores en las columnas indentificadas con la restricción UNIQUE. Al igual que con las restricciones PRIMARY KEY, para los propósitos de restricciones UNIQUE los valores NULL se consideran distintos de cualquier otro valor (incluidos otros NULLs). Si una sentencia INSERT o UPDATE intenta modificar el contenido de la tabla de modo que para dos o más filas resulten valores idénticos en un conjunto de columnas que estén sujetos a una restricción UNIQUE, se produce una violación de restricción.

Para las columnas con INTEGER PRIMARY KEY, se implementan tanto las restricciones UNIQUE como PRIMARY KEY mediante la creación de un índice en la base de datos (de la misma forma que lo hace una sentencia CREATE UNIQUE INDEX. Este índice se usa para optimizar consultas igual que cualquier otro índice en la base de datos. Como resultado, a menudo no se obtiene ninguna ventaja (pero si importantes gastos) de la creación de un índice en un conjunto de columnas que ya estén colectivamente presentes en una restricción UNIQUE o PRIMARY KEY.

Se puede añadir una restricción CHECK a una definición de columna o especificada como una restricción de tabla. En la práctica no hay diferencia. Cada vez que se inserta o se actualiza una fila en la tabla, se evalúa la expresión asociada a cada restricción CHECK y se convierte a un valor NUMERIC del mismo modo que en una expresión CAST. Si el resultado es cero (un valor entero 0 o real 0.0), se produce una violación de restricción. Si la expresión CHECK se evalúa como NULL, o cualquier otro valor distinto de cero, no hay violación de restricción.

Las restricciones CHECK están soportadas desde la versión 3.3.0. Antes de la versión 3.3.0, las restricciones CHECK se parsean pero no se tienen en cuenta.

Una restricción NOT NULL sólo puede estar asociada a una definición de columna, no se puede especificar como una restricción de tabla. Esto no es sorprendente, una restricción NOT NULL indica que la columna asociada no puede contener el valor NULL. Un intento de asignar NULL a la columna cuando se inserta co se actualiza una columna producirá una violación de restricción.

La forma exacta en que se trata una violación de restricción se determina mediante el algoritmo de resolución de conflictos. Cada restricción PRIMARY KEY, UNIQUE, NOT NULL y CHECK tiene un algoritmo de resolución de conflicto por defecto. A las restricciones PRIMARY KEY, UNIQUE y NOT NULL se les puede asignar un algoritmo de resolución de conflictos explícitamente mediante la inclusión de una cláusula de conflicto en sus definiciones. O, si no se incluye una cláusula de definición de restricción o es una restricción CHECK, el algoritmo de resolución de conflicto por defecto es ABORT. Distintas destricciones dentro de la misma tabla pueden tener diferentes algoritmos de resolución de conflictos por defecto. Ver la sección titulada ON CONFLICT para información adicional.

ROWIDs y la INTEGER PRIMARY KEY

Cada fila en cada tabla SQLite tiene una clave única entera con signo de 64-bit que identifica la fila dentro de la tabla. Este entero se suele llamar el "rowid". El valor rowid puede ser accedido usando uno de sus nombres especiales independientes del tipo "rowid", "oid", o "_rowid_" en el lugar de un nombre de columna. Si la tabla contiene una columna definida por el usuario con el nombre "rowid", "oid" o "_rowid_", entonces ese nombre se refiere explícitamente a la columna declarada y no se puede usar para recuperare el valor entero rowid.

Los datos de cada tabla en SQLite se almacenan en una estructura B-Tree que contiene una entrada para cada fila de la tabla, usanod el valor rowid como clave. Esto significa que recuperar u ordenar registros por rowid es rápido. Buscar un registro con un rowid específico, o todos los registros con rowids dentro de un rango especificado es alrededor de dos veces más rápido que una búsqueda similar hecha especificando cualquier otra PRIMARY KEY o valor indexado.

Con una excepción, si una tabla tiene una clave primaria que consiste en una única columna, y el tipo declarado para esa columna es "INTEGER" en cualquier combinación de mayúsculas y minúsculas, entonces la columna se convierte en un alias para rowid. Se refiere esa columna como una "clave primaria entera". Una columna PRIMARY KEY sólo se convierte en una clave primaria entera si el nombre del tipo declarado es exactamente "INTEGER". Otros nombres de tipos enteros como "INT" o "BIGINT" o "SHORT INTEGER" o "UNSIGNED INTEGER" hacen que la columna de clave primaria se comporte como una columna de tabla normal con afinidad de entero y un índice único, no como un alias para el rowid.

La excepción mencionada es que si la declaración de una columna con el tipo declarado "INTEGER" incluye una cláusula "PRIMARY KEY DESC", no se convierte en un alias para rowid y no se clasifica como una clave entera primaria. Esta peculiaridad no es por diseño. Se debe a un error en las primeras versiones de SQLite. Sin embargo, corregir este error puede resultar en graves incompatibilidades hacia atrás. Los desarrolladores de SQLite opinan que este comportamiento ridículo en un caso marginal es mejor que una ruptura de compatibilidad, de modo que se mantiene el comportamiento original. Esto significa que las tres siguientes declaraciones de tabla hacen que la columna "x" sea un alias para rowid (una clave primaria entera):

• CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
• CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
• CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));

Pero la siguiente declaración no hace que "x" se convierta en un alias para rowid:

• CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

Los valores de rowid pueden ser modificados usando sentencias UPDATE del mismo modo que cualquier valor de otra columna, ya sea usando uno de los alias integrados ("rowid", "oid" o "_rowid_") o usando un alias creado por una clave primaria entera. Similarmente, una sentencia INSERT puede proporcionar un valor para usar como rowid para cada fila insertada. Al contrario que las columnas normales SQLite, una clave primaria entera o una columna rowid debe contener valores enteros. Las claves primarias enteras o las columnas rowid no pueden contener valores en punto flotante, cadenas, BLOBs o NULLs.

Si una sentencia UPDATE intenta asignar un valor NULL, blob, cadena, o real que no se pueda convertir a entero sin pérdida, a una clave primaria entera o a una columna rowid, se produce un error "tipo de dato erróneo" y la sentencia es abortada. Si una sentencia INSERT intenta insertar un valor blob, una cadena, o un valor real que no se pueda convertir a entero sin pérdida, en una columa de clave primaria entera o rowid, se produce un error "tipo de dato erróneo" y la sentencia se aborta.

Si una sentencia INSERT intenta insertar un valor NULL en un rowid o en una columa de clave primaria, el sistema elige automáticamente un valor entero para usarlo como rowid. Una explicación detallada de como se hace esto se muestra en otro lugar.