CREATE, ALTER y DROP

Es hora de construir la estructura de nuestra tienda. En esta lección aprenderás los comandos DDL (Data Definition Language): CREATE para crear bases de datos y tablas, ALTER para modificarlas y DROP / TRUNCATE para eliminarlas. Al final tendrás el script completo del esquema que usaremos en todo el curso.

Concepto

Si la base de datos fuera un edificio:

• CREATE es construir un cuarto nuevo (tabla) con sus muebles (columnas).
• ALTER es remodelar: agregar una ventana, quitar una pared.
• DROP es demoler el cuarto con todo lo que tiene dentro.
• TRUNCATE es vaciar el cuarto pero dejarlo en pie.

Crear una base de datos

-- MySQL / MariaDB
CREATE DATABASE tienda CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE tienda;          -- a partir de aquí trabajas dentro de "tienda"

-- PostgreSQL
CREATE DATABASE tienda;
-- en psql te conectas con:  \c tienda

En SQLite no hace falta: la base de datos es el archivo que abres (sqlite3 tienda.db).

Para evitar errores si ya existe:

CREATE DATABASE IF NOT EXISTS tienda;   -- MySQL

Crear una tabla: CREATE TABLE

Sintaxis general:

CREATE TABLE nombre_tabla (
  columna1 TIPO [restricciones],
  columna2 TIPO [restricciones],
  ...
  [restricciones de tabla]
);

Ejemplo comentado:

CREATE TABLE categorias (
  id INT PRIMARY KEY,                 -- clave primaria
  nombre VARCHAR(50) NOT NULL UNIQUE  -- obligatorio y sin repetir
);

Restricciones más comunes (las verás a fondo en Restricciones e integridad):

Restricción	Qué hace
PRIMARY KEY	Identificador único y no nulo
NOT NULL	La columna es obligatoria
UNIQUE	No se permiten valores repetidos
DEFAULT valor	Valor si no se indica otro
CHECK (condición)	Valida una regla (precio >= 0)
FOREIGN KEY ... REFERENCES	Apunta a la PK de otra tabla

IDs automáticos

Escribir el id a mano es incómodo. Cada SGBD tiene su forma de generarlo:

MySQL

CREATE TABLE categorias (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL UNIQUE
);

PostgreSQL

CREATE TABLE categorias (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL UNIQUE
);

SQLite

CREATE TABLE categorias (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nombre VARCHAR(50) NOT NULL UNIQUE
);

En el curso usaremos INT PRIMARY KEY y escribiremos los ids explícitamente. Así el script funciona idéntico en los tres motores y los resultados de las consultas coinciden con los de las lecciones.

Ejemplo: el esquema completo de la tienda

Guarda esto como 01-esquema.sql. Funciona en MySQL 8+, MariaDB, PostgreSQL y SQLite.

-- =========================================
--  Esquema de la tienda (curso de SQL)
-- =========================================

-- Borrar tablas si existen (en orden inverso por las FK)
DROP TABLE IF EXISTS detalle_pedido;
DROP TABLE IF EXISTS pedidos;
DROP TABLE IF EXISTS productos;
DROP TABLE IF EXISTS clientes;
DROP TABLE IF EXISTS categorias;

-- Categorías de productos
CREATE TABLE categorias (
  id INT PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL UNIQUE
);

-- Clientes de la tienda
CREATE TABLE clientes (
  id INT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  ciudad VARCHAR(80),
  fecha_registro DATE NOT NULL
);

-- Productos (pueden no tener categoría)
CREATE TABLE productos (
  id INT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  categoria_id INT NULL,
  precio DECIMAL(10,2) NOT NULL CHECK (precio >= 0),
  stock INT NOT NULL DEFAULT 0,
  FOREIGN KEY (categoria_id) REFERENCES categorias(id)
);

