11. Creación y gestión de tablas

Hasta ahora hemos trabajado con el DML (Data Manipulation Language), aprendiendo a consultar, insertar, actualizar y eliminar datos. Pero, ¿cómo se crean las tablas en primer lugar? Aquí es donde entra el DDL (Data Definition Language).

El DDL es el conjunto de comandos SQL que te permite definir y modificar la estructura de la base de datos: crear tablas, modificarlas, eliminarlas, crear índices y establecer restricciones.

CREATE TABLE

Para crear una nueva tabla debemos usar CREATE TABLE seguido del nombre de la tabla y la definición de sus columnas entre paréntesis.

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

Ejemplo básico:

CREATE TABLE Empleados (
    EmpleadoId INTEGER PRIMARY KEY AUTOINCREMENT,
    Nombre TEXT NOT NULL,
    Apellido TEXT NOT NULL,
    Email TEXT UNIQUE,
    Salario REAL,
    FechaContratacion TEXT DEFAULT CURRENT_TIMESTAMP
);

Este comando crea una tabla llamada Empleados con 6 columnas, cada una con su tipo de dato y restricciones específicas.

Restricciones (Constraints)

Las restricciones son reglas que se aplican a las columnas para garantizar la integridad de los datos.

PRIMARY KEY

Identifica de forma única cada fila de la tabla. No puede haber valores duplicados ni nulos.

CREATE TABLE Productos (
    ProductoId INTEGER PRIMARY KEY AUTOINCREMENT,
    Nombre TEXT NOT NULL
);

AUTOINCREMENT hace que el valor se incremente automáticamente con cada nueva fila.

FOREIGN KEY

Establece una relación con otra tabla. Asegura que el valor exista en la tabla referenciada.

CREATE TABLE Pedidos (
    PedidoId INTEGER PRIMARY KEY AUTOINCREMENT,
    ClienteId INTEGER NOT NULL,
    Total REAL NOT NULL,
    FOREIGN KEY (ClienteId) REFERENCES Clientes(ClienteId)
);

Esto garantiza que cada ClienteId en la tabla Pedidos exista en la tabla Clientes.

NOT NULL

Obliga a que la columna tenga siempre un valor, no puede estar vacía.

CREATE TABLE Usuarios (
    UsuarioId INTEGER PRIMARY KEY AUTOINCREMENT,
    Nombre TEXT NOT NULL,
    Email TEXT NOT NULL
);

UNIQUE

Garantiza que no haya valores duplicados en esa columna. A diferencia de PRIMARY KEY, puede haber valores NULL.

CREATE TABLE Clientes (
    ClienteId INTEGER PRIMARY KEY AUTOINCREMENT,
    Email TEXT UNIQUE,
    DNI TEXT UNIQUE
);

DEFAULT

Establece un valor por defecto si no se proporciona ninguno al insertar.

CREATE TABLE Articulos (
    ArticuloId INTEGER PRIMARY KEY AUTOINCREMENT,
    Titulo TEXT NOT NULL,
    FechaPublicacion TEXT DEFAULT CURRENT_TIMESTAMP,
    Estado TEXT DEFAULT 'Borrador',
    Visitas INTEGER DEFAULT 0
);

Si insertas un artículo sin especificar el estado, automáticamente será 'Borrador'.

CHECK

Define una condición que debe cumplirse para los valores de la columna.

CREATE TABLE Productos (
    ProductoId INTEGER PRIMARY KEY AUTOINCREMENT,
    Nombre TEXT NOT NULL,
    Precio REAL CHECK(Precio > 0),
    Stock INTEGER CHECK(Stock >= 0),
    Descuento REAL CHECK(Descuento BETWEEN 0 AND 100)
);

Esto asegura que el precio siempre sea positivo, el stock no sea negativo y el descuento esté entre 0 y 100.

Ejemplo completo: Sistema de biblioteca

Vamos a crear un sistema de biblioteca con múltiples tablas relacionadas.

