Añadir/cambiar restricciones y redefinición de tablas

Los datos que vamos a usar — legacy_user

Las tablas en producción a menudo terminan necesitando restricciones que no se incluyeron en el diseño original.

Cosas como "añade un valor por defecto a esta columna", "haz que email no admita duplicados (UNIQUE)" o "marca este campo obligatorio como NOT NULL".

Este artículo cubre la adición de columnas y la retroadaptación de valores por defecto, y el procedimiento de reconstrucción de tabla para añadir restricciones a una tabla existente.

Antes de los ejercicios, échale un vistazo a las definiciones de columnas y a los datos de ejemplo de la tabla legacy_user.

① Ejecuta PRAGMA table_info(legacy_user); para comprobar nombres de columnas, tipos, clave primaria, NOT NULL y valores por defecto.

② Ejecuta SELECT * FROM legacy_user; para previsualizar todas las filas.

ALTER TABLE ADD COLUMN — añade una columna y un valor por defecto a posteriori

Para añadir una columna nueva a una tabla existente, usa ALTER TABLE tabla ADD COLUMN nombre_col tipo restriccion.

Añadir DEFAULT valor establece el valor por defecto usado cuando se omite la columna, y en el momento del ALTER el valor también rellena todas las filas existentes.

ADD COLUMN incluso acepta NOT NULL cuando se combina con DEFAULT (las filas existentes se rellenan con el valor por defecto, así que no violan NOT NULL).

Por otro lado, ADD COLUMN no puede añadir directamente UNIQUE o un NOT NULL sin valor por defecto.

Esos requieren una "reconstrucción de tabla".

-- Añade una columna con DEFAULT (usa una tabla desechable para ver el comportamiento)
DROP TABLE IF EXISTS alter_demo;
CREATE TABLE alter_demo(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO alter_demo VALUES (1,'Alice'),(2,'Bob');

-- Añade created_on con un DEFAULT → las filas existentes también reciben el valor
ALTER TABLE alter_demo ADD COLUMN created_on TEXT DEFAULT '2024-01-01';

-- NOT NULL también está bien si se combina con DEFAULT
ALTER TABLE alter_demo ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM alter_demo ORDER BY id;

Imagina el requisito: "añade una columna status a la tabla user y trata a todos los usuarios existentes como active". (Ejecútalo correctamente y aparecerá la explicación.)

① Añade a legacy_user una columna TEXT llamada status con un valor por defecto de 'active'.

② Luego ejecuta SELECT * FROM legacy_user; y confirma que la columna status de las 2 filas existentes está rellenada con el valor por defecto.

Añadir restricciones a posteriori implica reconstruir la tabla

En MySQL puedes añadir restricciones directamente a una tabla existente, como ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);.

En la consola de este curso, retroadaptar UNIQUE o NOT NULL a una tabla existente pasa por el procedimiento de reconstrucción de tabla: "crea una tabla nueva con las restricciones que quieres, copia los datos existentes y reemplaza la tabla vieja".

MySQL / PostgreSQL también ejecutan internamente este patrón de reconstrucción para algunas operaciones (como cambios de tipo de columna), según la herramienta.

El procedimiento sigue el diagrama de abajo: desactiva la comprobación de claves foráneas, luego CREATE la tabla nueva → copia todas las filas → DROP la tabla vieja → RENAME la tabla nueva → restaura la comprobación.

Sobre ADD CONSTRAINT / AUTO_INCREMENT / comentarios de columna de MySQL

La sintaxis ALTER TABLE ... ADD CONSTRAINT, AUTO_INCREMENT y comentarios de columna (COMMENT '...') que se muestra abajo es sintaxis de MySQL.

La consola del navegador de este curso no puede ejecutarlos, así que estos dos bloques code son solo de lectura (no intentes ejecutarlos).

En la consola de este curso, retroadapta restricciones mediante la reconstrucción de tabla de la sección de abajo, y usa INTEGER PRIMARY KEY para la autonumeración.

-- Sintaxis de MySQL (no la ejecutes en la consola del curso — solo léela)

-- MySQL: añade una restricción UNIQUE directamente a una tabla existente
ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);

-- MySQL: cambia a NOT NULL mediante MODIFY
ALTER TABLE legacy_user MODIFY email VARCHAR(255) NOT NULL;

-- MySQL: la autonumeración es el atributo de columna AUTO_INCREMENT; las descripciones son comentarios de columna
CREATE TABLE member (
  member_id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE COMMENT 'login email'
);

-- En la consola de este curso, consigue el equivalente de AUTO_INCREMENT con INTEGER PRIMARY KEY
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Esqueleto de la reconstrucción (usa la tabla desechable rebuild_demo para ver el comportamiento)
DROP TABLE IF EXISTS rebuild_demo;
CREATE TABLE rebuild_demo(id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO rebuild_demo VALUES (1,'a@example.com'),(2,'b@example.com');

-- ① Desactiva la comprobación de claves foráneas
PRAGMA foreign_keys=OFF;

-- ② Tabla nueva con las restricciones deseadas (email NOT NULL UNIQUE)
CREATE TABLE rebuild_demo_new(
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- ③ Copia los datos existentes
INSERT INTO rebuild_demo_new(id, email)
SELECT id, email FROM rebuild_demo;

-- ④ Elimina la tabla vieja, ⑤ renombra la nueva al nombre viejo
DROP TABLE rebuild_demo;
ALTER TABLE rebuild_demo_new RENAME TO rebuild_demo;

-- ⑥ Restaura la comprobación de claves foráneas
PRAGMA foreign_keys=ON;

-- Confirma que las restricciones están activas (un email duplicado dará error ahora)
SELECT sql FROM sqlite_master WHERE name='rebuild_demo';

Imagina el requisito: "cambia la columna email de legacy_user a obligatoria y sin duplicados (NOT NULL y UNIQUE)". Ejecuta el procedimiento principal de reconstrucción de tabla del artículo, paso a paso, tal cual lo muestra el diagrama de arriba.

① Desactiva la comprobación de claves foráneas (PRAGMA foreign_keys=OFF;).

② CREATE una tabla nueva llamada legacy_user_new con id (entero clave primaria), name (TEXT) y email (TEXT, NOT NULL, UNIQUE).

③ Copia todas las filas de la tabla existente con INSERT INTO legacy_user_new(...) SELECT ... FROM legacy_user;.

④ DROP la legacy_user vieja y RENAME legacy_user_new a legacy_user.

⑤ Restaura la comprobación de claves foráneas a ON.

⑥ Por último, ejecuta SELECT sql FROM sqlite_master WHERE name='legacy_user'; y confirma que el esquema nuevo contiene UNIQUE / NOT NULL.

Reproduce la misma reconstrucción sobre una tabla desechable y experimenta cómo la restricción UNIQUE del nuevo esquema bloquea de verdad un duplicado. Este ejercicio es autocontenido y usa una tabla dedicada email_check.

① DROP TABLE IF EXISTS email_check; y luego CREATE con id (entero clave primaria) y email (TEXT, NOT NULL, UNIQUE).

② INSERT una fila (1, 'a@example.com') en email_check.

③ Luego INSERT otra fila (2, 'a@example.com') cuyo valor de email ya existe. Viola la restricción UNIQUE, así que un error es el resultado correcto para el segundo INSERT.