Agregar una columna a tabla – MySQL

La crónica de esta ocasión tiene un título tan genérico, que parece una chapuza de bajo presupuesto, sin embargo, a pesar de ser una tarea tan común para quien ya tiene algunas horas de vuelo haciendo interactuar su programa con una base de datos, viene acompañada de otras tareas que a veces pasan desapercibidas.

Modificar la estructura de una tabla, agregando una o más columnas que son necesarias para satisfacer tal o cual necesidad require de la siguiente instrucción:

ALTER TABLE table ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

Donde:

column_name se sustituye por el nombre de columna que se desea.

column_definition por el tipo de dato en turno y las condiciones que se esperan de la misma: NOT NULL, DEFAULT, UNSIGNED, Etc.

FIRST|AFTER existing_column, que son opcionales y a elegir uno o el otro, nos darán los resultados: FIRST causará que la columna agregada sea la primera en la estrucura de la tabla; AFTER existing_column agregará la columna en turno después de la columna dada después de AFTER, misma que debe existir.

Por ejemplo:

ALTER TABLE mitabla ADD id_modulo INTEGER NOT NULL;

ALTER TABLE mitabla ADD porcentaje SMALLINT DEFAULT 0;

ALTER TABLE mitabla ADD cve_unidad CHAR(30) NOT NULL FIRST;

¿Y para qué aprender la instrucción si se puede hacer desde un diseñador como MySQL Workbench?

Por la misma razón que se aprenden comandos. No siempre se tiene a la mano un editor gráfico, o peor aún, autorización para utilizarlo, y en segunda instancia, porque el editor gráfico termina construyendo esta instrucción en segundo plano, de tal suerte que siempre es importante saber qué es lo que ocurre.

Ahora bien, mi intención al realizar estas crónicas no es otra que pasar del ejercicio de libro a la acción.

Reitero: modificar la estructura de una tabla es sólo parte del trabajo. Salvo que se esté trabajando aún en el diseño de la tabla o que el trabajo se encuentre en una etapa inicial, las tareas de ajuste se limitan al elemento en turno, es decir, casi con total libertad se puede hacer la modificación sin riesgo a impactar otros componentes.

Sin embargo, cuando estas condiciones no existen más, ya sea porque la tabla está poblada con datos de prueba o datos productivos (lo que complica más la operación), que la tabla contenga datos de un catálogo o sea objeto de restricciones en los datos (como índices únicos o referencias con revisión de integridad a otras tablas), modificar la estructura de este elemento implica conocer el alcance del cambio, los efectos que este cambio provocan en el resto de componentes del sistema (a nivel base de datos y aplicativo) y actuar en consecuencia. El resto de la crónica, va orientada precisamente a abordar esta situación.

Usemos como ejemplo un caso real, y describir cómo se puede resolver.

Tengo una tabla que define secciones de un contenido, seccion. Las columnas importantes en esta tabla son, por supuesto, la llave primaria id_seccion y el nombre de la sección. El cambio que deseo hacer es agregar una columna, que será llave foránea y que campiranamente llamaré id_modulo, que vendrá de un catálogo nuevo, c_modulo, que me permitirá identificar secciones de contenido asociadas a n módulos.

Entonces, las actividades son:

• Agregar la columna id_modulo
• Hacer la columna id_modulo llave foránea con integridad referencial a la columna id_modulo de la tabla c_modulo, donde esta es llave primaria.
• Asignar los valores a la columna id_modulo, conforme la definición del catálogo y que hagan sentido de acuerdo a la información que ya existe en la tabla, es decir, asignar correctamente las secciones al módulo que corresponda.

¿Qué alternativas hay?

Alterar la tabla

Esta evidentemente es la solución por omisión, derivada de la tarea principal.

Comenzamos deshabilitando la revisión referencial. ¿Por qué? Porque al agregar la columna y su restricción de integridad, salvo que el valor por omisión exista en el catálogo (que no hace sentido en mi caso asignarlo) provocaría un desorden de información y posibles errores.

SET foreign_key_checks = 0;

Continuamos con la alteración de la tabla

ALTER TABLE seccion ADD id_modulo INTEGER NOT NULL;

Agregamos la integridad referencial

