Crear Foreign Key (Integridad Referencial) con MySQL

Hace unos días que estoy bastante liado buscando información sobre MySQL, claves foráneas (o en inglés, foreign Key) y la Inegridad Referencial…
No es que sea un experto con el MySQL pero intentaré explicar todo lo que he aprendido:

Definición de Integridad Referencial

Podemos entender como Integridad Refencial a la propiedad, aplicada en las Bases de Datos, que nos garantiza que una Entidad (fila o registro) se relaciona con otra entidad que EXISTE en la Base de Datos. Nos aseguramos en todo momento que la información no esté repetida innecesariamente, que exita tal información, relaciones mal hechas…

Definición de Claves Foráneas – Foreign Key

Una clave foránea, o foreign key, no es más que un campo (Entidad) de un tabla que hace referencia al identificador de otra Tabla.

MySQL – Integridad Referencial y las Foreign Key

En las primeras versiones de MySQL (hasta aproximadamente la versión 3.23) la Integridad Referencial no estaba disponible, y a la hora de crear la Base de Datos, esta Integridad debía ser controlada por parte de la aplicación.

A la hora de crear una Base de Datos, el motor por defecto de MySQL es el MyISAM pero a partir de la versión 3.23 (según la Wikipedia en la versión 4 de MySQL) se incorpora el motor InnoDB, que nos permitirá tener Bases de Datos con Integridad Referencial.

Tengo que decir que no sé muy bien como interpreta el motor MyISAM la sintaxis de Foreign Key (por lo que he podido entender… lo interpreta como si fueran CREATE TABLE), pero si puedo asegurar, es que la Integridad Referencial no se aplica nativamente para este motor.

¿Qué es el Motor MyISAM?

Es el motor de almacenamiento por defecto del MySQL. Como propiedades relevantes tenemos que destacar su gran rapidez con las consultas, “ya que no tiene que hacer comprobaciones de Integridad Referencial”. Está realmente optimizado para aplicaciones, sistemas, programas… en las que no hay un número elevado de inserciones.

¿Qué es el Motor InnoDB?

El innoDB es una tecnología de almacenamiento de datos, se caracteriza por soportar transacciones de tipo ACID e incluir la Integridad Referencial. Sé que me repito mucho, pero la llegada de este motor de almacenamiento en MySQL, nos permite definir Claves Foráneas (Foreign Key), y gracias a ello, definir reglas o restricciones que nos aseguren la Integridad Referencial de los registros de la Tabla/Base de Datos.

¿Cómo especificamos el tipo de motor a utilizar en MySQL?

A la hora de crear una tabla hay que indicarle la opción ENGINE. Con esta opción le indicaremos el motor que tiene esta tabla en concreto: MyISAM, InnoDB, BDB, … veamos un ejemplo sencillo:

CREATE TABLE `PRUEBA` (
    ID int(10) unsigned NOT NULL auto_increment COMMENT 'IDENTIFICADOR TABLA',
    NAME varchar(40) NOT NULL COMMENT 'NOMBRE PELICULA',
    DESCRIPTION varchar(255) default NULL COMMENT 'DESCRIPCION PELICULA',
    CREATIONDATE timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'FECHA DADA DE ALTA LA PELICULA',
    PRIMARY KEY  (ID),
) ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

InnoDB vs MyISAM

Antes de llegar a la especificación de cómo crear la Integridad Referencial por código SQL, voy a enumerar primero las diferencias que podemos encontrar en los tipos de motor de almacenamiento InnoDB y MyISAM. ¿Por qué? Porque según la función e implicación que tenga que tener nuestra Base de Datos nos podemos decantar por uno o por otro motor.

InnoDB:

Permite el uso de Transacciones: no es más que todo un conjunto de órdenes que se ejecutan como si fueran una unidad de trabajo, dicho de otro modo, que este bloque de órdenes (transacciones) no finalizan en un estado intermedio. Si alguna orden se ha ejecutado y no finaliza la ejecución de todo el bloque de órdenes correctamente, el SGBD (Sitema Gestor de Base de Datos) se encargará (como de un rollback se tratase…) de dejar la Base de Datos en el estado inicial.
Las Transacciones son de Tipo ACID: acrónimo de Atomicity, Consistency, Isolation and Durability (o dicho en español: Atomicidad, Consistencia, Aislamiento y Durabilidad).
Si nuestra Aplicación utiliza mucho el uso de Inserts y Updates notaremos una gran mejoría respecto al motor MyISAM.
La caché de las lecturas y escrituras de los registros se realiza mediante una combinación entre Cachés de registro y de índice. Con lo consecuente, InnoDB no envia los cambios de la tabla al Sistema Operativo (S.O., opción más lenta…) para que los escriba, por lo tanto, es mucho más rápido que MyISAM en según que escenarios.
ACTUALIZACIÓN gracias a ikhuerta. Bloqueo a nivel de registro, es decir, por cada petición (Selects, Inserts, updates…) que se haga a la tabla se bloquea a nivel de REGISTRO, en cambio MyISAM bloquea toda la tabla entera hasta finalizar su ejecución, pudiendo así crear una cola de peticiones. Del otro modo, al bloquear solamente el registro necesario, el resto de registros quedan libres para su utilización.

MyISAM:

