Modificar con ALTER

Añadir columnas

La modificación de tablas existentes es una operación habitual en el mantenimiento de bases de datos. Cuando una tabla ya está creada y contiene datos, a menudo surge la necesidad de ampliar su estructura añadiendo nuevas columnas para almacenar información adicional.

La sentencia ALTER TABLE con la cláusula ADD COLUMN nos permite añadir nuevas columnas a tablas existentes sin necesidad de recrearlas desde cero. Esta operación preserva los datos ya almacenados mientras expande la estructura de la tabla.

Sintaxis básica

La sintaxis para añadir una columna a una tabla existente es:

ALTER TABLE nombre_tabla
ADD COLUMN nombre_columna tipo_dato [restricciones];

Donde:

• nombre_tabla es el nombre de la tabla que queremos modificar
• nombre_columna es el nombre de la nueva columna
• tipo_dato es el tipo de datos que almacenará la columna
• restricciones son opcionales y pueden incluir NOT NULL, DEFAULT, CHECK, etc.

Ejemplos prácticos

Veamos algunos ejemplos para entender mejor cómo añadir columnas:

Ejemplo 1: Añadir una columna simple

Supongamos que tenemos una tabla empleados y queremos añadir una columna para almacenar el número de teléfono:

ALTER TABLE empleados
ADD COLUMN telefono VARCHAR(15);

Esta sentencia añade una columna llamada telefono que puede almacenar hasta 15 caracteres. Como no especificamos ninguna restricción, la columna permitirá valores NULL.

Ejemplo 2: Añadir una columna con valor predeterminado

Si queremos añadir una columna activo para indicar si un empleado está en activo, con un valor predeterminado:

ALTER TABLE empleados
ADD COLUMN activo BOOLEAN DEFAULT TRUE;

Todos los registros existentes tendrán automáticamente el valor TRUE en esta nueva columna.

Ejemplo 3: Añadir una columna NOT NULL con valor predeterminado

Para añadir una columna que no permita valores nulos, debemos proporcionar un valor predeterminado (a menos que la tabla esté vacía):

ALTER TABLE empleados
ADD COLUMN fecha_modificacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Esta sentencia añade una columna que registra automáticamente la fecha y hora actual como valor predeterminado.

Añadir múltiples columnas a la vez

En MySQL y PostgreSQL podemos añadir varias columnas en una sola operación, lo que resulta más eficiente que ejecutar múltiples sentencias ALTER TABLE:

ALTER TABLE productos
ADD COLUMN stock INTEGER DEFAULT 0,
ADD COLUMN precio_oferta DECIMAL(10,2),
ADD COLUMN fecha_oferta DATE;

Esta sentencia añade tres columnas a la tabla productos en una sola operación.

Consideraciones importantes

Al añadir columnas a tablas existentes, debemos tener en cuenta algunas consideraciones:

• Rendimiento: Añadir columnas a tablas muy grandes puede ser una operación costosa, especialmente si se especifica NOT NULL sin un valor DEFAULT, ya que el sistema debe recorrer todos los registros.

• Compatibilidad: Aunque la sintaxis básica es similar, existen diferencias entre MySQL y PostgreSQL:

En MySQL:

  ALTER TABLE clientes
  ADD COLUMN puntos_fidelidad INTEGER AFTER email;

En PostgreSQL no se admite la cláusula AFTER, pero podemos modificar el orden de visualización más adelante con otras herramientas.

• Valores por defecto: Cuando añadimos una columna a una tabla con datos existentes:

• Si la columna permite NULL, los registros existentes tendrán NULL en la nueva columna.

• Si especificamos NOT NULL con un DEFAULT, todos los registros existentes recibirán ese valor predeterminado.

• Si especificamos NOT NULL sin DEFAULT en una tabla con datos, la operación fallará en la mayoría de los sistemas.

Añadir columnas con restricciones avanzadas

También podemos añadir columnas con restricciones más complejas:

ALTER TABLE pedidos
ADD COLUMN total DECIMAL(10,2) CHECK (total >= 0);

Esta sentencia añade una columna total que solo acepta valores positivos o cero.

