15.6. Crear tablas InnoDB

InnoDB rechaza cualquier operación INSERT o UPDATE que intente crear un valor de clave foránea en una tabla hija sin un valor de clave candidata coincidente en la tabla padre. La acción que InnoDB lleva a cabo para cualquier operación UPDATE o DELETE que intente actualizar o borrar un valor de clave candidata en la tabla padre que tenga filas coincidentes en la tabla hija depende de la accion referencial especificada utilizando las subcláusulas ON UPDATE y ON DETETE en la cláusula FOREIGN KEY. Cuando el usuario intenta borrar o actualizar una fila de una tabla padre, InnoDB soporta cinco acciones respecto a la acción a tomar:

InnoDB soporta las mismas opciones cuando se actualiza la clave candidata en la tabla padre. Con CASCADE, las columnas de clave foránea en la tabla hija son establecidas a los nuevos valores de la clave candidata en la tabla padre. Del mismo modo, las actualizaciones se producen en cascada si las columnas actualizadas en la tabla hija hacen referencia a claves foráneas en otra tabla.

Nótese que InnoDB soporta referencias de clave foránea dentro de una tabla, y, en estos casos, la tabla hija realmente significa registros dependientes dentro de la tabla.

InnoDB necesita que haya índices sobre las claves foráneas y claves referenciadas, así las verificaciones de claves foráneas pueden ser veloces y no necesitan recorrer la tabla. En MySQL 5.0, el índice en la clave foránea se crea automáticamente. Esto contrasta con versiones más antiguas (anteriores a 4.1.8), donde los índices debían crearse explícitamente o fallaba la creación de restricciones de claves foráneas.

Las columnas involucradas en la clave foránea y en la clave referenciada deben tener similares tipos de datos internos dentro de InnoDB, de modo que puedan compararse sin una conversión de tipo. La longitud y la condición de con o sin signo de los tipos enteros deben ser iguales. La longitud de los tipos cadena no necesita ser la misma. Si se especifica una acción SET NULL, hay que asegurarse de que las columnas en la tabla hija no se han declarado como NOT NULL.

Si MySQL informa que ocurrió un error número 1005 en una sentencia CREATE TABLE y la cadena con el mensaje de error se refiere al errno (número de error) 150, significa que la creación de una tabla falló debido a una restricción de clave foránea formulada incorrectamente. Del mismo modo, si un ALTER TABLE falla y hace referencia al número de error 150, significa que se ha formulado incorrectamente una restricción de clave extranjera cuando se alteró la tabla. En MySQL 5.0, puede emplearse SHOW INNODB STATUS para mostrar una explicación detallada del último error de clave foránea sufrido por InnoDB en el servidor.

Nota: InnoDB no verifica las restricciones de claves foráneas en las claves foráneas o valores de claves referenciados que contengan una columna NULL.

Una desviación del estándar SQL: Si en la tabla padre hay varios registros que contengan el mismo valor de clave referenciada, entonces InnoDB se comporta en las verificaciones de claves extranjeras como si los demás registros con el mismo valor de clave no existiesen. Por ejemplo, si se ha definido una restricción del tipo RESTRICT, y hay un registro hijo con varias filas padre, InnoDB no permite la eliminación de ninguna de éstas.

InnoDB lleva a cabo las operaciones en cascada a través de un algoritmo de tipo depth-first, basado en los registros de los indices correspondientes a las restricciones de claves foráneas.

Una desviación del estándar SQL: Si ON UPDATE CASCADE u ON UPDATE SET NULL vuelven a modificar la misma tabla que se está actualizando en cascada, el comportamiento es como en RESTRICT. Esto significa que en una tabla no se pueden ejecutar operaciones ON UPDATE CASCADE u ON UPDATE SET NULL que hagan referencia a ella misma. De ese modo se previenen bucles infinitos resultantes de la actualización en cascada. En cambio, una operación ON DELETE SET NULL, puede hacer referencia a la misma tabla donde se encuentra, al igual que ON DELETE CASCADE. En MySQL 5.0, las operaciones en cascada no pueden anidarse en más de 15 niveles de profundidad.

Una desviación del estándar SQL: Como sucede en MySQL en general, en una sentencia SQL que realice inserciones, eliminaciones o actualizaciones en varias filas, InnoDB verifica las restricciones UNIQUE y FOREIGN KEY fila a fila. De acuerdo con el estándar SQL, el comportamiento predeterminado debería ser que las restricciones se verifiquen luego de que la sentencia SQL ha sido procesada por completo.

Note: Actualmente, los disparadores no son activados por acciones de claves foráneas en cascada.

Un ejemplo sencillo que relaciona tablas padre e hijo a través de una clave foránea de una sola columna:

