Lección 9.2 · Tiempo de lectura: ~9 min

La sentencia SQL ALTER TABLE cambia la estructura de una tabla existente sin volver a crearla: añade, modifica, renombra y elimina columnas, y añade o elimina restricciones. En esta lección verás la sintaxis de cada cambio en MySQL y PostgreSQL, por qué los datos existentes pueden impedir un cambio y cómo encontrar y eliminar una restricción por su nombre.

ALTER TABLE en SQL: modificar una tabla existente

En la lección anterior creaste tablas con CREATE TABLE. Los requisitos cambian: una tabla necesita una columna nueva, un campo de texto más largo o una restricción olvidada. Eliminar y volver a crear la tabla borraría sus datos, así que la estructura se cambia en el sitio con ALTER TABLE.

Las bases de datos de ejemplo de SQLtest son de solo lectura, así que allí estas sentencias devuelven un error de permisos. Los ejemplos usan el esquema Sakila y se han comprobado en MySQL 8.0 y PostgreSQL 17.

Sintaxis de ALTER TABLE

ALTER TABLE table_name
    action [, action ...];

La acción (action) indica qué cambiar. Un solo ALTER TABLE puede contener varias acciones separadas por comas. Las más habituales:

Añadir una columna

ALTER TABLE customer
ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;

Resultado: cada cliente existente recibe loyalty_points = 0; las filas nuevas reciben 0, salvo que el INSERT indique un valor.

La columna nueva se añade al final de la tabla. MySQL puede colocarla en otro sitio con AFTER column_name o FIRST; PostgreSQL siempre la añade al final.

NOT NULL sin valor por defecto

Si la tabla ya tiene filas, una columna NOT NULL necesita un valor para ellas:

ALTER TABLE customer
ADD COLUMN phone VARCHAR(20) NOT NULL;

Resultado en PostgreSQL: un error, column "phone" contains null values. MySQL acepta la sentencia y rellena las filas existentes con el valor por defecto implícito del tipo: aquí una cadena vacía, 0 para los números.

Por eso, al añadir una columna obligatoria a una tabla con datos, indica siempre un DEFAULT, o añade la columna admitiendo NULL, rellénala con UPDATE y solo después hazla NOT NULL.

Modificar una columna

Para alargar la columna email:

-- MySQL
ALTER TABLE customer
MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- PostgreSQL
ALTER TABLE customer
ALTER COLUMN email TYPE VARCHAR(100);

Las dos bases de datos tratan de forma distinta el resto de la definición de la columna:

• MODIFY en MySQL sustituye toda la definición de la columna. Lo que no repites se pierde: ALTER TABLE customer MODIFY active SMALLINT; convierte una columna NOT NULL DEFAULT 1 en una columna que admite NULL y no tiene valor por defecto. Escribe siempre la definición completa: MODIFY active SMALLINT NOT NULL DEFAULT 1.
• ALTER COLUMN en PostgreSQL cambia una propiedad cada vez y conserva las demás:

ALTER TABLE customer
    ALTER COLUMN active TYPE INT,
    ALTER COLUMN active SET DEFAULT 1,
    ALTER COLUMN email SET NOT NULL;

MySQL también tiene CHANGE COLUMN old_name new_name definition, que renombra y redefine una columna en un solo paso.

Cuando los datos no caben

La base de datos comprueba las filas existentes frente a la nueva definición:

• Acortar VARCHAR(50) a VARCHAR(10) falla si algún valor es más largo: "Data truncated for column 'email'" en MySQL, "value too long for type character varying(10)" en PostgreSQL.
• Añadir NOT NULL falla si la columna contiene NULL: "Invalid use of NULL value" en MySQL, "column contains null values" en PostgreSQL.

Corrige primero los datos, por ejemplo UPDATE customer SET email = '' WHERE email IS NULL;, y repite el ALTER TABLE.

Renombrar y eliminar

ALTER TABLE customer RENAME COLUMN email TO email_address;

ALTER TABLE customer DROP COLUMN loyalty_points;

ALTER TABLE customer RENAME TO client;

Resultado: la columna recibe un nombre nuevo, la columna loyalty_points se elimina junto con sus datos y la tabla se renombra.

RENAME COLUMN funciona en MySQL 8.0, MariaDB 10.5 y PostgreSQL; SQL Server usa el procedimiento sp_rename. Renombrar no actualiza tus consultas, vistas ni el código de la aplicación: todo lo que use el nombre antiguo deja de funcionar.

DROP COLUMN no se puede deshacer con ROLLBACK en MySQL, porque el DDL confirma implícitamente (consulta la lección 8.4). Haz una copia de los datos que puedas necesitar.

Añadir restricciones

Las restricciones de CREATE TABLE se pueden añadir más tarde. Da un nombre a cada una con CONSTRAINT name para encontrarla y eliminarla fácilmente:

ALTER TABLE customer
ADD CONSTRAINT uq_customer_email UNIQUE (email);

ALTER TABLE payment
ADD CONSTRAINT chk_payment_amount CHECK (amount >= 0);

ALTER TABLE payment
ADD CONSTRAINT fk_payment_customer
    FOREIGN KEY (customer_id) REFERENCES customer (customer_id);

Una clave primaria se añade de la misma forma: ALTER TABLE table_name ADD PRIMARY KEY (column_name);.

Los datos existentes pueden impedir una restricción