ALTER TABLE usuarios
ADD COLUMN email VARCHAR(100) UNIQUE;

Aquí añadimos una columna email que debe contener valores únicos en toda la tabla.

Añadir columnas generadas (calculadas)

En versiones recientes de MySQL y PostgreSQL, podemos añadir columnas generadas o calculadas:

En MySQL:

ALTER TABLE productos
ADD COLUMN precio_con_iva DECIMAL(10,2) GENERATED ALWAYS AS (precio * 1.21) STORED;

En PostgreSQL:

ALTER TABLE productos
ADD COLUMN precio_con_iva DECIMAL(10,2) GENERATED ALWAYS AS (precio * 1.21) STORED;

Estas columnas calculan automáticamente su valor basándose en otras columnas, lo que mantiene la consistencia de los datos y evita cálculos repetitivos en las consultas.

Ejemplo práctico completo

Veamos un ejemplo más completo. Supongamos que tenemos una tabla de ventas y queremos añadir columnas para un nuevo sistema de análisis:

-- Primero, añadimos una columna para categorizar las ventas
ALTER TABLE ventas
ADD COLUMN categoria_venta VARCHAR(50);

-- Añadimos una columna para el margen de beneficio
ALTER TABLE ventas
ADD COLUMN margen_beneficio DECIMAL(5,2);

-- Añadimos una columna calculada para el beneficio neto
ALTER TABLE ventas
ADD COLUMN beneficio_neto DECIMAL(10,2) GENERATED ALWAYS AS (importe_venta - coste) STORED;

-- Añadimos una columna para análisis temporal
ALTER TABLE ventas
ADD COLUMN trimestre SMALLINT GENERATED ALWAYS AS (EXTRACT(QUARTER FROM fecha_venta)) STORED;

Este conjunto de operaciones amplía significativamente las capacidades analíticas de nuestra tabla de ventas, permitiéndonos realizar análisis más detallados sin modificar los datos existentes.

Modificar columnas

Una vez que una tabla está en uso, es común necesitar modificar las características de columnas existentes. Esto puede incluir cambiar el tipo de datos, ajustar el tamaño de un campo, añadir o eliminar restricciones, o modificar valores predeterminados. La sentencia ALTER TABLE con las cláusulas MODIFY COLUMN (MySQL) o ALTER COLUMN (PostgreSQL) nos permite realizar estos cambios sin tener que recrear la tabla.

Sintaxis según el sistema de gestión

La sintaxis para modificar columnas varía ligeramente entre MySQL y PostgreSQL:

En MySQL:

ALTER TABLE nombre_tabla
MODIFY COLUMN nombre_columna nuevo_tipo_dato [nuevas_restricciones];

En PostgreSQL:

ALTER TABLE nombre_tabla
ALTER COLUMN nombre_columna TYPE nuevo_tipo_dato [USING expresión_conversión];

ALTER TABLE nombre_tabla
ALTER COLUMN nombre_columna SET DEFAULT valor;

ALTER TABLE nombre_tabla
ALTER COLUMN nombre_columna [SET | DROP] NOT NULL;

Esta diferencia sintáctica es importante ya que PostgreSQL separa las operaciones de modificación en comandos más específicos.

Cambiar el tipo de datos

Uno de los cambios más comunes es modificar el tipo de datos de una columna:

En MySQL:

ALTER TABLE productos
MODIFY COLUMN descripcion TEXT;

Este comando cambia la columna descripcion al tipo TEXT, que puede almacenar textos más largos.

En PostgreSQL:

ALTER TABLE productos
ALTER COLUMN descripcion TYPE TEXT;

Ampliar o reducir el tamaño de un campo

Para ajustar la longitud de un campo de tipo cadena:

En MySQL:

ALTER TABLE clientes
MODIFY COLUMN telefono VARCHAR(20);

Este comando amplía el campo telefono para almacenar hasta 20 caracteres.

En PostgreSQL:

ALTER TABLE clientes
ALTER COLUMN telefono TYPE VARCHAR(20);

Conversión de tipos con reglas personalizadas

Cuando la conversión entre tipos no es directa, podemos especificar cómo debe realizarse:

En MySQL:

