Tutorial SQL: Creación de bases de datos y tablas: CREATE DATABASE, CREATE TABLE

CREATE DATABASE

El comando CREATE DATABASE es la instrucción SQL fundamental para crear nuevas bases de datos en sistemas de gestión como MySQL y PostgreSQL. Este comando establece el contenedor principal donde posteriormente almacenaremos todas nuestras tablas, vistas, procedimientos y demás objetos de base de datos.

La sintaxis básica del comando es sencilla pero potente:

CREATE DATABASE nombre_base_datos;

Donde nombre_base_datos es el identificador que asignamos a nuestra nueva base de datos. Este nombre debe seguir ciertas reglas de nomenclatura:

• Debe comenzar con una letra o guion bajo
• Puede contener letras, números y guiones bajos
• No debe contener espacios ni caracteres especiales
• No debe ser una palabra reservada de SQL

Opciones adicionales

Tanto MySQL como PostgreSQL permiten especificar opciones adicionales al crear una base de datos:

En MySQL

CREATE DATABASE [IF NOT EXISTS] nombre_base_datos
[CHARACTER SET charset_name]
[COLLATE collation_name];

• La cláusula IF NOT EXISTS evita que se produzca un error si intentamos crear una base de datos que ya existe.
• CHARACTER SET define el conjunto de caracteres predeterminado para las tablas de la base de datos.
• COLLATE especifica la regla de ordenación para el conjunto de caracteres.

Ejemplo práctico en MySQL:

CREATE DATABASE IF NOT EXISTS tienda_online
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Este comando crea una base de datos llamada "tienda_online" con soporte completo para caracteres Unicode (incluyendo emojis) y reglas de ordenación que respetan los caracteres especiales de diferentes idiomas.

En PostgreSQL

CREATE DATABASE nombre_base_datos
[WITH] [OWNER = rol]
      [TEMPLATE = plantilla]
      [ENCODING = codificación]
      [LC_COLLATE = collate]
      [LC_CTYPE = ctype]
      [TABLESPACE = espacio_tabla]
      [CONNECTION LIMIT = límite_conexiones];

PostgreSQL ofrece opciones más extensas:

• OWNER especifica el usuario propietario de la base de datos
• TEMPLATE permite crear la base de datos como copia de una plantilla existente
• ENCODING define la codificación de caracteres
• TABLESPACE especifica el espacio de tablas donde se almacenará físicamente la base de datos

Ejemplo en PostgreSQL:

CREATE DATABASE sistema_inventario
WITH OWNER = admin_inventario
     ENCODING = 'UTF8'
     TEMPLATE = template0
     CONNECTION LIMIT = 100;

Este comando crea una base de datos "sistema_inventario" con el usuario "admin_inventario" como propietario, codificación UTF8, basada en la plantilla vacía "template0" y con un límite de 100 conexiones simultáneas.

Verificación y selección de bases de datos

Después de crear una base de datos, podemos verificar su existencia y seleccionarla para trabajar con ella:

En MySQL:

-- Listar todas las bases de datos
SHOW DATABASES;

-- Seleccionar una base de datos para trabajar
USE nombre_base_datos;

En PostgreSQL:

-- Listar todas las bases de datos
\l

-- Conectarse a una base de datos específica
\c nombre_base_datos

También podemos consultar información sobre las bases de datos existentes mediante consultas al catálogo del sistema:

-- En MySQL
SELECT schema_name, default_character_set_name
FROM information_schema.schemata;

-- En PostgreSQL
SELECT datname, encoding, datcollate
FROM pg_database;

Consideraciones prácticas

Al crear bases de datos, es importante tener en cuenta algunas buenas prácticas:

• Utilizar nombres descriptivos que reflejen el propósito de la base de datos
• Establecer la codificación de caracteres adecuada desde el principio (generalmente UTF8)
• Asignar permisos apropiados al propietario de la base de datos
• Documentar el propósito y estructura de cada base de datos creada

Por ejemplo, en un entorno de desarrollo es común crear bases de datos separadas para diferentes entornos:

CREATE DATABASE app_desarrollo;
CREATE DATABASE app_pruebas;
CREATE DATABASE app_produccion;

Este enfoque permite mantener los datos de desarrollo, pruebas y producción completamente aislados, evitando modificaciones accidentales en datos críticos.

Diferencias entre MySQL y PostgreSQL

Aunque la sintaxis básica es similar, existen algunas diferencias importantes entre MySQL y PostgreSQL:

