Referencia de CREATE TABLE: tipos y restricciones en MySQL, PostgreSQL y SQLite

Lo difícil del DDL no es la sintaxis, son los dialectos

CREATE TABLE es una sentencia sencilla. Si aun así hay que consultarla cada vez, es porque la misma columna se escribe de forma distinta en MySQL, PostgreSQL y SQLite. «El DDL funcionaba en MySQL pero AUTO_INCREMENT da error de sintaxis en PostgreSQL». «La restricción CHECK que pasaba en local con SQLite nunca se aplicó en producción». Casi todos los sustos con DDL se reducen a diferencias de dialecto.

Esta es una referencia construida sobre tablas comparativas de tipos y restricciones. No trata del diseño del esquema (normalización, estrategia de índices), sino de cómo escribir un diseño que ya has decidido.

La estructura de un CREATE TABLE

Una definición mínima que funciona en las tres bases de datos:

CREATE TABLE users (
    id          INTEGER PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(100),
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Cada columna sigue el orden nombre → tipo → restricciones. Las restricciones son de dos clases: de columna (después del tipo, como arriba) y de tabla (después de todas las columnas). Las claves primarias compuestas y las claves únicas compuestas solo pueden expresarse como restricciones de tabla.

CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity   INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id),                      -- clave primaria compuesta
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
);

Tabla comparativa de tipos

Uso	MySQL	PostgreSQL	SQLite
Entero (general)	INT	INTEGER	INTEGER
Entero (grande)	BIGINT	BIGINT	INTEGER
Importes / decimales exactos	DECIMAL(10,2)	NUMERIC(10,2)	NUMERIC
Cadena corta	VARCHAR(255)	VARCHAR(255) o TEXT	TEXT
Texto largo	TEXT	TEXT	TEXT
Booleano	BOOLEAN (en realidad TINYINT(1))	BOOLEAN	INTEGER (0/1)
Solo fecha	DATE	DATE	TEXT
Fecha y hora	DATETIME	TIMESTAMPTZ	TEXT
JSON	JSON	JSONB	TEXT
UUID	CHAR(36) o BINARY(16)	UUID	TEXT

Hay tres ideas que conviene interiorizar.

SQLite apenas tiene tipos. SQLite usa tipado dinámico a nivel de valor: el tipo declarado en la columna es solo una «afinidad de tipo». Escribir BOOLEAN o DATETIME no da error, pero el valor se guarda internamente como número o texto. Por eso el destino SQLite siempre necesita su propia conversión de dialecto.

En PostgreSQL, VARCHAR(n) no aporta rendimiento. TEXT y VARCHAR comparten implementación y el límite de longitud se comporta casi como una restricción CHECK. A diferencia de MySQL, más corto no significa más rápido: si no hay un límite real de negocio, TEXT basta.

Nunca guardes dinero en FLOAT / DOUBLE. El punto flotante binario no representa exactamente los decimales, así que los totales se desvían. Usa DECIMAL / NUMERIC.

El autoincremento son tres funcionalidades distintas

Es la mayor diferencia de dialecto en el DDL cotidiano.

Base de datos	Cómo se escribe
MySQL	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
PostgreSQL (recomendado)	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
PostgreSQL (heredado)	id SERIAL PRIMARY KEY
SQLite	id INTEGER PRIMARY KEY

En PostgreSQL, prefiere IDENTITY a SERIAL. SERIAL es un pseudotipo que significa «INTEGER más una secuencia creada por detrás». Desde PostgreSQL 10 existe el estándar SQL GENERATED ... AS IDENTITY, donde la secuencia pertenece correctamente a la tabla, lo que simplifica la limpieza al eliminar la tabla y la gestión de permisos.