Mayor velocidad en general a la hora de recuperar datos.
Es recomendable para aquellas Base de Datos donde predominan los Selects y no los Inserts o Updates.
Con la ausencia de Automacidad (no hay comprobaciones de integridad referencial, no hay bloqueos de tablas,…) obtenemos nuevamente una mayor velocidad.
Cuenta con una algoritmo de Compresión de Datos muy eficiente, de modo que el espacio en disco, Ram o caché, es realmente inferior al del motor InnoDB. Aunque he leído que el motor InnoDB ha mejorado este aspecto y ya está en práctica en el MySQL 5, reduciendo así un 20% del espacio.
No soporta Transacciones, ¿ventaja o desventaja? Según lo que pone la wiki puede llegar a ser una ventaja, por el simple echo que los accesos a disco que tiene el motor InnoDB es de almenos una por cada transacción. Esto supone una limitación de transacciones para los discos duros, de aproximadamente unas 200 por segundo.

Hasta aquí creo que ya es suficiente de teoría, es una buena recopilación de información, ahora pasemos a lo importante: “LOS EJEMLOS”.

Código SQL, para montar la Integridad Referencial

Veamos un ejemplo del Esquema en MER(Modelo Entidad Relación) y MR (Modelo Relacional, después de normalizar el MER), adjunto imagen:

Ahora veamos el código SQL para ver como se crearía esta Base de Datos y todas sus relaciones:


SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Base de datos: `testpostFKs`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `T_COCHE`
--

CREATE TABLE IF NOT EXISTS `T_COCHE` (
    `MARCA` varchar(20) NOT NULL,
    `ORIGEN` varchar(25) NOT NULL,
    `FECHAINICIO` datetime NOT NULL,
    PRIMARY KEY  (`MARCA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcar la base de datos para la tabla `T_COCHE`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `T_COCHE_SERVTEC`
--

CREATE TABLE IF NOT EXISTS `T_COCHE_SERVTEC` (
    `IDMODELO` int(10) unsigned NOT NULL,
    `IDSERVICIO` int(10) unsigned NOT NULL,
    PRIMARY KEY  (`IDMODELO`,`IDSERVICIO`),
    KEY `IDMODELO` (`IDMODELO`),
    KEY `IDSERVICIO` (`IDSERVICIO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcar la base de datos para la tabla `T_COCHE_SERVTEC`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `T_MODELO`
--

CREATE TABLE IF NOT EXISTS `T_MODELO` (
    `IDMODELO` int(10) unsigned NOT NULL auto_increment,
    `NAME` varchar(25) NOT NULL,
    `DESCRIPTION` varchar(255) default NULL,
    `FECHAEXPEDICION` datetime default NULL,
    `DISENYADOR` varchar(25) NOT NULL,
    `MARCA` varchar(20) NOT NULL,
    PRIMARY KEY  (`IDMODELO`),
    KEY `MARCA` (`MARCA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `T_MODELO`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `T_SERVICIOTECNICO`
--

CREATE TABLE IF NOT EXISTS `T_SERVICIOTECNICO` (
    `IDSERVICIO` int(10) unsigned NOT NULL auto_increment,
    `NOMBRE` varchar(40) NOT NULL,
    `PAIS` varchar(25) NOT NULL,
    `PROVINCIA` varchar(25) NOT NULL,
    `LOCALIDAD` varchar(25) NOT NULL,
    `DIRECCION` varchar(255) NOT NULL,
    `TELEFONO` int(10) unsigned default NULL,
    `FAX` int(10) unsigned default NULL,
    PRIMARY KEY  (`IDSERVICIO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `T_SERVICIOTECNICO`
--

--
-- Filtros para las tablas descargadas (dump)
--

--
-- Filtros para la tabla `T_COCHE_SERVTEC`
--
ALTER TABLE `T_COCHE_SERVTEC`
ADD CONSTRAINT `T_COCHE_SERVTEC_ibfk_2` FOREIGN KEY (`IDSERVICIO`) REFERENCES `T_SERVICIOTECNICO` (`IDSERVICIO`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `T_COCHE_SERVTEC_ibfk_1` FOREIGN KEY (`IDMODELO`) REFERENCES `T_MODELO` (`IDMODELO`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `T_MODELO`
--
ALTER TABLE `T_MODELO`
ADD CONSTRAINT `T_MODELO_ibfk_1` FOREIGN KEY (`MARCA`) REFERENCES `T_COCHE` (`MARCA`) ON DELETE CASCADE ON UPDATE CASCADE;

Puntos a tener en CUENTA:

El motor de almacenamiento es INNODB (Engine=INNODB)
El campo o entidad que representa la Foreign Key, para indicarle realmente que es una Foreign Key, primero hay que especificarle que es un INDEX, lo hacemos así: KEY `IDSERVICIO` (`IDSERVICIO`)
Hay que indicarle a la Foreign Key la función a ejecutar por parte de la Base de Datos cuando se elimine una Clave Primaria (PK), por ejemplo, si eliminamos una tupla de T_COCHE ¿cómo tiene que proceder la base de datos con las otras tablas que estén referenciadas? Para eso sirve el: ON DELETE CASCADE ON UPDATE CASCADE

Hasta aquí este post, el siguiente será: Montar la Integridad Referencial con PHPMyAdmin.

Mis notas favoritas sobre informática, siempre a mano..

2009-08-30