-- Pedidos de cada cliente
CREATE TABLE pedidos (
  id INT PRIMARY KEY,
  cliente_id INT NOT NULL,
  fecha DATE NOT NULL,
  estado VARCHAR(20) NOT NULL DEFAULT 'pendiente',
  FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

-- Productos dentro de cada pedido (relación N:M)
CREATE TABLE detalle_pedido (
  pedido_id INT,
  producto_id INT,
  cantidad INT NOT NULL CHECK (cantidad > 0),
  precio_unitario DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (pedido_id, producto_id),
  FOREIGN KEY (pedido_id) REFERENCES pedidos(id),
  FOREIGN KEY (producto_id) REFERENCES productos(id)
);

¿Por qué FOREIGN KEY al final y no en la columna?

SQL estándar permite escribir categoria_id INT REFERENCES categorias(id) directamente en la columna. PostgreSQL y SQLite lo respetan, pero MySQL lo ignora en silencio (no crea la FK). La forma FOREIGN KEY (columna) REFERENCES tabla(id) al final funciona en todos.

SQLite y las claves foráneas

SQLite no revisa las FK por defecto. Actívalo en cada conexión con PRAGMA foreign_keys = ON;.

Ver la estructura

-- MySQL
SHOW TABLES;
DESCRIBE productos;
SHOW CREATE TABLE productos;

-- PostgreSQL (psql)
-- \dt            lista tablas
-- \d productos   describe la tabla

-- SQLite
-- .tables
-- .schema productos

Salida de DESCRIBE productos; en MySQL:

Field	Type	Null	Key	Default
id	int	NO	PRI	NULL
nombre	varchar(100)	NO		NULL
categoria_id	int	YES	MUL	NULL
precio	decimal(10,2)	NO		NULL
stock	int	NO		0

Modificar tablas: ALTER TABLE

Los requisitos cambian. El cliente te pide guardar el teléfono de los clientes y una descripción de los productos:

-- Agregar columnas
ALTER TABLE clientes ADD COLUMN telefono VARCHAR(20);
ALTER TABLE productos ADD COLUMN descripcion TEXT;

-- Renombrar una columna (MySQL 8+, PostgreSQL, SQLite 3.25+)
ALTER TABLE clientes RENAME COLUMN telefono TO celular;

-- Eliminar una columna
ALTER TABLE clientes DROP COLUMN celular;

-- Renombrar la tabla
ALTER TABLE productos RENAME TO articulos;
ALTER TABLE articulos RENAME TO productos;

Cambiar el tipo de una columna varía según el motor:

MySQL

ALTER TABLE productos MODIFY COLUMN nombre VARCHAR(150) NOT NULL;

PostgreSQL

ALTER TABLE productos ALTER COLUMN nombre TYPE VARCHAR(150);
ALTER TABLE productos ALTER COLUMN stock SET DEFAULT 10;
ALTER TABLE productos ALTER COLUMN stock DROP DEFAULT;

Agregar y quitar restricciones:

-- Agregar una restricción con nombre
ALTER TABLE productos ADD CONSTRAINT chk_stock_positivo CHECK (stock >= 0);

-- Quitarla (PostgreSQL y MySQL 8.0.19+)
ALTER TABLE productos DROP CONSTRAINT chk_stock_positivo;

Error común

Agregar una columna NOT NULL sin DEFAULT a una tabla que ya tiene filas: la BD no sabe qué poner en las filas existentes y falla. Solución: ALTER TABLE clientes ADD COLUMN activo BOOLEAN NOT NULL DEFAULT TRUE;

Eliminar: DROP y TRUNCATE

-- Vaciar todos los datos, conservando la estructura (muy rápido)
TRUNCATE TABLE detalle_pedido;     -- en SQLite: DELETE FROM detalle_pedido;

-- Eliminar la tabla completa (estructura + datos)
DROP TABLE IF EXISTS detalle_pedido;

-- Eliminar toda la base de datos
DROP DATABASE IF EXISTS tienda;

Comando	Borra datos	Borra estructura	Se puede filtrar con WHERE	Reinicia autoincremento
DELETE FROM t	Sí	No	Sí	No
TRUNCATE TABLE t	Sí (todos)	No	No	Sí
DROP TABLE t	Sí	Sí	No	—

DROP no tiene "deshacer"

En MySQL, DROP y TRUNCATE hacen commit automático: no hay vuelta atrás. Antes de ejecutarlos en un servidor real, verifica dos veces en qué base de datos estás (SELECT DATABASE(); en MySQL, SELECT current_database(); en PostgreSQL) y ten un respaldo reciente.

Orden al borrar con claves foráneas

No puedes borrar clientes mientras pedidos tenga filas que lo referencian. Borra primero las tablas "hijas":

detalle_pedido  →  pedidos  →  productos  →  clientes  →  categorias
   (hija)                                                   (padre)

Por eso el script empieza con los DROP TABLE IF EXISTS en ese orden.

Paso a paso: tu flujo de trabajo

1. Escribe el esquema en un archivo .sql (no directamente en la consola).
2. Empieza el archivo con DROP TABLE IF EXISTS para poder re-ejecutarlo.
3. Ejecútalo: en MySQL mysql -u root -p tienda < 01-esquema.sql, en PostgreSQL psql -d tienda -f 01-esquema.sql.
4. Revisa con DESCRIBE / \d.
5. Cuando la BD ya tenga datos reales, no uses DROP: crea scripts de cambios con ALTER TABLE (eso se llama migraciones; Laravel y Prisma las automatizan).

Buena práctica

Usa nombres en minúsculas, sin acentos y con guion bajo (detalle_pedido, fecha_registro). Elige plural o singular para las tablas y sé consistente. Las FK nómbralas tabla_singular_id (cliente_id).

Ejercicios

1. Crea una tabla proveedores con: id (PK), nombre obligatorio, email único, teléfono opcional y fecha de alta con valor por defecto de la fecha actual (DEFAULT CURRENT_DATE).
2. Agrega a productos una columna proveedor_id que sea FK hacia proveedores.
3. Agrega a clientes una columna activo booleana, obligatoria, verdadera por defecto.
4. ¿Qué diferencia hay entre TRUNCATE TABLE pedidos y DROP TABLE pedidos?

Ver solución

-- 1
CREATE TABLE proveedores (
  id INT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE,
  telefono VARCHAR(20),
  fecha_alta DATE NOT NULL DEFAULT CURRENT_DATE
);
-- Nota: en MySQL, DEFAULT con expresión requiere paréntesis:
-- fecha_alta DATE NOT NULL DEFAULT (CURRENT_DATE)

-- 2
ALTER TABLE productos ADD COLUMN proveedor_id INT NULL;
ALTER TABLE productos
  ADD CONSTRAINT fk_productos_proveedor
  FOREIGN KEY (proveedor_id) REFERENCES proveedores(id);

-- 3
ALTER TABLE clientes ADD COLUMN activo BOOLEAN NOT NULL DEFAULT TRUE;

4. TRUNCATE borra todas las filas pero la tabla sigue existiendo (vacía). DROP elimina la tabla completa: estructura, datos, índices y restricciones.

Resumen

• CREATE DATABASE / CREATE TABLE construyen la estructura con tipos y restricciones.
• ALTER TABLE agrega, renombra o elimina columnas y restricciones.
• TRUNCATE vacía una tabla; DROP la elimina por completo. Ninguno se puede deshacer fácilmente.
• Escribe las FK con FOREIGN KEY (...) REFERENCES ... para que funcionen también en MySQL.
• Guarda tu esquema en un archivo .sql re-ejecutable. Ya tienes el de la tienda: en la siguiente lección le metemos datos.