erDiagram
    Autores ||--o{ Libros : escribe
    Libros ||--o{ Prestamos : "se presta"
    Socios ||--o{ Prestamos : realiza

    Autores {
        int AutorId PK
        string Nombre
        string Nacionalidad
    }
    Libros {
        int LibroId PK
        string Titulo
        int AutorId FK
        int AnioPublicacion
        int Stock
    }
    Socios {
        int SocioId PK
        string Nombre
        string Email
        date FechaInscripcion
    }
    Prestamos {
        int PrestamoId PK
        int LibroId FK
        int SocioId FK
        date FechaPrestamo
        date FechaDevolucion
    }

CREATE TABLE Autores (
    AutorId INTEGER PRIMARY KEY AUTOINCREMENT,
    Nombre TEXT NOT NULL,
    Nacionalidad TEXT
);

CREATE TABLE Libros (
    LibroId INTEGER PRIMARY KEY AUTOINCREMENT,
    Titulo TEXT NOT NULL,
    AutorId INTEGER NOT NULL,
    AnioPublicacion INTEGER CHECK(AnioPublicacion > 1000 AND AnioPublicacion <= 2100),
    Stock INTEGER DEFAULT 1 CHECK(Stock >= 0),
    FOREIGN KEY (AutorId) REFERENCES Autores(AutorId)
);

CREATE TABLE Socios (
    SocioId INTEGER PRIMARY KEY AUTOINCREMENT,
    Nombre TEXT NOT NULL,
    Email TEXT UNIQUE NOT NULL,
    FechaInscripcion TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Prestamos (
    PrestamoId INTEGER PRIMARY KEY AUTOINCREMENT,
    LibroId INTEGER NOT NULL,
    SocioId INTEGER NOT NULL,
    FechaPrestamo TEXT DEFAULT CURRENT_TIMESTAMP,
    FechaDevolucion TEXT,
    FOREIGN KEY (LibroId) REFERENCES Libros(LibroId),
    FOREIGN KEY (SocioId) REFERENCES Socios(SocioId)
);

Ahora podemos insertar datos:

INSERT INTO Autores (Nombre, Nacionalidad) VALUES
    ('Gabriel García Márquez', 'Colombiana'),
    ('Isabel Allende', 'Chilena'),
    ('Jorge Luis Borges', 'Argentina');

INSERT INTO Libros (Titulo, AutorId, AnioPublicacion, Stock) VALUES
    ('Cien años de soledad', 1, 1967, 3),
    ('La casa de los espíritus', 2, 1982, 2),
    ('Ficciones', 3, 1944, 5);

INSERT INTO Socios (Nombre, Email) VALUES
    ('Ana Martínez', 'ana@email.com'),
    ('Carlos López', 'carlos@email.com');

INSERT INTO Prestamos (LibroId, SocioId) VALUES
    (1, 1),
    (3, 2);

ALTER TABLE

Una vez creada una tabla, puedes modificarla sin perder los datos existentes.

Añadir una columna

ALTER TABLE [tabla] ADD COLUMN [nombre_columna] [tipo] [restricciones];

Añadamos una columna para el teléfono en la tabla Socios:

ALTER TABLE Socios ADD COLUMN Telefono TEXT;

Renombrar una columna

ALTER TABLE [tabla] RENAME COLUMN [nombre_antiguo] TO [nombre_nuevo];

ALTER TABLE Socios RENAME COLUMN Telefono TO NumeroTelefono;

Renombrar una tabla

ALTER TABLE [tabla_antigua] RENAME TO [tabla_nueva];

ALTER TABLE Socios RENAME TO Miembros;

SQLite tiene limitaciones con ALTER TABLE. No puedes eliminar columnas directamente ni modificar el tipo de una columna existente. Para hacer esto, debes crear una nueva tabla con la estructura correcta, copiar los datos y eliminar la tabla antigua.

DROP TABLE

Para eliminar una tabla completa y todos sus datos:

DROP TABLE [nombre_tabla];

DROP TABLE Prestamos;

¡Cuidado! Esta acción es irreversible. Todos los datos de la tabla se perderán permanentemente.

Si quieres evitar un error cuando la tabla no existe:

DROP TABLE IF EXISTS [nombre_tabla];

DROP TABLE IF EXISTS Prestamos;

Índices

Los índices mejoran la velocidad de las consultas, especialmente en tablas grandes. Funcionan como el índice de un libro: en lugar de leer todo el libro para encontrar un tema, vas directamente a la página indicada.

CREATE INDEX

CREATE INDEX [nombre_indice] ON [tabla] ([columna]);

Crear un índice en la columna Email de la tabla Socios para búsquedas más rápidas:

CREATE INDEX idx_socios_email ON Socios(Email);

Ahora las consultas que filtren por email serán mucho más rápidas:

SELECT * FROM Socios WHERE Email = 'ana@email.com';

Índices compuestos

Puedes crear índices sobre múltiples columnas:

CREATE INDEX idx_libros_autor_anio ON Libros(AutorId, AnioPublicacion);

Esto acelera consultas que filtren por autor y año simultáneamente.

DROP INDEX

Para eliminar un índice:

DROP INDEX [nombre_indice];

DROP INDEX idx_socios_email;

Los índices aceleran las lecturas pero ralentizan las escrituras (INSERT, UPDATE, DELETE), ya que el índice debe actualizarse. Úsalos estratégicamente en columnas que consultes frecuentemente.

Índices únicos

Puedes crear un índice que además garantice valores únicos:

CREATE UNIQUE INDEX idx_productos_codigo ON Productos(CodigoProducto);

Esto es similar a usar la restricción UNIQUE, pero con la ventaja adicional de mejorar el rendimiento.

Transacciones

Las transacciones permiten agrupar varias operaciones SQL en una sola unidad de trabajo. O se ejecutan todas, o no se ejecuta ninguna. Esto es fundamental para mantener la integridad de los datos.

BEGIN TRANSACTION;
    -- Operaciones SQL aquí
COMMIT; -- Confirma los cambios

O si algo sale mal:

ROLLBACK; -- Deshace todos los cambios desde BEGIN

Ejemplo práctico: transferir dinero entre cuentas bancarias:

BEGIN TRANSACTION;

-- Restar dinero de la cuenta origen
UPDATE Cuentas SET Saldo = Saldo - 100 WHERE CuentaId = 1;

-- Sumar dinero a la cuenta destino
UPDATE Cuentas SET Saldo = Saldo + 100 WHERE CuentaId = 2;

-- Si todo está bien, confirmar
COMMIT;

Si hay un error entre BEGIN y COMMIT, puedes ejecutar ROLLBACK para deshacer todos los cambios:

BEGIN TRANSACTION;

UPDATE Cuentas SET Saldo = Saldo - 100 WHERE CuentaId = 1;

-- Ops, nos damos cuenta de un error
ROLLBACK; -- Deshace el UPDATE anterior

Esto garantiza que nunca se reste dinero de una cuenta sin sumarlo a la otra.

Buenas prácticas

1. Nombra las tablas en singular o plural de forma consistente: Usuario o Usuarios, pero no mezcles.

2. Usa nombres descriptivos: FechaCreacion es mejor que FC o fecha1.

3. Define siempre una PRIMARY KEY: Cada tabla debe tener un identificador único.

4. Usa FOREIGN KEY para relaciones: Mantiene la integridad referencial.

5. Establece restricciones adecuadas: NOT NULL, CHECK, UNIQUE según corresponda.

6. Crea índices estratégicamente: En columnas que uses frecuentemente en WHERE, JOIN o ORDER BY.

7. Usa transacciones para operaciones críticas: Especialmente cuando actualices múltiples tablas relacionadas.

8. Documenta tu esquema: Añade comentarios explicando decisiones de diseño complejas.

Viendo la estructura de las tablas

En SQLite Browser puedes ver la estructura en la pestaña "Database Structure", pero también puedes usar SQL:

PRAGMA table_info(nombre_tabla);

Ejemplo:

PRAGMA table_info(Customer);

Esto te mostrará todas las columnas, tipos, restricciones y valores por defecto de la tabla.

Para ver todas las tablas de la base de datos:

SELECT name FROM sqlite_master WHERE type='table';

Para ver el comando CREATE TABLE original de una tabla:

SELECT sql FROM sqlite_master WHERE type='table' AND name='Customer';

Actividad 1

Crea una base de datos para una escuela con las siguientes tablas:

1. Tabla Profesores con: ProfesorId, Nombre, Especialidad, Email (único).
2. Tabla Asignaturas con: AsignaturaId, Nombre, ProfesorId (FK), Creditos (entre 1 y 10).
3. Tabla Estudiantes con: EstudianteId, Nombre, Email (único), FechaInscripcion (con valor por defecto).
4. Tabla Matriculas relacionando estudiantes con asignaturas, con: MatriculaId, EstudianteId (FK), AsignaturaId (FK), Nota (entre 0 y 10, puede ser NULL).

Después de crear las tablas:

• Inserta al menos 2 profesores, 3 asignaturas, 3 estudiantes y 5 matrículas.
• Crea un índice en la columna Email de la tabla Estudiantes.
• Añade una columna Telefono a la tabla Profesores.

Actividad 2

Practica con transacciones creando un sistema de reservas de hotel:

1. Crea una tabla Habitaciones con: HabitacionId, Numero, Tipo, PrecioPorNoche, Disponible (booleano, default 1).
2. Crea una tabla Reservas con: ReservaId, HabitacionId (FK), NombreCliente, FechaEntrada, FechaSalida, TotalPagado.
3. Inserta 5 habitaciones, todas disponibles.
4. Usa una transacción para crear una reserva: marca la habitación como no disponible (Disponible = 0) e inserta la reserva. Si algo falla, usa ROLLBACK.

Soluciones