Una restricción nueva se aplica a las filas que ya están en la tabla. Si alguna fila la incumple, toda la sentencia falla:

Busca las filas problemáticas con un SELECT antes de añadir la restricción. Por ejemplo, los pagos que hacen referencia a un cliente inexistente:

SELECT p.payment_id, p.customer_id
FROM payment AS p
LEFT JOIN customer AS c ON c.customer_id = p.customer_id
WHERE c.customer_id IS NULL;

Resultado: las filas huérfanas que bloquearían la clave foránea. Corrígelas o elimínalas y después añade la restricción.

Encontrar y eliminar restricciones

Para eliminar una restricción necesitas su nombre. Si no se indicó ninguno, la base de datos generó uno, como payment_ibfk_1 en MySQL o payment_customer_id_fkey en PostgreSQL. La vista information_schema.table_constraints los muestra:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.table_constraints
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'payment'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

Resultado en Sakila: fk_payment_customer, fk_payment_rental y fk_payment_staff (FOREIGN KEY) y PRIMARY (PRIMARY KEY). En PostgreSQL, filtra por table_schema = 'public' en lugar de DATABASE().

Después elimínala:

-- PostgreSQL, SQL Server and MySQL 8.0.19+
ALTER TABLE payment DROP CONSTRAINT fk_payment_customer;

-- MySQL: type-specific forms
ALTER TABLE payment DROP FOREIGN KEY fk_payment_customer;
ALTER TABLE payment DROP CHECK chk_payment_amount;
ALTER TABLE customer DROP INDEX uq_customer_email;

En MySQL, una restricción UNIQUE es un índice, por eso se elimina con DROP INDEX.

ALTER TABLE en tablas grandes

En una tabla con millones de filas, algunos cambios reescriben toda la tabla y bloquean las escrituras mientras se ejecutan. Añadir una columna que admite NULL o una columna con un valor por defecto constante es rápido en las versiones actuales: MySQL 8.0 lo hace al instante (ALGORITHM=INSTANT), igual que PostgreSQL 11 y posteriores. Cambiar el tipo de una columna, añadir una clave foránea a una tabla grande o eliminar una columna en versiones antiguas puede tardar mucho.

En bases de datos de producción, prueba antes el cambio de esquema en una copia y ejecútalo cuando la carga sea baja.

Puntos clave:

• ALTER TABLE modifica una tabla existente en el sitio: columnas, restricciones y el nombre de la tabla.
• Añade las columnas obligatorias a tablas con datos con un DEFAULT, o rellénalas antes de poner NOT NULL.
• En MySQL, MODIFY sustituye toda la definición de la columna; repite NOT NULL y DEFAULT o se perderán.
• Las filas existentes deben cumplir el nuevo tipo, NOT NULL o la restricción; si no, la sentencia falla.
• Pon nombre a tus restricciones; busca las que no lo tienen en information_schema.table_constraints y elimínalas con DROP CONSTRAINT.
• En MySQL, ALTER TABLE confirma implícitamente y no se puede deshacer.

Preguntas frecuentes

¿Cómo añadir una columna a una tabla existente en SQL?

Usa ALTER TABLE table_name ADD COLUMN column_name data_type;, por ejemplo ALTER TABLE customer ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;. Las filas existentes reciben el valor por defecto, o NULL si la columna no tiene valor por defecto.

¿Cómo cambiar el tipo de datos de una columna en SQL?

En MySQL, ALTER TABLE customer MODIFY COLUMN email VARCHAR(100) NOT NULL; con la definición completa de la columna. En PostgreSQL, ALTER TABLE customer ALTER COLUMN email TYPE VARCHAR(100);. El cambio falla si los valores existentes no caben en el nuevo tipo.

¿Cómo añadir una clave foránea a una tabla existente?

ALTER TABLE payment ADD CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id);. Cada customer_id existente en payment debe existir en customer; si no, la sentencia falla.

¿Cómo eliminar una restricción si no sé su nombre?

Búscala en information_schema.table_constraints para la tabla y ejecuta ALTER TABLE table_name DROP CONSTRAINT constraint_name;. En MySQL, las claves foráneas también se pueden eliminar con DROP FOREIGN KEY y las restricciones de unicidad con DROP INDEX.

Preguntas de entrevista

¿Qué diferencia hay entre MODIFY COLUMN en MySQL y ALTER COLUMN en PostgreSQL?

MODIFY COLUMN sustituye la definición completa de la columna, así que los atributos que no repites, como NOT NULL o DEFAULT, se eliminan. ALTER COLUMN en PostgreSQL cambia una propiedad, como el tipo o el valor por defecto, y conserva el resto.

¿Por qué puede fallar añadir NOT NULL o UNIQUE a una tabla existente?

La restricción se comprueba sobre las filas que ya están en la tabla. NOT NULL falla si hay algún NULL, UNIQUE falla con duplicados y una clave foránea falla con valores que no existen en la tabla referenciada. Limpia antes los datos.

¿Cómo añadirías una columna obligatoria a una tabla grande en producción?

Añadirla con un DEFAULT constante, que es un cambio rápido de metadatos en MySQL 8.0 y PostgreSQL 11+; o añadirla admitiendo NULL, rellenarla por lotes con UPDATE y después poner NOT NULL. Probar en una copia y ejecutarlo con poca carga.