ALTER TABLE ventas
MODIFY COLUMN importe DECIMAL(10,2);

En PostgreSQL:

ALTER TABLE ventas
ALTER COLUMN importe TYPE DECIMAL(10,2) USING importe::DECIMAL(10,2);

La cláusula USING en PostgreSQL permite definir cómo convertir los datos existentes al nuevo tipo.

Añadir o eliminar valores predeterminados

Para modificar el valor predeterminado de una columna:

En MySQL:

ALTER TABLE usuarios
MODIFY COLUMN estado VARCHAR(20) DEFAULT 'activo';

En PostgreSQL:

ALTER TABLE usuarios
ALTER COLUMN estado SET DEFAULT 'activo';

Para eliminar un valor predeterminado:

En MySQL:

ALTER TABLE usuarios
MODIFY COLUMN estado VARCHAR(20) DEFAULT NULL;

En PostgreSQL:

ALTER TABLE usuarios
ALTER COLUMN estado DROP DEFAULT;

Añadir o eliminar restricciones NOT NULL

Para hacer que una columna no acepte valores nulos:

En MySQL:

ALTER TABLE pedidos
MODIFY COLUMN fecha_entrega DATE NOT NULL;

En PostgreSQL:

ALTER TABLE pedidos
ALTER COLUMN fecha_entrega SET NOT NULL;

Para permitir valores nulos en una columna:

En MySQL:

ALTER TABLE pedidos
MODIFY COLUMN comentarios TEXT NULL;

En PostgreSQL:

ALTER TABLE pedidos
ALTER COLUMN comentarios DROP NOT NULL;

Cambios que requieren precaución

Algunos cambios en las columnas pueden ser potencialmente peligrosos si no se manejan correctamente:

• Reducir la longitud de un campo: Si intentamos reducir el tamaño de un campo que ya contiene datos más largos que el nuevo límite, la operación fallará o podría truncar los datos.

-- Verificar primero la longitud máxima actual
SELECT MAX(LENGTH(nombre)) FROM clientes;

-- Si es seguro, entonces reducir
ALTER TABLE clientes
MODIFY COLUMN nombre VARCHAR(50);

• Cambiar de un tipo numérico a texto: Esta conversión suele ser segura.

• Cambiar de texto a numérico: Esta conversión puede fallar si algún valor no puede convertirse a número.

-- En PostgreSQL, podemos usar USING para manejar la conversión
ALTER TABLE productos
ALTER COLUMN codigo TYPE INTEGER USING CASE 
    WHEN codigo ~ '^[0-9]+$' THEN codigo::INTEGER 
    ELSE NULL 
END;

Modificar múltiples columnas a la vez

Para mejorar la eficiencia, podemos modificar varias columnas en una sola operación:

En MySQL:

ALTER TABLE empleados
    MODIFY COLUMN nombre VARCHAR(100) NOT NULL,
    MODIFY COLUMN apellido VARCHAR(100) NOT NULL,
    MODIFY COLUMN fecha_contratacion DATE NOT NULL;

En PostgreSQL:

ALTER TABLE empleados
    ALTER COLUMN nombre TYPE VARCHAR(100),
    ALTER COLUMN nombre SET NOT NULL,
    ALTER COLUMN apellido TYPE VARCHAR(100),
    ALTER COLUMN apellido SET NOT NULL,
    ALTER COLUMN fecha_contratacion SET NOT NULL;

Renombrar columnas

Aunque técnicamente es una operación diferente, renombrar columnas suele considerarse parte de la modificación de columnas:

En MySQL:

ALTER TABLE productos
CHANGE COLUMN precio precio_unitario DECIMAL(10,2);

En MySQL, CHANGE COLUMN permite cambiar el nombre y también el tipo de datos en una sola operación.

En PostgreSQL:

ALTER TABLE productos
RENAME COLUMN precio TO precio_unitario;

Ejemplo práctico: Evolución de una tabla

Veamos un ejemplo completo de cómo podría evolucionar una tabla de clientes a lo largo del tiempo:

-- Supongamos que inicialmente teníamos una tabla simple
-- Ahora necesitamos hacer varios cambios

