PDF de programación - Integridad referencial en MySQL

Integridad referencial en MySQL



MySQL 4.0 soporta cinco tipos de tablas: MyISAM, ISAM, HEAP, BDB (Base de datos Berkeley), e InnoDB. BDB e InnoDB son ambas tipos de tablas
transaccionales. Además de poder trabajar con transacciones en MySQL, las tablas del tipo InnoDB también tienen soporte para la definición de
claves foráneas, por lo que se nos permite definir reglas o restricciones que garanticen la integridad referencial de los registros.

A partir de la versión 4.0, MySQL ha agregado InnoDB a la lista de tipos de tablas soportados en una instalación típica. En este artículo se asume
que se cuenta ya con un servidor MySQL con soporte para el tipo de tablas InnoDB. En nuestro caso haremos uso de un servidor MySQL 4.013
ejecutándose en un sistema MSWindows.

Nota: para asegurarnos que tenemos soporte para el tipo de tablas InnoDB podemos ejecutar la siguiente sentencia:

mysql> SHOW VARIABLES LIKE '%innodb%';
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| have_innodb | YES |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | . |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | . |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
+---------------------------------+------------------------+
20 rows in set (0.00 sec)

La variable más importante es por supuesto have_innodb que tiene el valor YES.

Claves primarias

Para entender lo que son las claves foráneas, tal vez sea necesario entender primero lo que son las claves primarias.

Es un hecho que las claves juegan un papel muy importante no sólo en MySQL, sino en cualquier base de datos relacional. De manera simple, las
claves proporcionan una manera rápida y eficiente de buscar datos en una tabla, además de que permiten preservar la integridad de los datos.

Una clave candidata es un campo, o una combinación de campos, que identifican de manera única un registro de una tabla. Éstas no pueden
contener valores nulos, y su valor debe ser único.

Una clave primaria es una clave candidata que ha sido diseñada para identificar de manera única a los registros de una tabla a través de toda la
estructura de la base de datos.

La selección de una clave primaria es muy importante en el diseño de una base de datos, ya que es un elemento clave de los datos que facilita la
unión de tablas y el concepto total de una base de datos relacional.

Las claves primarias deben ser únicas y no nulas, de manera que garanticen que una fila de una tabla pueda ser siempre referenciada a través de
su clave primaria.

MySQL requiere que se especifique NOT NULL para las columnas que se van a utilizar como claves primarias al momento de crear una tabla.

Claves foráneas e integridad referencial



Podemos decir de manera simple que integridad referencial significa que cuando un registro en una tabla haga referencia a un registro en otra
tabla, el registro correspondiente debe existir. Por ejemplo, consideremos la relación entre una tabla cliente y una tabla venta.

+------------+ +-------------+
| cliente | | venta |
+------------+ +-------------+
| id_cliente | | id_factura |
| nombre | | id_cliente |
+------------+ | cantidad |
+-------------+

Para poder establecer una relación entre dos tablas, es necesario asignar un campo en común a las dos tablas. Para este ejemplo, el campo
id_cliente existe tanto en la tabla cliente como en la tabla venta. La mayoría de las veces, este campo en común debe ser una clave primaria en
alguna de las tablas. Vamos a insertar algunos datos en estas tablas.

Tabla cliente
+------------+--------------+
| id_cliente | nombre |
+------------+--------------+
| 1 | Juan penas |
| 2 | Pepe el Toro |
+------------+--------------+

Tabla venta
+------------+------------+----------+
| id_factura | id_cliente | cantidad |
+------------+------------+----------+
| 1 | 1 | 23 |
| 2 | 3 | 39 |
| 3 | 2 | 81 |
+------------+------------+----------+

Hay dos registros en la tabla cliente, pero existen 3 id_cliente distintos en la tabla venta. Habíamos dicho que las dos tablas se relacionan con el
campo id_cliente, por lo tanto, podemos decir que Juan Penas tiene una cantidad de 23, y Pepe el Toro 81, sin embargo, no hay un nombre que se
corresponda con el id_cliente 3.