• En MySQL, el comando CREATE DATABASE es equivalente a CREATE SCHEMA
• PostgreSQL distingue entre DATABASE (contenedor global) y SCHEMA (espacio de nombres dentro de una base de datos)
• PostgreSQL no permite crear bases de datos dentro de una transacción
• MySQL permite especificar la ubicación de los archivos de datos, mientras que PostgreSQL utiliza el sistema de tablespaces

Estas diferencias son importantes cuando se trabaja en entornos que utilizan ambos sistemas de gestión de bases de datos.

CREATE TABLE

El comando CREATE TABLE es fundamental en SQL para definir la estructura de las tablas donde almacenaremos nuestros datos. Una tabla representa una entidad o concepto del mundo real (como clientes, productos o pedidos) y está compuesta por columnas que definen los atributos de esa entidad.

La sintaxis básica del comando es:

CREATE TABLE nombre_tabla (
    columna1 tipo_dato [restricciones],
    columna2 tipo_dato [restricciones],
    ...
    [restricciones_tabla]
);

Definición de columnas

Cada columna en una tabla requiere al menos dos elementos:

• Un nombre de columna único dentro de la tabla
• Un tipo de dato que determina qué valores puede almacenar

Por ejemplo, para crear una tabla simple de productos:

CREATE TABLE productos (
    id INT,
    nombre VARCHAR(100),
    precio DECIMAL(10,2),
    stock INT,
    fecha_creacion DATE
);

En este ejemplo:

• id almacena números enteros
• nombre almacena texto de hasta 100 caracteres
• precio almacena valores decimales con 10 dígitos en total y 2 decimales
• stock almacena números enteros
• fecha_creacion almacena fechas

Tipos de datos comunes

MySQL y PostgreSQL soportan varios tipos de datos, aunque con algunas diferencias:

• Numéricos:

• INT o INTEGER: números enteros

• DECIMAL(p,s) o NUMERIC(p,s): números decimales con precisión p y escala s

• FLOAT, REAL: números de punto flotante

• Texto:

• CHAR(n): texto de longitud fija

• VARCHAR(n): texto de longitud variable hasta n caracteres

• TEXT: texto de longitud variable (grande)

• Fecha y hora:

• DATE: solo fecha (YYYY-MM-DD)

• TIME: solo hora (HH:MM:SS)

• TIMESTAMP: fecha y hora

• DATETIME (MySQL): similar a TIMESTAMP

• Booleanos:

• BOOLEAN o BOOL: verdadero/falso

• Otros:

• BLOB: datos binarios

• JSON: datos en formato JSON (nativo en PostgreSQL, desde MySQL 5.7)

• UUID: identificadores únicos universales (nativo en PostgreSQL)

Restricciones a nivel de columna

Las restricciones permiten definir reglas que los datos deben cumplir:

CREATE TABLE empleados (
    id INT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salario DECIMAL(10,2) CHECK (salario > 0),
    departamento_id INT REFERENCES departamentos(id),
    fecha_contratacion DATE DEFAULT CURRENT_DATE
);

Las restricciones más comunes son:

• PRIMARY KEY: identifica de manera única cada fila
• NOT NULL: la columna no puede contener valores nulos
• UNIQUE: todos los valores en la columna deben ser únicos
• CHECK: los valores deben cumplir una condición específica
• REFERENCES: establece una relación con otra tabla (clave foránea)
• DEFAULT: valor predeterminado si no se especifica uno

Restricciones a nivel de tabla

Algunas restricciones pueden definirse a nivel de tabla, especialmente cuando involucran múltiples columnas:

CREATE TABLE pedidos (
    pedido_id INT,
    producto_id INT,
    cantidad INT NOT NULL,
    precio_unitario DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (pedido_id, producto_id),
    CHECK (cantidad * precio_unitario <= 10000),
    FOREIGN KEY (producto_id) REFERENCES productos(id)
);

En este ejemplo:

• Definimos una clave primaria compuesta por dos columnas
• Establecemos una restricción CHECK que involucra dos columnas
• Definimos una clave foránea que referencia otra tabla

Opciones adicionales

Tanto MySQL como PostgreSQL ofrecen opciones adicionales al crear tablas:

En MySQL

CREATE TABLE ventas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    monto DECIMAL(10,2),
    fecha DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Opciones específicas de MySQL:

• ENGINE: define el motor de almacenamiento (InnoDB, MyISAM, etc.)
• AUTO_INCREMENT: genera valores secuenciales automáticamente
• CHARACTER SET y COLLATE: definen la codificación y reglas de ordenación