-- 1. Ampliar el campo de email para direcciones más largas
ALTER TABLE clientes
MODIFY COLUMN email VARCHAR(255);

-- 2. Cambiar el tipo de teléfono a VARCHAR para permitir formatos internacionales
ALTER TABLE clientes
MODIFY COLUMN telefono VARCHAR(20);

-- 3. Hacer que el nombre y apellido sean obligatorios
ALTER TABLE clientes
MODIFY COLUMN nombre VARCHAR(100) NOT NULL,
MODIFY COLUMN apellido VARCHAR(100) NOT NULL;

-- 4. Añadir un valor predeterminado para la fecha de registro
ALTER TABLE clientes
MODIFY COLUMN fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 5. Cambiar el campo de código postal para asegurar formato uniforme
ALTER TABLE clientes
MODIFY COLUMN codigo_postal CHAR(5);

En PostgreSQL, estas operaciones se escribirían así:

-- 1. Ampliar el campo de email
ALTER TABLE clientes
ALTER COLUMN email TYPE VARCHAR(255);

-- 2. Cambiar el tipo de teléfono
ALTER TABLE clientes
ALTER COLUMN telefono TYPE VARCHAR(20);

-- 3. Hacer campos obligatorios
ALTER TABLE clientes
ALTER COLUMN nombre SET NOT NULL,
ALTER COLUMN apellido SET NOT NULL;

-- 4. Añadir valor predeterminado
ALTER TABLE clientes
ALTER COLUMN fecha_registro SET DEFAULT CURRENT_TIMESTAMP;

-- 5. Cambiar formato de código postal
ALTER TABLE clientes
ALTER COLUMN codigo_postal TYPE CHAR(5);

Consideraciones de rendimiento

Modificar columnas en tablas grandes puede tener un impacto significativo en el rendimiento:

• Las operaciones que solo cambian metadatos (como añadir/quitar DEFAULT o NULL) suelen ser rápidas.
• Cambiar el tipo de datos o la longitud puede requerir reescribir toda la tabla.
• En tablas muy grandes, considere realizar estos cambios durante periodos de baja actividad.
• En PostgreSQL, algunas operaciones de tipo ALTER COLUMN adquieren un bloqueo exclusivo sobre la tabla.

-- En PostgreSQL, para operaciones en tablas grandes, a veces es mejor
-- crear una nueva columna, migrar los datos y luego renombrar
ALTER TABLE grandes_datos ADD COLUMN nuevo_campo TEXT;
UPDATE grandes_datos SET nuevo_campo = campo_antiguo::TEXT;
ALTER TABLE grandes_datos DROP COLUMN campo_antiguo;
ALTER TABLE grandes_datos RENAME COLUMN nuevo_campo TO campo_antiguo;

Esta estrategia permite mantener la tabla disponible durante más tiempo durante el proceso de migración.

Gestionar restricciones

Las restricciones son reglas que se aplican a las columnas de una tabla para mantener la integridad y consistencia de los datos. Cuando una tabla ya está creada y en uso, a menudo necesitamos añadir, modificar o eliminar estas restricciones para adaptarnos a nuevos requisitos del negocio. La sentencia ALTER TABLE nos permite gestionar estas restricciones sin necesidad de recrear la tabla desde cero.

Tipos de restricciones que podemos gestionar

En SQL existen varios tipos de restricciones que podemos manipular:

• PRIMARY KEY: Identifica de forma única cada registro en una tabla
• FOREIGN KEY: Establece relaciones entre tablas
• UNIQUE: Asegura que todos los valores en una columna sean diferentes
• CHECK: Garantiza que los valores en una columna cumplan una condición específica
• NOT NULL: Impide que una columna acepte valores nulos
• DEFAULT: Establece un valor predeterminado

Añadir restricciones a tablas existentes

Añadir una clave primaria

Para añadir una clave primaria a una tabla existente:

ALTER TABLE empleados
ADD PRIMARY KEY (id_empleado);

Si necesitamos una clave primaria compuesta por múltiples columnas:

ALTER TABLE detalle_pedido
ADD PRIMARY KEY (id_pedido, id_producto);

Añadir una clave foránea

Para establecer una relación entre tablas mediante una clave foránea:

