Tablas, tipos y el id automático - Bases de datos SQL

Una tabla es un compromiso

Crear una tabla no es reservar espacio: es declarar las reglas de lo que se va a poder guardar ahí. Qué columnas hay, de qué tipo es cada una, cuáles son obligatorias y cuáles no pueden repetirse.

A partir de ese momento, la base de datos se encarga de que nadie las incumpla. Es lo que la lección 1 llamaba «se niega a guardar lo que no tiene sentido».

La mayor diferencia entre los tres motores

Casi todas las tablas tienen una columna id que se numera sola: 1, 2, 3… No la escribes tú, la pone la base de datos.

Los tres motores hacen exactamente lo mismo y lo escriben de tres formas distintas. Es la incompatibilidad número uno al mover una base de datos de un motor a otro.

PostgreSQL

CREATE TABLE clientes (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nombre      VARCHAR(100) NOT NULL,
    email       VARCHAR(150) UNIQUE,
    ciudad      VARCHAR(80),
    saldo       DECIMAL(12,2) NOT NULL DEFAULT 0,
    activo      BOOLEAN NOT NULL DEFAULT TRUE,
    creado_en   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

MySQL

CREATE TABLE clientes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nombre      VARCHAR(100) NOT NULL,
    email       VARCHAR(150) UNIQUE,
    ciudad      VARCHAR(80),
    saldo       DECIMAL(12,2) NOT NULL DEFAULT 0,
    activo      BOOLEAN NOT NULL DEFAULT TRUE,
    creado_en   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

SQL Server

CREATE TABLE clientes (
    id          INT IDENTITY(1,1) PRIMARY KEY,
    nombre      VARCHAR(100) NOT NULL,
    email       VARCHAR(150) UNIQUE,
    ciudad      VARCHAR(80),
    saldo       DECIMAL(12,2) NOT NULL DEFAULT 0,
    activo      BIT NOT NULL DEFAULT 1,
    creado_en   DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

Las tres formas, en una frase

GENERATED ALWAYS AS IDENTITY

PostgreSQL. Es el estándar del lenguaje y la forma recomendada hoy.

Verás mucho SERIAL en código antiguo: hace lo mismo, es la forma heredada.

AUTO_INCREMENT

MySQL. Va pegado al tipo de la columna.

Es propio de MySQL: no funciona en ningún otro motor.

IDENTITY(1,1)

SQL Server. Los dos números son desde dónde empieza y de cuánto en cuánto sube.

IDENTITY(100,5) empezaría en 100 y subiría de cinco en cinco.

Un detalle que la documentación de PostgreSQL dice y casi nadie recoge: una columna de identidad NO garantiza que los valores sean únicos. Solo genera el siguiente número de una secuencia.

Quien impone la unicidad es PRIMARY KEY o UNIQUE. Por eso en los tres ejemplos va PRIMARY KEY al lado: sin él tendrías una columna que se numera sola y admite repetidos si alguien inserta valores a mano.

Los tipos, y el que hay que acertar

Los tipos básicos coinciden bastante entre motores. La tabla siguiente cubre lo que se usa el 95% del tiempo.

Empareja cada tipo con lo que guarda

Arrastra cada tipo a su descripción, o toca uno y luego su espacio.

Texto de longitud variable, hasta un máximo. Es el tipo de texto de uso general.
Números enteros: cantidades, identificadores, unidades.
Números con decimales exactos. Es el tipo del dinero.
Verdadero o falso. En SQL Server se llama BIT y usa 1 y 0.
Fecha y hora juntas. En SQL Server, DATETIME2.

El dinero va en DECIMAL, nunca en un tipo de coma flotante como FLOAT o REAL.

La coma flotante no puede representar exactamente valores como 0,10: guarda una aproximación. Sumas mil facturas y aparecen diferencias de centavos que nadie sabe explicar, y que en una auditoría son un problema serio. DECIMAL(12,2) significa «hasta 12 dígitos, 2 de ellos decimales», y es exacto.

Las restricciones: el producto, no la burocracia

Cada palabra que acompaña a una columna es una regla que la base de datos va a hacer cumplir siempre, venga el dato de donde venga: de tu consulta, de la aplicación o de un archivo importado.

Las cinco restricciones que se usan

Piensa qué impone cada una antes de voltear.

Una regla de negocio que no se puede saltar

-- El descuento nunca puede pasar del 30%
ALTER TABLE ventas
    ADD CONSTRAINT chk_descuento CHECK (descuento >= 0 AND descuento <= 30);

-- A partir de aqui, esto es IMPOSIBLE de guardar:
INSERT INTO ventas (producto, total, descuento) VALUES ('Papa', 22000, 99.5);

Resultado

ERROR:  new row for relation "ventas" violates check constraint "chk_descuento"

Fíjate en lo que acaba de pasar: la regla del 30% ya no depende de que la aplicación se acuerde de comprobarla. Da igual si el dato llega desde un formulario, desde una importación o desde alguien escribiendo SQL a mano: la base de datos lo rechaza.

Una validación que solo vive en la pantalla es una sugerencia. Esta es una regla.

Modificar una tabla que ya existe

-- Anadir una columna
ALTER TABLE clientes ADD COLUMN telefono VARCHAR(30);

-- Cambiar el nombre de una columna
ALTER TABLE clientes RENAME COLUMN ciudad TO ciudad_residencia;

-- Borrar una columna (los datos de esa columna se pierden)
ALTER TABLE clientes DROP COLUMN telefono;

-- Ver como quedo la tabla
\d clientes

Decisiones que se preguntan aquí

¿VARCHAR de cuánto?

Lo suficiente para el peor caso real, sin exagerar: 100 para un nombre, 150 para un correo.

En PostgreSQL existe TEXT, sin límite y sin penalización de rendimiento; en los otros motores conviene poner un límite razonable.

¿Qué es NULL exactamente?

NULL es «no se sabe», y no es lo mismo que cero ni que texto vacío.

Un saldo en cero es un dato; un saldo NULL significa que se desconoce. La diferencia importa en las consultas, porque NULL no se compara con = sino con IS NULL.

¿Borro con DROP o con DELETE?

DELETE quita filas y la tabla sigue ahí. DROP TABLE elimina la tabla entera, con su estructura y sus datos.

DROP no pide confirmación y no tiene papelera. Antes de escribirlo, mira dos veces en qué base de datos estás.

Nombres de tablas y columnas

En minúsculas y con guion bajo -fecha_creacion-, sin tildes ni eñes ni espacios.

Los motores tratan las mayúsculas de forma distinta, y las tildes complican las conexiones desde otros programas. Es una convención que ahorra problemas reales.

Compruébalo tú mismo

Vas a guardar importes de facturas. ¿Qué tipo usas?

Declaras una columna como columna de identidad, sin más. ¿Garantiza que no habrá valores repetidos?

Necesitas que un descuento nunca supere el 30%, y el dato entra desde tres sitios distintos. ¿Dónde pones la regla?

Lo que sigue

Ya tienes la tabla clientes. En la siguiente lección metes datos y los consultas: INSERT, cómo recuperar el id que acaba de generarse -otra cosa que los tres motores hacen distinto- y SELECT con filtros, incluida la trampa de LIKE con las mayúsculas.