En SQLite normalmente no quieres AUTOINCREMENT. Declarar INTEGER PRIMARY KEY ya convierte la columna en un alias del rowid interno y asigna el valor automáticamente al omitirlo. La palabra clave AUTOINCREMENT solo añade la garantía de que un ID borrado nunca se reutilice, a costa de escrituras adicionales en la tabla sqlite_sequence. La documentación oficial recomienda evitarla salvo que necesites esa garantía. Además, AUTOINCREMENT fuera de INTEGER PRIMARY KEY es un error de sintaxis.

Tabla comparativa de restricciones

Restricción	Significado	Advertencia por dialecto
PRIMARY KEY	Única y no nula	Implícitamente NOT NULL; una por tabla
NOT NULL	Rechaza NULL	Sin diferencias relevantes
UNIQUE	Rechaza valores duplicados	Los NULL no cuentan como duplicados: varias filas pueden ser NULL
DEFAULT	Valor si se omite	En MySQL, los valores por defecto con expresión en TEXT/JSON requieren paréntesis (8.0.13+)
CHECK	Solo permite valores que cumplan la condición	Se analiza pero se ignora en silencio antes de MySQL 8.0.16
FOREIGN KEY	Integridad referencial	Desactivada por defecto en SQLite; ignorada por motores MySQL distintos de InnoDB

Dos de ellas provocan la mayoría de los incidentes reales.

El CHECK de MySQL. Antes de MySQL 8.0.16, las cláusulas CHECK se aceptaban sintácticamente y nunca se aplicaban. Es un fallo silencioso: el DDL dice que la regla existe y los datos inválidos siguen entrando hasta que alguien lo detecta meses después. Si el esquema puede acabar en un servidor antiguo, considera la validación en la aplicación como la fuente de verdad.

Las claves foráneas de SQLite. Por compatibilidad hacia atrás, SQLite no aplica las claves foráneas por defecto, y la opción es por conexión, así que hay que ejecutarla cada vez que se conecta:

PRAGMA foreign_keys = ON;

Si se olvida, se insertan sin problema filas que referencian a un padre inexistente aunque el REFERENCES esté en el DDL. Con SQLite en local y PostgreSQL en producción, esto aparece como una integridad que solo se rompe en las máquinas de desarrollo.

Claves foráneas: ON DELETE / ON UPDATE

Hay cuatro comportamientos cuando la fila referenciada se elimina o se actualiza:

Opción	Comportamiento
RESTRICT / NO ACTION	Rechaza borrar/actualizar el padre mientras existan hijos (por defecto)
CASCADE	Elimina/actualiza las filas hijas junto con el padre
SET NULL	Pone a NULL la columna de clave foránea (la columna debe admitir NULL)
SET DEFAULT	Asigna el valor por defecto al hijo (no soportado por MySQL/InnoDB)

-- Al borrar un usuario se eliminan sus posts, pero los comentarios quedan sin autor
CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE comments (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users (id) ON DELETE SET NULL  -- no puede ser NOT NULL
);

CASCADE es cómodo, pero su alcance es invisible si no se lee el DDL. No lo uses en tablas que deban sobrevivir al padre: registros de auditoría, líneas de pedido, cualquier dato financiero.

Cómo elegir las columnas de fecha y hora

Objetivo	MySQL	PostgreSQL
Rellenar la fecha de creación	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	TIMESTAMPTZ DEFAULT NOW()
Refrescar la fecha de actualización	TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Requiere un trigger: no hay equivalente a nivel de columna

ON UPDATE CURRENT_TIMESTAMP es exclusivo de MySQL. Migrar a PostgreSQL implica sustituirlo por un trigger BEFORE UPDATE, así que copiar el esquema tal cual deja updated_at congelado en el valor de inserción sin ningún error que lo delate.

Al elegir un tipo de fecha en MySQL, recuerda que TIMESTAMP solo cubre de 1970 a 2038 y se convierte con la zona horaria de la sesión, mientras que DATETIME tiene un rango mayor y no aplica conversión. En PostgreSQL, TIMESTAMPTZ es la opción más segura frente a un TIMESTAMP sin zona.

En MySQL el juego de caracteres es utf8mb4, no utf8