Las relaciones de claves foráneas se describen como relaciones padre/hijo (en nuestro ejemplo, cliente es el padre y venta es el hijo), y se dice que
un registro es huérfano cuando su padre ya no existe.

Cuando en una base de datos se da una situación como esta, se dice que se tiene una integridad referencial pobre (pueden existir otra clase de
problemas de integridad). Generalmente esto va ligado a un mal diseño, y puede generar otro tipo de problemas en la base de datos, por lo tanto
debemos evitar esta situación siempre que sea posible.

En el pasado, MySQL no se esforzaba en evitar este tipo de situaciones, y la responsabilidad pasaba a la aplicación. Para muchos desarrolladores,
esta no era una situación del todo grata, y por lo tanto no se consideraba a MySQL para ser usado en sistemas "serios". Por supuesto, esta fue una
de las cosas más solicitadas en las anteriores versiones de MySQL; que se tuviera soporte para claves foráneas, para que MySQL mantenga la
integridad referencial de los datos.

Una clave foránea es simplemente un campo en una tabla que se corresponde con la clave primaria de otra tabla. Para este ejemplo, el campo
id_cliente en la tabla venta es la clave foránea. Nótese que este campo se corresponde con el campo id_cliente en la tabla cliente, en dónde este
campo es la clave primaria.

Las claves foráneas tienen que ver precisamente con la integridad referencial, lo que significa que si una clave foránea contiene un valor, ese valor
se refiere a un registro existente en la tabla relacionada.





Claves foráneas en MySQL



Estrictamente hablando, para que un campo sea una clave foránea, éste necesita ser definido como tal al momento de crear una tabla. Se pueden
definir claves foráneas en cualquier tipo de tabla de MySQL, pero únicamente tienen sentido cuando se usan tablas del tipo InnoDB.

A partir de la versión 3.23.43b, se pueden definir restricciones de claves foráneas con el uso de tablas InnoDB. InnoDB es el primer tipo de tabla
que permite definir estas restricciones para garantizar la integridad de los datos.

Para trabajar con claves foráneas, necesitamos hacer lo siguiente:

•
•
•

Crear ambas tablas del tipo InnoDB.
Usar la sintaxis FOREIGN KEY(campo_fk) REFERENCES nombre_tabla (nombre_campo)
Crear un índice en el campo que ha sido declarado clave foránea.

InnoDB no crea de manera automática índices en las claves foráneas o en las claves referenciadas, así que debemos crearlos de manera explícita.
Los índices son necesarios para que la verificación de las claves foráneas sea más rápida. A continuación se muestra como definir las dos tablas de
ejemplo con una clave foránea.

CREATE TABLE cliente
(
id_cliente INT NOT NULL,
nombre VARCHAR(30),
PRIMARY KEY (id_cliente)
) TYPE = INNODB;

CREATE TABLE venta
(
id_factura INT NOT NULL,
id_cliente INT NOT NULL,
cantidad INT,
PRIMARY KEY(id_factura),
INDEX (id_cliente),
FOREIGN KEY (id_cliente) REFERENCES cliente(id_cliente)
) TYPE = INNODB;

La sintaxis completa de una restricción de clave foránea es la siguiente:

[CONSTRAINT símbolo] FOREIGN KEY (nombre_columna, ...)
REFERENCES nombre_tabla (nombre_columna, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]

Las columnas correspondientes en la clave foránea y en la clave referenciada deben tener tipos de datos similares para que puedan ser
comparadas sin la necesidad de hacer una conversión de tipos. El tamaño y el signo de los tipos enteros debe ser el mismo. En las columnas de
tipo caracter, el tamaño no tiene que ser el mismo necesariamente.

Si MySQL da un error cuyo número es el 1005 al momento de ejecutar una sentencia CREATE TABLE, y el mensaje de error se refiere al número
150, la creación de la tabla falló porque la restricción de la clave foránea no se hizo de la manera adecuada. De la misma manera, si falla una
sentencia ALTER TABLE y se hace referencia al error número 150, esto significa que la definición de la restricción de la clave foránea no se hizo
adecuadamente. A partir de la versión 4.0.13 de MySQL, se puede usar la sentenci