RV: [documentación] Llaves foráneas en MySQL/Innodb
| From: | Jaime Chavarriaga | Date: | Sat, 27 Sep 2003 12:54:19 +0000 |
| Subject: | RV: [documentación] Llaves foráneas en MySQL/Innodb | ||
| Groups: | php.general.es | ||
| Request: | Send a blank email to general-es+get-40995@lists.php.net to get a copy of this message | ||
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: lidis-desarrollo-unsubscribe@gruposyahoo.com
Su uso de Yahoo! Grupos está sujeto a las
http://e1.docs.yahoo.com/info/utos.html