ALTER TABLE: Modificar la estructura de tablas y columnas en MySQL

Nuevo post para repasar la sentencia ALTER TABLE de MySQL, su meta es la de modificar la estructura de las tablas y sus columnas de una base de datos.

ALTER TABLE cambia la estructura de una tabla. Por ejemplo, agrega o elimina columnas, crea o elimina índices, modificar el tipo de columnas existentes o renombrar columnas o la propia tabla.

También modifica las características tales como el tipo de almacenamiento utilizado para las tablas. Otras sentencias para trabajar con tablas son:

• CREATE TABLE
• DROP TABLE
• TRUNCATE TABLE
• RENAME TABLE
• MySQL INSERT
• MySQL SELECT

Sintaxis de ALTER TABLE en MySQL

Veamos la sintaxis de esta sentencia extraída de su página web oficial:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE nombre_tabla
[alter_specification [, alter_specification] ...]

El código supererior muestra la estructura básica de la sentencia ALTER TABLE, veamos ahora la sintaxis de las diferentes opciones para modificar una tabla en MySQL:

# alter_specification:
# table_options
| ADD [COLUMN] col_name column_definition
      [FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
      [index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
      UNIQUE [INDEX|KEY] [index_name]
      [index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name]
      (index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name]
      (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
      FOREIGN KEY [index_name] (index_col_name,...)
      reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
      [FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
      [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO|AS] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

Ejemplos prácticos de ALTER TABLE en MySQL

Comenzamos con un ejemplo para renombrar y/o cambiar el nombre la tabla:

ALTER TABLE nombre_tabla RENAME nombre_nuevo_tabla;

Cambiar el juego de caracteres de la tabla:

ALTER TABLE nombre_tabla CHARACTER SET latin1;

Cambiar el engine (motor) de almacenamiento:

ALTER TABLE nombre_tabla ENGINE = InnoDB;

Eliminar una columna de la tabla:

ALTER TABLE nombre_tabla DROP COLUMN nombre_columna;

Eliminar varias columnas de la tabla:

ALTER TABLE nombre_tabla DROP COLUMN nombre_columna, DROP COLUMN nombre_columna2;

Eliminar una clave primaria y clave externa (FOREING KEY y PRIMARY KEY):

#Eliminar clave primaria
ALTER TABLE nombre_tabla DROP PRIMARY KEY;
#Eliminar clave externa
ALTER TABLE nombre_tabla DROP FOREIGN KEY nombre_columna;

En el siguiente código vamos a insertar una nueva columna al final de la tabla:

ALTER TABLE nombre_tabla ADD fecha_nacimiento date;

Añadir una nueva columna después de otra:

ALTER TABLE nombre_tabla ADD nombre_columna VARCHAR(5) AFTER nombre_columna_anterior;

Añadir una nueva columna en la primera posición de la tabla:

ALTER TABLE nombre_tabla ADD nombre_columna VARCHAR(5) INT FIRST;

Añadir un indice a una columna y eliminar un íncide:

ALTER TABLE nombre_tabla ADD INDEX (nombre_columna);
ALTER TABLE nombre_tabla DROP INDEX nombre_indice;

En estos 3 post tenéis más info sobre la creación, eliminación y tipos de índices:

• MySQL CREATE INDEX: Crear o añadir índices en tablas

• MySQL DROP INDEX: Eliminar índices de las tablas

Asignar como clave primaria a una columna:

ALTER TABLE nombre_Tabla ADD PRIMARY KEY(nombre_columna);

Modificar el valor de la columna con propiedad autoincrement para que comiencen por el valor 15000:

ALTER TABLE nombre_tabla AUTO_INCREMENT=15000;

Cambiar el nombre o renombrar una columna:

ALTER TABLE nombre_tabla CHANGE nombre_viejo_columna nombre_nuevo_columna;

Cambiar el nombre y tipo de dato de una columna:

ALTER TABLE nombre_tabla CHANGE nombre_viejo_columna nombre_nuevo_columna VARCHAR(20);

Solamente cambiar el tipo de dato de una columna:

ALTER TABLE nombre_tabla MODIFY nombre_columna DATE NOT NULL;