SQL ALTER TABLE para agregar columnas: guía completa

Agregar una columna a una tabla existente en SQL es una tarea común que tendrás que hacer conforme evoluciona el esquema de tu base de datos. La sentencia ALTER TABLE te permite modificar la estructura de una tabla existente sin perder datos. Esta guía explica cómo usar ALTER TABLE para agregar columnas con ejemplos prácticos.

Entendiendo ALTER TABLE

El comando ALTER TABLE sirve para agregar, eliminar o modificar columnas en una tabla existente. También se puede usar para cambiar las restricciones de la tabla. Este tutorial se enfoca específicamente en agregar columnas nuevas.

Al agregar una columna nueva, debes especificar el nombre de la columna, el tipo de dato y cualquier restricción o valor predeterminado adicional. La operación es atómica y no afecta los datos existentes.

Sintaxis básica para agregar una columna

Esta es la sintaxis básica para agregar una columna nueva a una tabla:

ALTER TABLE table_name
ADD column_name column_type [constraint];

• table_name: el nombre de la tabla a la que le vas a agregar una columna.
• column_name: el nombre de la nueva columna.
• column_type: el tipo de dato de la nueva columna (p. ej., VARCHAR, INT, DATE, etc.).
• constraint: opcional. Cualquier restricción adicional como NOT NULL, UNIQUE o valores DEFAULT.

Ejemplo: agregar una columna

Empecemos con una tabla employees sencilla:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50)
);

-- Current data in employees table:
INSERT INTO employees (id, name, position) VALUES (1, 'Alice', 'Engineer');
INSERT INTO employees (id, name, position) VALUES (2, 'Bob', 'Manager');

SELECT * FROM employees;

Resultado:

+----+-------+----------+
| id | name  | position |
+----+-------+----------+
|  1 | Alice | Engineer |
|  2 | Bob   | Manager  |
+----+-------+----------+

Agregar una nueva columna para el salario

Agreguemos una nueva columna llamada salary con el tipo de dato DECIMAL(10, 2), que puede guardar números de hasta 10 dígitos, incluidos 2 decimales:

ALTER TABLE employees
ADD salary DECIMAL(10, 2);

Este comando modifica la tabla employees para incluir la nueva columna salary. Las filas existentes tendrán valores NULL en esta columna hasta que las actualices.

Verificar la estructura de la tabla

Verifica que la columna se haya agregado con DESCRIBE o SHOW COLUMNS (según tu sistema de base de datos):

DESCRIBE employees;

Resultado:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(100)| YES  |     | NULL    |       |
| position | varchar(50) | YES  |     | NULL    |       |
| salary   | decimal(10,2)| YES |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

Agregar una columna con un valor predeterminado

También puedes agregar una columna con un valor predeterminado. Así se agrega una columna hire_date que toma por defecto la fecha actual:

ALTER TABLE employees
ADD hire_date DATE DEFAULT CURRENT_DATE;

Las nuevas filas que insertes en la tabla employees tendrán automáticamente hire_date con la fecha actual cuando no se proporcione un valor.

Agregar una columna con la restricción NOT NULL

Agregar una columna con la restricción NOT NULL a una tabla existente requiere atención especial. No puedes simplemente agregar una columna NOT NULL a una tabla que ya tiene datos sin darle un valor predeterminado, porque eso violaría la restricción en las filas existentes.

El problema de NOT NULL con datos existentes

Este comando fallará si la tabla ya tiene filas:

-- This will fail on tables with existing data
ALTER TABLE employees
ADD phone_number VARCHAR(20) NOT NULL;

El error ocurre porque las filas existentes tendrían valores NULL en la nueva columna, lo que viola la restricción NOT NULL.

Solución 1: agregar la columna con un valor predeterminado

La solución más directa es proporcionar un valor predeterminado:

ALTER TABLE employees
ADD phone_number VARCHAR(20) NOT NULL DEFAULT 'Unknown';

Esto funciona porque:

1. La columna se agrega con el valor predeterminado en todas las filas existentes
2. La restricción NOT NULL se cumple de inmediato
3. Las inserciones futuras deben dar un valor o usar el predeterminado

Solución 2: proceso en varios pasos

Para tener más control sobre los valores, usa un enfoque en varios pasos:

-- Step 1: Add the column as nullable
ALTER TABLE employees
ADD phone_number VARCHAR(20);

-- Step 2: Update existing rows with appropriate values
UPDATE employees 
SET phone_number = 'Unknown' 
WHERE phone_number IS NULL;

-- Step 3: Add the NOT NULL constraint
ALTER TABLE employees
ALTER COLUMN phone_number SET NOT NULL;

Nota: la sintaxis del paso 3 varía según el sistema de base de datos:

• PostgreSQL: ALTER COLUMN phone_number SET NOT NULL
• MySQL: MODIFY COLUMN phone_number VARCHAR(20) NOT NULL
• SQL Server: ALTER COLUMN phone_number VARCHAR(20) NOT NULL

Solución 3: valores predeterminados condicionales

Puedes asignar distintos valores predeterminados según los datos existentes:

-- Add column with temporary default
ALTER TABLE employees
ADD department VARCHAR(50) NOT NULL DEFAULT 'TBD';

-- Update with conditional logic based on position
UPDATE employees 
SET department = CASE 
    WHEN position = 'Engineer' THEN 'Engineering'
    WHEN position = 'Manager' THEN 'Management'
    ELSE 'General'
END;

-- Optionally remove the default if you don't want it for future inserts
ALTER TABLE employees
ALTER COLUMN department DROP DEFAULT;

Buenas prácticas para columnas NOT NULL

1. Planea con anticipación: considera las restricciones NOT NULL desde el diseño inicial de la tabla
2. Usa valores predeterminados con sentido: elige valores que tengan sentido para tu lógica de negocio
3. Prueba primero: siempre prueba las operaciones ALTER TABLE en una copia de tus datos
4. Documenta los cambios: lleva un registro de las modificaciones al esquema para comunicarlas a tu equipo
5. Considera la migración de datos: planea cómo llenar la nueva columna con datos significativos

Verificar las restricciones NOT NULL

Después de agregar una columna NOT NULL, verifica que la restricción esté activa:

-- Check table structure
DESCRIBE employees;

-- Verify constraint by attempting to insert NULL (should fail)
INSERT INTO employees (id, name, position, phone_number) 
VALUES (3, 'Carol', 'Analyst', NULL);
-- Error: Column 'phone_number' cannot be null

Operaciones relacionadas

Ahora que sabes cómo agregar columnas, quizá también quieras conocer otras operaciones de ALTER TABLE:

• Cómo renombrar columnas con ALTER TABLE
• PostgreSQL: cambiar el tipo de una columna
• Cómo agregar columnas en Teradata

Estas operaciones son esenciales para mantener y evolucionar el esquema de tu base de datos conforme cambian los requisitos de tu aplicación.

Conclusión

Usar ALTER TABLE para agregar columnas te permite modificar el esquema sin perder datos. Esta flexibilidad te ayuda a adaptar tu base de datos a los requisitos cambiantes de tu aplicación. Respalda siempre tus datos antes de cambiar el esquema y verifica la compatibilidad con la lógica existente de tu aplicación.

Para operaciones de bases de datos más avanzadas y para administrar tus bases de datos SQL de forma eficaz, prueba Beekeeper Studio, que ofrece una interfaz intuitiva para gestionar esquemas y hacer operaciones SQL.