11. Creating and managing tables

Until now we have worked with DML (Data Manipulation Language), learning to query, insert, update and delete data. But, how are tables created in the first place? This is where DDL (Data Definition Language) comes in.

DDL is the set of SQL commands that lets you define and modify the structure of the database: create tables, modify them, delete them, create indexes and set constraints.

CREATE TABLE

To create a new table we must use CREATE TABLE followed by the name of the table and the definition of its columns in parentheses.

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

Basic example:

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
);

This command creates a table called Empleados with 6 columns, each with its data type and specific constraints.

Constraints

Constraints are rules applied to columns to guarantee data integrity.

PRIMARY KEY

It uniquely identifies each row of the table. There cannot be duplicate or null values.

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

AUTOINCREMENT makes the value increment automatically with each new row.

FOREIGN KEY

It establishes a relationship with another table. It ensures that the value exists in the referenced table.

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

This guarantees that every ClienteId in the Pedidos table exists in the Clientes table.

NOT NULL

It forces the column to always have a value, it cannot be empty.

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

UNIQUE

It guarantees that there are no duplicate values in that column. Unlike PRIMARY KEY, there can be NULL values.

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

DEFAULT

It sets a default value if none is provided when inserting.

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
);

If you insert an article without specifying the status, it will automatically be 'Borrador'.

CHECK

It defines a condition that must be met for the column's values.

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)
);

This ensures that the price is always positive, the stock is not negative and the discount is between 0 and 100.

Complete example: Library system

We are going to create a library system with multiple related tables.

erDiagram
    Autores ||--o{ Libros : writes
    Libros ||--o{ Prestamos : "is loaned"
    Socios ||--o{ Prestamos : makes

    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)
);

Now we can insert data:

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

Once a table is created, you can modify it without losing the existing data.

Add a column

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

Let's add a column for the phone in the Socios table:

ALTER TABLE Socios ADD COLUMN Telefono TEXT;

Rename a column

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

ALTER TABLE Socios RENAME COLUMN Telefono TO NumeroTelefono;

Rename a table

ALTER TABLE [tabla_antigua] RENAME TO [tabla_nueva];

ALTER TABLE Socios RENAME TO Miembros;

SQLite has limitations with ALTER TABLE. You cannot delete columns directly nor modify the type of an existing column. To do this, you must create a new table with the correct structure, copy the data and delete the old table.

DROP TABLE

To delete a complete table and all its data:

DROP TABLE [nombre_tabla];

DROP TABLE Prestamos;

Be careful! This action is irreversible. All the data in the table will be lost permanently.

If you want to avoid an error when the table does not exist:

DROP TABLE IF EXISTS [nombre_tabla];

DROP TABLE IF EXISTS Prestamos;

Indexes

Indexes improve the speed of queries, especially in large tables. They work like the index of a book: instead of reading the whole book to find a topic, you go directly to the indicated page.

CREATE INDEX

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

Create an index on the Email column of the Socios table for faster searches:

CREATE INDEX idx_socios_email ON Socios(Email);

Now the queries that filter by email will be much faster:

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

Composite indexes

You can create indexes on multiple columns:

CREATE INDEX idx_libros_autor_anio ON Libros(AutorId, AnioPublicacion);

This speeds up queries that filter by author and year simultaneously.

DROP INDEX

To delete an index:

DROP INDEX [nombre_indice];

DROP INDEX idx_socios_email;

Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE), since the index must be updated. Use them strategically on columns that you query frequently.

Unique indexes

You can create an index that also guarantees unique values:

CREATE UNIQUE INDEX idx_productos_codigo ON Productos(CodigoProducto);

This is similar to using the UNIQUE constraint, but with the additional advantage of improving performance.

Transactions

Transactions allow you to group several SQL operations into a single unit of work. Either they all run, or none of them runs. This is fundamental to keeping data integrity.

BEGIN TRANSACTION;
    -- SQL operations here
COMMIT; -- Commit the changes

Or if something goes wrong:

ROLLBACK; -- Undo all changes since BEGIN

Practical example: transferring money between bank accounts:

BEGIN TRANSACTION;

-- Subtract money from the source account
UPDATE Cuentas SET Saldo = Saldo - 100 WHERE CuentaId = 1;

-- Add money to the destination account
UPDATE Cuentas SET Saldo = Saldo + 100 WHERE CuentaId = 2;

-- If everything is fine, commit
COMMIT;

If there is an error between BEGIN and COMMIT, you can run ROLLBACK to undo all the changes:

BEGIN TRANSACTION;

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

-- Oops, we notice an error
ROLLBACK; -- Undo the previous UPDATE

This guarantees that money is never subtracted from one account without adding it to the other.

Best practices

1. Name the tables in singular or plural consistently: Usuario or Usuarios, but do not mix them.

2. Use descriptive names: FechaCreacion is better than FC or fecha1.

3. Always define a PRIMARY KEY: Each table must have a unique identifier.

4. Use FOREIGN KEY for relationships: It keeps referential integrity.

5. Set appropriate constraints: NOT NULL, CHECK, UNIQUE as appropriate.

6. Create indexes strategically: On columns that you use frequently in WHERE, JOIN or ORDER BY.

7. Use transactions for critical operations: Especially when you update multiple related tables.

8. Document your schema: Add comments explaining complex design decisions.

Viewing the structure of tables

In SQLite Browser you can see the structure in the "Database Structure" tab, but you can also use SQL:

PRAGMA table_info(nombre_tabla);

Example:

PRAGMA table_info(Customer);

This will show you all the columns, types, constraints and default values of the table.

To see all the tables of the database:

SELECT name FROM sqlite_master WHERE type='table';

To see the original CREATE TABLE command of a table:

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

Activity 1

Create a database for a school with the following tables:

1. Table Profesores with: ProfesorId, Nombre, Especialidad, Email (unique).
2. Table Asignaturas with: AsignaturaId, Nombre, ProfesorId (FK), Creditos (between 1 and 10).
3. Table Estudiantes with: EstudianteId, Nombre, Email (unique), FechaInscripcion (with a default value).
4. Table Matriculas relating students with subjects, with: MatriculaId, EstudianteId (FK), AsignaturaId (FK), Nota (between 0 and 10, can be NULL).

After creating the tables:

• Insert at least 2 teachers, 3 subjects, 3 students and 5 enrollments.
• Create an index on the Email column of the Estudiantes table.
• Add a Telefono column to the Profesores table.

Activity 2

Practice with transactions by creating a hotel booking system:

1. Create a Habitaciones table with: HabitacionId, Numero, Tipo, PrecioPorNoche, Disponible (boolean, default 1).
2. Create a Reservas table with: ReservaId, HabitacionId (FK), NombreCliente, FechaEntrada, FechaSalida, TotalPagado.
3. Insert 5 rooms, all available.
4. Use a transaction to create a booking: mark the room as unavailable (Disponible = 0) and insert the booking. If something fails, use ROLLBACK.

Solutions