ALTER TABLE seccion
ADD CONSTRAINT fk_seccion_c_modulo1
FOREIGN KEY (id_modulo) REFERENCES c_modulo(id_modulo);

Asignamos el valor correspondiente a la columna id_modulo con UPDATEs. Por ejemplo:

update c_seccion set id_modulo = 1 where id_seccion <12;

Reactivamos la revisión de integridad:

SET foreign_key_checks = 1;

Esta fue la estrategia que seguí, ya que la tabla se usa poco y no tiene tantos datos. Aun no es una tabla productiva. Estas condiciones me permitían ejecutar el ALTER sin afectar la operación de la base de datos, y ajustar los datos que aloja fue más sencillo porque al ser un conjunto manejable, esto quedó resuelto con 10 UPDATES.

En un escenario de más datos, o si la tabla es de un ambiente productivo, no podríamos simplemente ejecutar el ALTER así nada más, sobre todo, si la tabla es de mucha lectura, porque la instrucción ALTER restringirá el acceso a la misma durante la el proceso de modificación; esto puede provocar lentitud en la lectura de los datos e impedimento de escritura en la misma.

Volver a crear la tabla

Si cuentas con los privilegios suficientes y la tabla tiene muchos datos, probablemente es más fácil y rápido, volver a crear la tabla. ¿Por qué? Puedes dejar la tabla actual funcionando, y crear una tabla copia que incluya la o las columnas nuevas con sus condiciones. Una vez que la tengas lista, puedes hacer una instruccion INSERT INTO… SELECT FROM y realizar un poblado masivo. A veces esta operación es más rápida y menos intrusiva que un ALTER. Dependerá de ti y el administrador de base de datos (quizás este rol lo tengas tú) cuál es la más adecuada.

¿Cómo habría sido este proceso?

Creamos la tabla nueva incluyendo la columna faltante y sus restricciones:

Para llevar los datos podemos hacer el INSERT INTO SELECT FROM… y dar un valor existente en la tabla catálogo de módulos. Fíjate que en este proceso no hemos deshabilitado las revisiones de integridad en ningún momento.

INSERT INTO seccion_nueva (id_seccion,..., id_modulo) SELECT id_seccion, ..., 1 FROM seccion;

Este insert causará que todos los datos de la tabla seccion sean llevados a la tabla c_seccion, poniendo como valor 1 en la columna id_modulo. Posteriormente podríamos hacer updates para ajustar nuevamente la asignación de la referencia al catálogo de módulos según corresponda.

Podemos también hacer un varios insert por subconjuntos, por ejemplo:

INSERT INTO seccion_nueva (id_seccion,..., id_modulo) SELECT id_seccion, ..., 1 FROM seccion WHERE id_seccion < 12;

INSERT INTO seccion_nueva (id_seccion,..., id_modulo) SELECT id_seccion, ..., 30 FROM seccion WHERE id_seccion BETWEEN 30 and 68;

Una vez que tengas los datos como deseas, puedes renombrar las tablas. Esto te permitrá tener un respaldo de la tabla original de la cual partir nuevamente o con la cual revisar si lo que hiciste es correcto. Considera siempre tener un respaldo a la mano.

ALTER TABLE seccion RENAME TO seccion_original;
ALTER TABLE seccion_nueva RENAME TO seccion;

Recapitulando

En el ejercicio de tus actividades de desarrollo de software, entre otras tantas cosas, debes considerar:

Lo que sea más rápido, no para ti, sino que minimice el tiempo de espera de los demás.

Lo que tenga menor costo, no solo en dinero, considera también la utilización de espacio en disco, uso de memoria, procesamiento, tiempo de ejecución, etc.

Lo que tenga menor riesgo, que minimice la probabilidad de error y te permita tener una forma de regresar a como estaba y comenzar de nuevo.

En proyectos pequeños o donde uno protagoniza las tareas, estas decisiones quedan en manos de uno mismo. Sin embargo, siendo parte de un equipo en un proyecto, obviamente no es así, prepárate a escuchar otros puntos de vista, exponer tus ideas y debatirlas. Al final, el objetivo es siempre encontrar la mejor opción, la que beneficie a la mayoría y reduzca los efectos negativos.