atom feed1 message in net.php.lists.php-esRV: [documentación] Llaves foráneas e...
FromSent OnAttachments
Jaime ChavarriagaSep 27, 2003 5:53 am 
Subject:RV: [documentación] Llaves foráneas en MySQL/Innodb
From:Jaime Chavarriaga (jai@usb.edu.co)
Date:Sep 27, 2003 5:53:57 am
List:net.php.lists.php-es

Les envió un texto que estoy haciendo sobre el manejo de llaves foráneas en MySQL

8-) Jaime

-----Mensaje original----- Enviado el: Miércoles, 24 de Septiembre de 2003 07:34 a.m. Asunto: [lidis-desarrollo] [documentación] Llaves foráneas en MySQL

Uso de Llaves foráneas en MySQL/InnoDb

NOTA: Esta información aplica para versiones de MySQL superiores a 3.23.43b. Ha sido probada usando MySQL/InnoDB 4.0.15.

MySQL soporta diferentes manejadores de archivos de datos. Por defecto, utiliza un sistema MyISAM, pero es posible usar berkeley db o Innodb.

Cuando se usa InnoDB, es posible usar llaves foráneas y transacciones ACID. (De hecho, también soporta procedimiento almacenados, pero no esta disponible en las versiones binarias que distribuyen desde la web).

== Cómo saber si tu versión de MySQL soporta llaves foráneas

El tipo de almacenamiento InnoDb esta incluído en las versiones "max" de MySQL desde 3.23.43b, pero es parte del binario estándar desde 4.0.12.

NOTA: El sitio MySQL permite descargar las dos versiones desde el sitio web. La versión "max", con opciones no estándar, que estan en período de pruebas y maduración, y la versión "estándar" que incluye las opciones probadas ampliamente.

Para saber si tu versión de MySQL soporta llaves foráneas, es posible usar el comando... mysql> SHOW VARIABLES;

Para conocer el nivel de soporte, puedes usar mysql> SHOW VARIABLES LIKE 'have%';

Un resultado de ejemplo es +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_bdb | NO | | have_crypt | NO | | have_innodb | YES | | have_isam | YES | | have_raid | NO | | have_symlink | YES | | have_openssl | NO | | have_query_cache | YES | +------------------+-------+ 8 rows in set (0.00 sec)

Si aparece "have_innodb" con el valor de YES, es posible usar las llaves foráneas en tu sistema.

NOTA: Si no aparece la opción, puede ser que estes usando una versión sin esta opción habilitada. Es posible cerrar el sistema funcionando... C:> mysqladmin shutdown

E intentar cargar el servidor "max". En Win95, 98 y ME... C:> mysqld-max.exe

Y en Win NT, 2000, XP... C:> mysqld-max-nt.exe

== Como usar llaves foráneas

La sintáxis para definir llaves foráneas en MySQL es

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...) REFERENCES table_name (index_col_name, ...) [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

Para poder definir una llave foránea es necesario: 1) que las dos (2) tablas sean de tipo innoDB 2) que la tabla que hace referencia a la otra, contenga un INDEX con las columnas que hacen parte de la llave foránea. (si son varias columnas, deben estar en el mismo orden en el INDEX y el FOREIGN KEY).

NOTA: en otros sistemas de bases de datos, el índice de las columnas que hacen referencia a la otra tabla se genera automáticamente. En MySQL/InnoDB no ocurre así y es necesario crear el INDEX.

== un ejemplo

Un ejemplo del uso de llaves foráneas en la creación de tablas

CREATE TABLE roles ( codigo CHAR(3) NOT NULL default '', nombre CHAR(12) NOT NULL default '', PRIMARY KEY (codigo) ) TYPE=InnoDB;

CREATE TABLE usuarios ( codigo CHAR(9) NOT NULL default '', codigorol CHAR(3) NOT NULL default '', codigobeneficiario CHAR(9) NOT NULL default '', login CHAR(10) NOT NULL default '', password CHAR(10) NOT NULL default '', activo CHAR(3) NOT NULL default '', PRIMARY KEY (codigo), INDEX(codigorol), FOREIGN KEY (codigorol) REFERENCES roles(codigo) ) TYPE=InnoDB;

== restricciones adicionales de llave foránea

Adicionalmente a la revisión de existencia de registros en la tabla referenciada, es posible definir algunos comportamientos de las tablas. Por ejemplo, es posible establecer si el valor de la columna en la tabla referencia cambia, que deberá pasar con los valores relacionados: - ON DELETE establece que pasa con los valores relacionados cuando el valor en la tabla referenciada es eliminada. Es posible indicar que se borren todos los valores relacionados (CASCADE), que se coloque la llave foránea en NULL (SET NULL), que se genere un error (RESTRICT) o que no se realice acción alguna (NO ACTION). - ON UPDATE establece que pasa con los valores relacionados cuando el valor en la tabla referenciada es modificada. Es posible indicar que se borren todos los valores relacionados (CASCADE), que se coloque la llave foránea en NULL (SET NULL), que se genere un error (RESTRICT) o que no se realice acción alguna (NO ACTION).

Por ejemplo...

CREATE TABLE productos ( categoria INT NOT NULL, id INT NOT NULL, precio DECIMAL, PRIMARY KEY(category, id) ) TYPE=INNODB;