En PostgreSQL

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Opciones específicas de PostgreSQL:

• SERIAL: tipo de dato que crea automáticamente una secuencia para generar IDs
• CURRENT_TIMESTAMP: función para obtener la fecha y hora actual

Tablas temporales

Ambos sistemas permiten crear tablas temporales que existen solo durante la sesión actual:

CREATE TEMPORARY TABLE resultados_temp (
    id INT,
    resultado VARCHAR(100)
);

Las tablas temporales son útiles para almacenar resultados intermedios durante operaciones complejas.

Creación de tablas a partir de consultas

También podemos crear tablas basadas en los resultados de una consulta:

CREATE TABLE productos_caros AS
SELECT * FROM productos WHERE precio > 1000;

Esta técnica, conocida como CTAS (Create Table As Select), es útil para:

• Crear copias de seguridad de datos
• Materializar resultados de consultas complejas
• Crear tablas de resumen o agregación

Ejemplos prácticos

Tabla para una tienda online

CREATE TABLE clientes (
    cliente_id INT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    apellido VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    telefono VARCHAR(15),
    fecha_registro DATE DEFAULT CURRENT_DATE,
    activo BOOLEAN DEFAULT TRUE
);

CREATE TABLE productos (
    producto_id INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    precio DECIMAL(10,2) NOT NULL CHECK (precio > 0),
    stock INT NOT NULL DEFAULT 0,
    categoria VARCHAR(50),
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE pedidos (
    pedido_id INT PRIMARY KEY,
    cliente_id INT NOT NULL,
    fecha_pedido TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado VARCHAR(20) DEFAULT 'pendiente',
    total DECIMAL(12,2),
    FOREIGN KEY (cliente_id) REFERENCES clientes(cliente_id)
);

Tabla para un sistema de blog

CREATE TABLE usuarios (
    usuario_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    nombre VARCHAR(50),
    apellido VARCHAR(50),
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE articulos (
    articulo_id SERIAL PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    contenido TEXT NOT NULL,
    fecha_publicacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    autor_id INT NOT NULL,
    publicado BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (autor_id) REFERENCES usuarios(usuario_id)
);

CREATE TABLE comentarios (
    comentario_id SERIAL PRIMARY KEY,
    articulo_id INT NOT NULL,
    usuario_id INT NOT NULL,
    contenido TEXT NOT NULL,
    fecha_comentario TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (articulo_id) REFERENCES articulos(articulo_id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(usuario_id)
);

Verificación de la estructura de tablas

Después de crear una tabla, podemos verificar su estructura:

En MySQL:

-- Ver la estructura de una tabla
DESCRIBE nombre_tabla;
-- o
SHOW COLUMNS FROM nombre_tabla;

-- Ver la definición completa
SHOW CREATE TABLE nombre_tabla;

En PostgreSQL:

-- Ver la estructura de una tabla
\d nombre_tabla

-- Ver la definición completa
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'nombre_tabla';

Buenas prácticas

Al crear tablas, es recomendable seguir estas buenas prácticas:

• Utilizar nombres descriptivos para tablas y columnas
• Definir siempre una clave primaria para cada tabla
• Establecer restricciones apropiadas para mantener la integridad de los datos
• Normalizar las tablas para evitar redundancia de datos
• Documentar el propósito de cada tabla y columna
• Utilizar tipos de datos apropiados para optimizar el almacenamiento
• Considerar el rendimiento al definir índices y claves foráneas

Definición de restricciones

Las restricciones (constraints) en SQL son reglas que se aplican a las columnas o tablas para limitar los tipos de datos que pueden ser almacenados, garantizando así la integridad y consistencia de la información en la base de datos. Estas reglas se definen durante la creación de tablas con el comando CREATE TABLE o pueden añadirse posteriormente mediante ALTER TABLE.

Tipos de restricciones principales

SQL ofrece varios tipos de restricciones que podemos implementar para proteger nuestros datos:

• PRIMARY KEY: Identifica de manera única cada registro en una tabla
• FOREIGN KEY: Mantiene la integridad referencial entre tablas relacionadas
• UNIQUE: Asegura que todos los valores en una columna o conjunto de columnas sean distintos
• NOT NULL: Impide que una columna acepte valores nulos
• CHECK: Verifica que los valores cumplan una condición específica
• DEFAULT: Asigna un valor predeterminado cuando no se especifica uno

Veamos cada una de estas restricciones en detalle.

PRIMARY KEY (Clave primaria)

La restricción PRIMARY KEY identifica de forma única cada fila en una tabla. Una tabla solo puede tener una clave primaria, que puede consistir en una o múltiples columnas.

-- Clave primaria en una sola columna
CREATE TABLE clientes (
    cliente_id INT PRIMARY KEY,
    nombre VARCHAR(50),
    email VARCHAR(100)
);

-- Clave primaria a nivel de tabla
CREATE TABLE pedidos_productos (
    pedido_id INT,
    producto_id INT,
    cantidad INT,
    PRIMARY KEY (pedido_id, producto_id)
);

En MySQL, podemos combinar la clave primaria con auto-incremento:

CREATE TABLE usuarios (
    usuario_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre_usuario VARCHAR(50),
    fecha_registro DATE
);

En PostgreSQL, usaríamos:

CREATE TABLE usuarios (
    usuario_id SERIAL PRIMARY KEY,
    nombre_usuario VARCHAR(50),
    fecha_registro DATE
);

FOREIGN KEY (Clave foránea)

La restricción FOREIGN KEY establece una relación entre dos tablas, asegurando que los valores en una columna (o conjunto de columnas) de la tabla secundaria correspondan a valores en la tabla principal.

CREATE TABLE departamentos (
    departamento_id INT PRIMARY KEY,
    nombre VARCHAR(50)
);

CREATE TABLE empleados (
    empleado_id INT PRIMARY KEY,
    nombre VARCHAR(50),
    departamento_id INT,
    FOREIGN KEY (departamento_id) REFERENCES departamentos(departamento_id)
);

Podemos añadir acciones referenciales que determinan qué sucede cuando se elimina o actualiza un registro referenciado:

CREATE TABLE articulos (
    articulo_id INT PRIMARY KEY,
    titulo VARCHAR(100)
);

CREATE TABLE comentarios (
    comentario_id INT PRIMARY KEY,
    articulo_id INT,
    texto TEXT,
    FOREIGN KEY (articulo_id) REFERENCES articulos(articulo_id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

Las opciones para ON DELETE y ON UPDATE son:

• CASCADE: Propaga la acción (si se elimina un artículo, se eliminan todos sus comentarios)
• RESTRICT: Impide la acción si existen referencias
• SET NULL: Establece la columna referenciada como NULL
• SET DEFAULT: Establece la columna referenciada a su valor predeterminado
• NO ACTION: Similar a RESTRICT, pero se verifica al final de la transacción

UNIQUE (Único)

La restricción UNIQUE garantiza que todos los valores en una columna o conjunto de columnas sean distintos. A diferencia de PRIMARY KEY, una tabla puede tener múltiples restricciones UNIQUE.

CREATE TABLE usuarios (
    usuario_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    nombre_usuario VARCHAR(50) UNIQUE,
    telefono VARCHAR(15)
);

-- Restricción UNIQUE para múltiples columnas
CREATE TABLE empleados (
    empleado_id INT PRIMARY KEY,
    nombre VARCHAR(50),
    apellido VARCHAR(50),
    departamento_id INT,
    UNIQUE (nombre, apellido, departamento_id)
);

La restricción UNIQUE permite valores NULL (a menos que se combine con NOT NULL), y en la mayoría de los sistemas de bases de datos, múltiples valores NULL se consideran distintos entre sí.

NOT NULL

La restricción NOT NULL especifica que una columna no puede contener valores nulos. Esto obliga a que siempre se proporcione un valor para esa columna al insertar o actualizar datos.

CREATE TABLE productos (
    producto_id INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    precio DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0
);

Es una buena práctica aplicar NOT NULL a columnas que siempre deben tener un valor para que los registros sean significativos.

CHECK

La restricción CHECK permite definir una condición que deben cumplir los valores de una columna o conjunto de columnas.

CREATE TABLE productos (
    producto_id INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) CHECK (precio > 0),
    descuento DECIMAL(5,2) CHECK (descuento >= 0 AND descuento <= 100),
    stock INT CHECK (stock >= 0)
);

-- CHECK con múltiples columnas
CREATE TABLE pedidos (
    pedido_id INT PRIMARY KEY,
    fecha_pedido DATE,
    fecha_entrega DATE,
    CHECK (fecha_entrega >= fecha_pedido)
);

Las restricciones CHECK son especialmente útiles para validar rangos de valores, relaciones entre columnas y reglas de negocio específicas.

DEFAULT

La restricción DEFAULT asigna un valor predeterminado a una columna cuando no se especifica un valor en una instrucción INSERT.

CREATE TABLE publicaciones (
    publicacion_id INT PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    contenido TEXT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado VARCHAR(20) DEFAULT 'borrador',
    vistas INT DEFAULT 0
);

Los valores predeterminados pueden ser literales, expresiones o funciones del sistema como CURRENT_TIMESTAMP.

Nombrar restricciones

Es posible (y recomendable) asignar nombres a las restricciones para facilitar su gestión posterior:

CREATE TABLE pedidos (
    pedido_id INT,
    cliente_id INT,
    fecha DATE,
    total DECIMAL(10,2),
    CONSTRAINT pk_pedidos PRIMARY KEY (pedido_id),
    CONSTRAINT fk_pedidos_clientes FOREIGN KEY (cliente_id) 
        REFERENCES clientes(cliente_id),
    CONSTRAINT chk_pedidos_total CHECK (total > 0)
);

Nombrar las restricciones facilita su modificación o eliminación posterior, y hace que los mensajes de error sean más descriptivos.

Restricciones temporales

En algunos casos, podemos necesitar deshabilitar temporalmente las restricciones, por ejemplo, durante la carga masiva de datos:

-- En MySQL
SET foreign_key_checks = 0;
-- Operaciones de carga masiva
SET foreign_key_checks = 1;

-- En PostgreSQL
SET constraints_deferred = ON;
-- Operaciones de carga masiva
SET constraints_deferred = OFF;

Ejemplo completo con múltiples restricciones

Veamos un ejemplo completo de una tabla con varias restricciones:

CREATE TABLE facturas (
    factura_id INT AUTO_INCREMENT,
    cliente_id INT NOT NULL,
    fecha_emision DATE NOT NULL DEFAULT CURRENT_DATE,
    fecha_vencimiento DATE NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL CHECK (subtotal >= 0),
    impuestos DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (impuestos >= 0),
    total DECIMAL(12,2) GENERATED ALWAYS AS (subtotal + impuestos) STORED,
    estado VARCHAR(20) NOT NULL DEFAULT 'pendiente' 
        CHECK (estado IN ('pendiente', 'pagada', 'anulada')),
    
    CONSTRAINT pk_facturas PRIMARY KEY (factura_id),
    CONSTRAINT fk_facturas_clientes FOREIGN KEY (cliente_id)
        REFERENCES clientes(cliente_id) ON DELETE RESTRICT,
    CONSTRAINT chk_fechas_facturas CHECK (fecha_vencimiento >= fecha_emision)
);

En este ejemplo:

• Definimos una clave primaria auto-incrementable
• Establecemos una clave foránea con restricción de eliminación
• Aplicamos restricciones NOT NULL a columnas críticas
• Utilizamos valores DEFAULT para columnas con valores típicos
• Implementamos restricciones CHECK para validar rangos y valores permitidos
• Creamos una columna calculada (en sistemas que lo soportan)
• Nombramos todas las restricciones para facilitar su gestión

Diferencias entre MySQL y PostgreSQL

Aunque la mayoría de las restricciones funcionan de manera similar en MySQL y PostgreSQL, existen algunas diferencias:

• PostgreSQL tiene soporte completo para todas las restricciones estándar SQL
• MySQL con el motor InnoDB soporta todas las restricciones, pero otros motores como MyISAM no soportan FOREIGN KEY
• PostgreSQL permite restricciones CHECK más complejas, incluyendo subconsultas
• MySQL históricamente ignoraba las restricciones CHECK (antes de la versión 8.0.16)
• PostgreSQL ofrece restricciones de exclusión (EXCLUDE) para casos avanzados

Buenas prácticas

Al definir restricciones, es recomendable seguir estas prácticas:

• Nombrar todas las restricciones con un prefijo que indique su tipo (pk_, fk_, chk_, etc.)
• Aplicar NOT NULL a todas las columnas que lógicamente requieran un valor
• Definir claves primarias para todas las tablas
• Implementar claves foráneas para mantener la integridad referencial
• Utilizar restricciones CHECK para validar reglas de negocio
• Establecer valores DEFAULT apropiados para simplificar las operaciones de inserción
• Documentar el propósito de las restricciones complejas

Las restricciones son una parte fundamental del diseño de bases de datos, ya que ayudan a mantener la integridad de los datos y a implementar reglas de negocio directamente en la estructura de la base de datos, evitando que datos incorrectos o inconsistentes sean almacenados.