CREATE TABLE parent(
  id INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child(
  id INT, 
  parent_id INT,
  INDEX par_ind (parent_id),
  FOREIGN KEY (parent_id) 
    REFERENCES parent(id) 
    ON DELETE CASCADE
) ENGINE=INNODB;

Aquí, un ejemplo más complejo, en el cual una tabla product_order tiene claves foráneas hacia otras dos tablas. Una de las claves foráneas hace referencia a un índice de dos columnas en la tabla product. La otra hace referencia a un índice de una sola columna en la tabla customer:

CREATE TABLE product (
  category INT NOT NULL, 
  id INT NOT NULL,
  price DECIMAL,
  PRIMARY KEY(category, id)
) ENGINE=INNODB;

CREATE TABLE customer (
  id INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE product_order (
  no INT NOT NULL AUTO_INCREMENT,
  product_category INT NOT NULL,
  product_id INT NOT NULL,
  customer_id INT NOT NULL,
  PRIMARY KEY(no),
  INDEX (product_category, product_id),
  FOREIGN KEY (product_category, product_id)
    REFERENCES product(category, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX (customer_id),
  FOREIGN KEY (customer_id)
    REFERENCES customer(id)
) ENGINE=INNODB;

InnoDB permite agregar una nueva restricción de clave foránea a una tabla empleando ALTER TABLE:

ALTER TABLE yourtablename
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Debe recordarse crear en primer lugar los índices necesarios.. También se puede agregar una clave foránea autoreferente a una tabla empleando ALTER TABLE.

InnoDB también soporta el uso de ALTER TABLE para borrar claves foráneas:

ALTER TABLE nombre_tabla DROP FOREIGN KEY símbolo_clave_foránea;

Si la cláusula FOREIGN KEY incluye un nombre de CONSTRAINT cuando se crea la clave foránea, se puede utilizar ese nombre para eliminarla. En otro caso, el valor símbolo_clave_foránea es generado internamente por InnoDB cuando se crea la clave foránea. Para saber cuál es este símbolo cuando se desee eliminar una clave foránea, se emplea la sentencia SHOW CREATE TABLE. Un ejemplo:

mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
       Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
  `A` int(11) NOT NULL auto_increment,
  `D` int(11) NOT NULL default '0',
  `B` varchar(200) NOT NULL default '',
  `C` varchar(175) default NULL,
  PRIMARY KEY  (`A`,`D`,`B`),
  KEY `B` (`B`,`C`),
  KEY `C` (`C`),
  CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

El procesador de sentencias (parser) de InnoDB permite emplear acentos graves (ASCII 96) para encerrar los nombres de tablas y columnas en una clásusula FOREIGN KEY ... REFERENCES .... El parser de InnoDB también toma en cuenta lo establecido en la variable de sistema lower_case_table_names.

InnoDB devuelve las definiciones de claves foráneas de una tabla como parte de la salida de la sentencia SHOW CREATE TABLE:

SHOW CREATE TABLE tbl_name;

A partir de esta versión, mysqldump también produce definiciones correctas de las tablas en el fichero generado, sin omitir las claves foráneas.

Se pueden mostrar las restricciones de claves foráneas de una tabla de este modo:

SHOW TABLE STATUS FROM nombre_bd LIKE 'nombre_tabla';

Las restricciones de claves foráneas se listan en la columna Comment de la salida producida.

Al llevar a cabo verificaciones de claves foráneas, InnoDB establece bloqueos compartidos a nivel de fila en los registros de tablas hijas o padres en los cuales deba fijarse. InnoDB verifica las restricciones de claves foráneas inmediatamente, la verificación no es demorada hasta la confirmación de la transacción.

Para facilitar la recarga de ficheros de volcado de tablas que tengan relaciones de claves foráneas, mysqldump incluye automáticamente una sentencia en la salida del comando para establecer FOREIGN_KEY_CHECKS a 0. Esto evita problemas con tablas que tengan que ser creadas en un orden particular cuando se recarga el fichero de volcado. También es posible establecer el valor de esta variable manualmente:

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

Esto permite importar las tablas en cualquier orden si el fichero de volcado contiene tablas que no están ordenadas según lo requieran sus claves foráneas. También acelera la operación de importación. Establecer FOREIGN_KEY_CHECKS a 0 también puede ser útil para ignorar restricciones de claves foráneas durante operaciones LOAD DATA y ALTER TABLE.

InnoDB no permite eliminar una tabla que está referenciada por una restricción FOREIGN KEY, a menos que se ejecute SET FOREIGN_KEY_CHECKS=0. Cuando se elimina una tabla, las restricciones que fueron definidas en su sentencia de creación también son eliminadas.

Si se recrea una tabla que fue eliminada, debe ser definida de acuerdo a las restricciones de claves foráneas que están haciendo referencia a ella. Debe tener los tipos y nombres correctos de columnas, y debe tener índices sobre las tablas referenciadas, como se estableció anteriormente. Si estas condiciones no se cumplen, MySQL devuelve un error número 1005 y menciona el error número 150 en el mensaje de error.