CREATE TABLE clientes ( id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB;

CREATE TABLE ordenes ( no INT NOT NULL AUTO_INCREMENT, categoria_producto INT NOT NULL, id_producto INT NOT NULL, id_cliente INT NOT NULL, PRIMARY KEY(no), INDEX (categoria_producto, id_producto), FOREIGN KEY (categoria_producto, id_producto) REFERENCES productos(categoria, id) ON DELETE RESTRICT ON UPDATE CASCADE, INDEX (id_cliente), FOREIGN KEY (id_cliente) REFERENCES clientes(id) ) TYPE=INNODB;

En este ejemplo, si el código y categoría de un producto cambia, se cambiarán automaticamente los valores correspondientes en la tabla de ordenes. Igualmente, si alguien intenta eliminar un producto que tiene una orden que le hace referencia, se generará un mensaje de error y no se permitirá eliminar el producto.

Es recomendable establecer las opciones de ON DELETE antes de ON UPDATE.

== Adicionando llaves foráneas a las tablas

Si ya se tienen algunas tablas, es posible adicionar las llaves foráneas a estas bases de datos existentes.

Si las tablas existen previamente, es posible que estén definidas con el tipo "MyISAM". Si este es el caso, es necesario cambiar el tipo de la tabla a "InnoDB". Para hacerlo, es posible usar... mysql> ALTER TABLE tabla TYPE=INNODB;

Por ejemplo... mysql> ALTER TABLE usuarios TYPE=innodb;

Es necesario recordar que para poder colocar las llaves foráneas, es necesario convertir las dos tablas al tipo InnoDB. No basta con convertir una sola, es necesario hacerlo con las dos tablas. mysql> ALTER TABLE roles TYPE=innodb;

Para agregar la restricción de llave foránea, es posible usar las opciones de ALTER TABLE. En este caso, puede ser necesario crear el índice y luego la llave foránea. mysql> ALTER TABLE usuarios ADD INDEX (codigorol); mysql> ALTER TABLE usuarios ADD FOREIGN KEY (codigorol) REFERENCES roles(codigo);

Si no se crea el índice (o no existe), el sistema genera un mensaje de error. Normalmente, un mensaje error similar a #1005 - Can't create table '.xxx.frm' (errno: 150)

NOTA: si no es necesario tener llaves foráneas, no es necesario convertir las tablas a InnoDB, es posible dejar las tablas en el tipo MyISAM. Es posible definir una base de datos con tablas en diferentes tipos, sin problemas.

== Desactivando la llave foránea

Durante algunas operaciones de carga de datos, es recomendable desactivar la revisión de las llaves foráneas con el fin de acelerar el procesamiento.

Para desactivar la revisión de las llaves foráneas, es posible usar la opción: mysql> SET FOREIGN_KEY_CHECKS=0;

== Detectando las restricciones de llaves foráneas

Desde la versión 3.23.50, las definiciones de todas las restricciones, incluyendo las de llave foránea, pueden obtenerse usando la instrucción mysql> SHOW CREATE TABLE tablename

Por ejemplo, usando mysql> SHOW CREATE TABLE usuarios

Es posible obtener la información +----------+------------------------------------------------------------ -------+ | Table | Create Table | +----------+------------------------------------------------------------ -------+ | usuarios | CREATE TABLE `usuarios` ( `codigo` char(9) NOT NULL default '', `codigorol` char(3) NOT NULL default '', `codigobeneficiario` char(9) NOT NULL default '', `login` char(10) NOT NULL default '', `password` char(10) NOT NULL default '', `activo` char(3) NOT NULL default '', PRIMARY KEY (`codigo`), KEY `codigorol` (`codigorol`), CONSTRAINT `0_27` FOREIGN KEY (`codigorol`) REFERENCES `roles` (`codigo`) ) TYPE=InnoDB | +----------+------------------------------------------------------------ -------+ 1 row in set (0.00 sec)

También es posible obtener la información usando mysql> SHOW TABLE STATUS FROM databasename LIKE 'tablename';

Allí es posible revisar la columna de "comments" y revisar las restricciones de FOREIGN KEY.

Por ejemplo, usando mysql> SHOW TABLE STATUS FROM biblioteca LIKE 'usuarios';

+----------+--------+-----+--------------------------------------+ | Name | Type | ... | Comment | +----------+--------+- +--------------------------------------+ | usuarios | InnoDB | ... | InnoDB free: 59392 kB; (codigorol) REFER bib2/roles(codigo) | +----------+--------+-----+--------------------------------------+ 1 row in set (0.01 sec)

== Eliminando la llave foránea

Si una llave foránea deja de ser necesaria, es posible eliminar esa restricción de llave foránea.

A partir de MySQL 4.0.13, es posible eliminar la llave foránea de una tabla mysql> ALTER TABLE tablename DROP FOREIGN KEY internally_generated_foreign_key_id

Para poder eliminar la referencia, es posible determinar el identificador de la llave foránea usando la opción de mysql> SHOW CREATE TABLE tablename;

== Enlaces

Documentación sobre las llaves foráneas de MySQL/InnoDB http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html

Documentación sobre el comando SHOW en MySQL http://www.mysql.com/doc/en/SHOW.html

Manual de referencia de MySQL/InnoDB http://www.innodb.com/ibman.html

Información mínima sobre el uso de MySQL/InnoDB http://www.innodb.com/howtouse.html

Oracle y MySQL fueron los ganadores en la comparación de varios sistemas manejadores de bases de datos http://www.eweek.com/article/0,3658,s=708&a=23115,00.asp

Información sobre el uso de transacciones en MySQL/InnoDb

http://hotwired.lycos.com/webmonkey/backend/databases/tutorials/tutorial 2.html

http://mysql.planetmirror.com/Downloads/Presentations/2003-07-12-LinuxTa g-InnoDB.pdf

Información sobre el uso de logs en MySQL/InnoDb http://www.devarticles.com/content.php?articleId=181&page=1

8-) Jaime

Página web : http://argos.usb.edu.co/lidis Cancelar su suscripción: lidi@gruposyahoo.com

Su uso de Yahoo! Grupos está sujeto a las http://e1.docs.yahoo.com/info/utos.html