Por razones históricas, el utf8 de MySQL es una codificación distinta limitada a tres bytes por carácter, por lo que los emojis y algunos caracteres CJK provocan Incorrect string value. Especifica utf8mb4 de forma explícita:

CREATE TABLE posts (
    id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    body  TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

PostgreSQL y SQLite gestionan UTF-8 a nivel de base de datos o de archivo, así que la definición de la tabla no necesita indicar nada.

Del diseño al DDL y al diagrama ER

Una vez decididos tipos y restricciones, escribir tres dialectos a mano es trabajo mecánico. Generador de DDL permite montar tablas y columnas en pantalla y genera las sentencias CREATE TABLE para MySQL, PostgreSQL y SQLite junto con un diagrama ER en formato Mermaid. También puedes pegar un JSON de ejemplo —una respuesta de API, una línea de log— para inferir las columnas automáticamente, muy útil cuando reconstruyes un esquema a partir de datos existentes. Las diferencias de dialecto viven dentro de la herramienta, así que no hace falta volver a esta tabla cada vez.

Si ya tienes el DDL, pégalo en SQL a Diagrama ER para ver las relaciones como diagrama. Con las tablas listas, Visual SQL Builder te ayuda a montar las sentencias SELECT. Para las diferencias entre tipos de JOIN, consulta la referencia de JOIN en SQL; para la sintaxis del diagrama, la referencia de diagramas ER en Mermaid. Todas estas herramientas funcionan íntegramente en el navegador: el esquema que introduces nunca se envía a ningún servidor.

Resumen

• En los tipos: en SQLite solo hay afinidades, VARCHAR(n) no acelera nada en PostgreSQL y el dinero va en DECIMAL
• El autoincremento son tres funcionalidades distintas: AUTO_INCREMENT / GENERATED ALWAYS AS IDENTITY / INTEGER PRIMARY KEY. En esquemas nuevos de PostgreSQL, IDENTITY antes que SERIAL
• CHECK se ignora antes de MySQL 8.0.16 y SQLite necesita PRAGMA foreign_keys = ON en cada conexión
• Una columna con ON DELETE SET NULL no puede ser NOT NULL
• ON UPDATE CURRENT_TIMESTAMP es solo de MySQL; PostgreSQL necesita un trigger
• En MySQL el charset es utf8mb4, nunca utf8

Preguntas frecuentes

¿Debo usar VARCHAR o TEXT?

Depende de la base de datos. En PostgreSQL ambos están implementados casi igual, así que TEXT es suficiente salvo que necesites imponer un límite real. En MySQL, VARCHAR se almacena en la propia fila mientras que TEXT puede guardarse fuera de página, por lo que las cadenas cortas que filtras y ordenas con frecuencia funcionan mejor como VARCHAR(n). En SQLite ambas declaraciones se comportan igual internamente.

¿Conviene añadir AUTOINCREMENT en SQLite?

Normalmente no. INTEGER PRIMARY KEY ya asigna el valor automáticamente cuando se omite. AUTOINCREMENT solo añade la garantía de no reutilizar IDs borrados y cuesta escrituras extra en una tabla de control. Úsalo solo cuando reutilizar un ID ya emitido sea un problema real, por ejemplo con IDs expuestos a sistemas externos.

Mi restricción CHECK no se aplica

Si usas MySQL, comprueba si el servidor es anterior a 8.0.16: las versiones antiguas aceptan la sintaxis CHECK sin aplicarla. Ejecuta SELECT VERSION(); para confirmarlo. En SQLite, CHECK sí funciona, pero las claves foráneas son las que están desactivadas por defecto, así que necesitas PRAGMA foreign_keys = ON; en cada conexión.

¿El DDL que pego se envía a un servidor?

No. Tanto el Generador de DDL como SQL a Diagrama ER funcionan íntegramente en el navegador: las definiciones de tablas y los datos del esquema que introduces nunca se transmiten a ningún sitio.