Crear y eliminar tablas

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255),
    age INTEGER
);

INTEGER y VARCHAR(255) son tipos de datos: numérico y de cadena, respectivamente. Hablaremos de ellos con más detalle en los próximos artículos.

• SERIAL o GENERATED ALWAYS AS IDENTITY

  Indica que el valor de esta columna se incrementará automáticamente al añadir registros nuevos a la tabla. SERIAL es una forma abreviada de crear una columna autoincremental.

MySQL 8.4
CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18
);

• id es una columna de tipo SERIAL (entero autoincremental) que es la clave primaria;

MySQL 8.4
CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Para ver la descripción de la tabla creada, puedes consultar el esquema de información:

MySQL 8.4
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = current_schema() AND table_name = 'users';

Clave primaria

Si no has definido la clave primaria en los parámetros de la columna, puedes hacerlo con los parámetros adicionales de la tabla, añadiendo PRIMARY KEY (columna [, ...]) después de la lista de columnas:

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    PRIMARY KEY (id)
);

Claves foráneas

Supongamos que queremos guardar datos sobre la empresa en la que trabajan nuestros usuarios. Vamos a crear una pequeña tabla Companies, en la que guardaremos el identificador único y el nombre de la empresa:

MySQL 8.4
CREATE TABLE Companies (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

A continuación, hay que añadir a la tabla Users la columna company, el lugar de trabajo de nuestro usuario, que hará referencia a un registro de la tabla Companies. La consulta completa para crear la tabla quedará así:

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    company INTEGER,
    PRIMARY KEY (id)
);

Para garantizar, al añadir registros nuevos a la tabla Users, que la columna company contenga un identificador existente en la tabla Companies, se usa una clave foránea. Su sintaxis es la siguiente:

FOREIGN KEY (columna [, ...])
REFERENCES tabla_externa (columna_tabla_externa [, ...])
[ON DELETE accion]
[ON UPDATE accion]

La consulta completa para crear la tabla con una clave foránea será esta:

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    company INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (company) REFERENCES Companies (id)
);

Cuando hay claves foráneas, puedes definir cómo se comporta el registro actual cuando se modifica o se elimina el registro al que hace referencia.

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    company INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (company) REFERENCES Companies (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

ON DELETE RESTRICT significa que, si intentas eliminar una empresa que tiene datos en la tabla Users, la base de datos no te dejará hacerlo:

MySQL 8.4
ERROR:  update or delete on table "companies" violates foreign key constraint "users_company_fkey" on table "users"
DETAIL:  Key (id)=(1) is still referenced from table "users".

Si se hubiera indicado ON DELETE CASCADE, al eliminar la empresa se eliminarían todos los usuarios que hacen referencia a ella.

Hay otra opción más: ON DELETE SET NULL. Con ella, la base de datos escribirá NULL en la columna company de todos los usuarios que trabajaban en la empresa eliminada.

ON UPDATE CASCADE significa que, si una empresa cambia su identificador, todos los usuarios (Users) recibirán el nuevo identificador en la columna company.