ALTER TABLE pedidos
ADD CONSTRAINT fk_cliente_pedido
FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente);

La restricción incluye un nombre (fk_cliente_pedido) que nos permitirá identificarla fácilmente si necesitamos modificarla o eliminarla posteriormente.

También podemos especificar el comportamiento cuando se elimina o actualiza el registro referenciado:

ALTER TABLE pedidos
ADD CONSTRAINT fk_cliente_pedido
FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente)
ON DELETE CASCADE
ON UPDATE RESTRICT;

Las opciones más comunes para ON DELETE y ON UPDATE son:

• CASCADE: Propaga la acción a los registros relacionados
• RESTRICT: Impide la acción si existen registros relacionados
• SET NULL: Establece el valor a NULL en los registros relacionados
• NO ACTION: Similar a RESTRICT, pero se comprueba al final de la transacción

Añadir restricciones UNIQUE

Para garantizar que los valores en una columna o conjunto de columnas sean únicos:

ALTER TABLE usuarios
ADD CONSTRAINT uk_email
UNIQUE (email);

Para una restricción única compuesta:

ALTER TABLE empleados
ADD CONSTRAINT uk_departamento_cargo
UNIQUE (id_departamento, cargo);

Añadir restricciones CHECK

Las restricciones CHECK permiten validar los datos según condiciones personalizadas:

ALTER TABLE productos
ADD CONSTRAINT chk_precio_positivo
CHECK (precio > 0);

Podemos crear restricciones más complejas:

ALTER TABLE empleados
ADD CONSTRAINT chk_fecha_contratacion
CHECK (fecha_contratacion <= CURRENT_DATE AND fecha_contratacion > '2000-01-01');

Eliminar restricciones existentes

Para eliminar una restricción, necesitamos conocer su nombre. Si no lo conocemos, podemos consultarlo en las tablas del sistema o en herramientas de administración.

Eliminar una clave primaria

ALTER TABLE empleados
DROP PRIMARY KEY;

Eliminar una clave foránea

ALTER TABLE pedidos
DROP CONSTRAINT fk_cliente_pedido;

En MySQL, también podemos usar:

ALTER TABLE pedidos
DROP FOREIGN KEY fk_cliente_pedido;

Eliminar restricciones UNIQUE o CHECK

ALTER TABLE usuarios
DROP CONSTRAINT uk_email;

ALTER TABLE productos
DROP CONSTRAINT chk_precio_positivo;

Modificar restricciones existentes

Para modificar una restricción, generalmente necesitamos eliminarla y volver a crearla con la nueva definición:

-- Eliminar la restricción existente
ALTER TABLE pedidos
DROP CONSTRAINT fk_cliente_pedido;

-- Crear la restricción con la nueva definición
ALTER TABLE pedidos
ADD CONSTRAINT fk_cliente_pedido
FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente)
ON DELETE SET NULL
ON UPDATE CASCADE;

Habilitar o deshabilitar restricciones temporalmente

En algunos sistemas, como PostgreSQL, podemos deshabilitar temporalmente las restricciones para operaciones de carga masiva:

-- Deshabilitar una restricción
ALTER TABLE pedidos
ALTER CONSTRAINT fk_cliente_pedido DEFERRABLE INITIALLY DEFERRED;

-- Volver a habilitarla
ALTER TABLE pedidos
ALTER CONSTRAINT fk_cliente_pedido DEFERRABLE INITIALLY IMMEDIATE;

Restricciones con nombre vs. sin nombre

Es una buena práctica asignar nombres explícitos a las restricciones:

-- Con nombre explícito (recomendado)
ALTER TABLE productos
ADD CONSTRAINT uk_codigo_producto UNIQUE (codigo_producto);

-- Sin nombre explícito (no recomendado)
ALTER TABLE productos
ADD UNIQUE (codigo_producto);

Cuando no asignamos un nombre, el sistema genera uno automáticamente, lo que puede dificultar su gestión posterior.

Consultar las restricciones existentes

Para ver las restricciones definidas en nuestras tablas:

En MySQL:

-- Ver claves primarias y foráneas
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'nombre_base_datos'
AND TABLE_NAME = 'nombre_tabla';

-- Ver detalles de claves foráneas
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'nombre_base_datos'
AND TABLE_NAME = 'nombre_tabla';

En PostgreSQL:

-- Ver todas las restricciones
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'nombre_tabla'::regclass;

Ejemplos prácticos de gestión de restricciones

Escenario 1: Reforzar la integridad de datos en un sistema de ventas

Supongamos que tenemos una tabla de ventas y queremos asegurarnos de que todos los importes sean positivos y estén asociados a un cliente válido:

-- Añadir restricción CHECK para importes positivos
ALTER TABLE ventas
ADD CONSTRAINT chk_importe_positivo
CHECK (importe > 0);

-- Añadir clave foránea al cliente
ALTER TABLE ventas
ADD CONSTRAINT fk_cliente_venta
FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente)
ON DELETE RESTRICT;

-- Asegurar que la fecha de venta no sea futura
ALTER TABLE ventas
ADD CONSTRAINT chk_fecha_venta
CHECK (fecha_venta <= CURRENT_DATE);

Escenario 2: Actualizar un sistema de inventario

Imaginemos que necesitamos actualizar un sistema de inventario para garantizar que los productos tengan códigos únicos y existencias no negativas:

-- Añadir restricción UNIQUE al código de producto
ALTER TABLE productos
ADD CONSTRAINT uk_codigo_producto
UNIQUE (codigo_producto);

-- Añadir restricción CHECK para existencias no negativas
ALTER TABLE productos
ADD CONSTRAINT chk_stock_no_negativo
CHECK (stock >= 0);

-- Añadir restricción CHECK para precios válidos
ALTER TABLE productos
ADD CONSTRAINT chk_precio_valido
CHECK (precio_venta > precio_costo);

Consideraciones importantes

Al gestionar restricciones en tablas existentes, debemos tener en cuenta:

• Datos existentes: Antes de añadir una restricción, debemos asegurarnos de que los datos existentes la cumplen, o la operación fallará.

-- Verificar antes de añadir la restricción
SELECT COUNT(*) FROM productos WHERE precio <= 0;

-- Si no hay registros que incumplan, añadir la restricción
ALTER TABLE productos
ADD CONSTRAINT chk_precio_positivo
CHECK (precio > 0);

• Rendimiento: Las restricciones mejoran la integridad de los datos pero pueden afectar al rendimiento de las operaciones de inserción y actualización.

• Transacciones: Es recomendable realizar cambios en las restricciones dentro de transacciones, especialmente en entornos de producción:

BEGIN;
-- Verificar que los datos cumplen la nueva restricción
SELECT COUNT(*) FROM empleados WHERE salario < 0;
-- Añadir la restricción
ALTER TABLE empleados
ADD CONSTRAINT chk_salario_positivo
CHECK (salario >= 0);
COMMIT;

• Convenciones de nombres: Es útil seguir una convención para nombrar las restricciones:
• pk_ para claves primarias
• fk_ para claves foráneas
• uk_ para restricciones UNIQUE
• chk_ para restricciones CHECK

Restricciones a nivel de tabla vs. a nivel de columna

Algunas restricciones pueden definirse tanto a nivel de columna como de tabla:

-- A nivel de columna (durante la creación de la tabla)
CREATE TABLE productos (
    id_producto INT PRIMARY KEY,
    precio DECIMAL(10,2) CHECK (precio > 0)
);

-- A nivel de tabla (con ALTER TABLE)
ALTER TABLE productos
ADD CONSTRAINT chk_precio_positivo
CHECK (precio > 0);

Las restricciones a nivel de tabla son más flexibles, especialmente para condiciones que involucran múltiples columnas:

ALTER TABLE productos
ADD CONSTRAINT chk_precio_descuento
CHECK (precio_descuento < precio_normal);

Restricciones en cascada

Las restricciones en cascada son particularmente útiles para mantener la integridad referencial:

ALTER TABLE pedidos
ADD CONSTRAINT fk_cliente
FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente)
ON DELETE CASCADE;

Con esta configuración, si eliminamos un cliente, todos sus pedidos asociados se eliminarán automáticamente.