Ejemplo de Tablas relacionales en MySql

Muchos habreis tenido problemas al crear tablas relacionadas con MySql, pero de verdad que es bastante sencillo, solo hay que tener en cuenta una serie de premisas:

  • Las tablas que se van a relacionar tienen que ser tipo InnoDb(InnoDB es el primer tipo de tabla que permite definir estricciones de claves foráneas para garantizar la integridad de los datos).
  • Usar sintaxis FOREIGN KEY (campo_fk) REFERENCES nombre_tabla(nombre_campo)
  • Crear un gndice en el campo que ha sido declarado claveforánea

Es necesario el uso de índices para que la verificación de las claves foráneas sea más rápida. Vamos a ver un ejemplo de una definición de 2 tablas relacionadas. Clientes y privilegios:

PLAIN TEXT
MySQL:
  1. CREATE TABLE clientes
  2. (
  3. id_cliente INT NOT NULL AUTO_INCREMENT,
  4. nombre VARCHAR(30),
  5. PRIMARY KEY (id_cliente)
  6. ) TYPE = INNODB;
  7.  
  8. CREATE TABLE privilegios
  9. (
  10. id_privilegio INT NOT NULL AUTO_INCREMENT,
  11. id_cliente INT NOT NULL,
  12. privilegio   INT(2),
  13. PRIMARY KEY(id_privilegio),
  14. INDEX (id_cliente),
  15. FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente)
  16. ) TYPE = INNODB;

De esta manera estamos relacionando la tabla clientes y privilegios por el campo id_cliente.

La clave foránea (FOREIGN KEY) y la Referenciada tienen que tener tipos de datos similares para que puedan ser comparadas sin la necesidad de hacer una conversión de tipos.

Ahora ya tenemos las tablas relacionadas y seguro que os haceis la siguiente pregunta:

"¿Cómo inserto registros en las tablas que tengan claves foráneas (FOREIGN KEYS)?"

Bueno, esta pregunta realmente nos la podemos hacer para cuando se inserta un registro, se borra o se actualiza, ya que en las tres lo tendremos que tener en cuenta.

Ahora vamos a insertar algún registro para verificar que todo está correcto:

PLAIN TEXT
MySQL:
  1. INSERT INTO clientes VALUES (1, 'Pedro Picapiedras');
  2. INSERT INTO clientes VALUES (2, 'Pablo Marmol');
  3. INSERT INTO clientes VALUES (3, 'Ana Botella');

PLAIN TEXT
MySQL:
  1. INSERT INTO privilegios VALUES (1,1,10);
  2. INSERT INTO privilegios VALUES (2,3,05);
  3. INSERT INTO privilegios VALUES (3,2,01);

Nota: No me pregunteis porque he puesto Ana Botella dentro de esta categoría de clientes :D .

Hasta ahora ha ido todo bien, pero ¿que pasaría si intentamos insertar un registro en la tabla "privilegios" en el que nos refiramos a un cliente que no exista en la tabla clientes?, vamos a probarlo:

PLAIN TEXT
MySQL:
  1. INSERT INTO privilegios VALUES (4,5,10);

Pues sí nos tiene que devolver un error tal como este:

"ERROR 1216: Cannot add or update a child row: a foreign key constraint fails"

La razón es bastante evidente, estamos intentando insertar un registro en la tabla privilegios sobre un cliente "id_cliente=5", y hasta el momento no existe.

Ahora vamos a intentar eliminar un registro de la tabla clientes:

PLAIN TEXT
MySQL:
  1. DELETE FROM cliente WHERE id_cliente=3;

Debido a que tenemos la restricción de la clave foránea no permitirá eliminar el registro puesto que se hace referencia a él en la tabla "privilegios", por lo tanto Mysql nos devolvería el siguiente error:

"ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails"

Para eliminar este tipo de registros con claves foráneas existen lo que se llama eliminación en CASCADA, en la que todos los registros relacionados se eliminan según las relaciones de claves foráneas.

Para llevar a cabo una eliminación de todos los registros relacionados por las claves foráneas en distintas tablas se usa la funcion "ON DELETE CASCADE".

Si se especifica ON DELETE CASCADE, y una fila en la tabla padre es eliminada, entonces se eliminarán las filas de la tabla hijo cuya clave foránea sea igual al valor de la clave referenciada en la tabla padre. Esta acción siempre ha estado disponible en MySQL. Fuente: Mysql-hispano.org

Veamos un ejemplo de eliminación en cascada:

PLAIN TEXT
MySQL:
  1. ALTER TABLE privilegios ADD FOREIGN KEY(id_cliente)
  2. REFERENCES cliente(id_cliente) ON DELETE CASCADE;

Con esta sentencia, ya tendremos dispuesta la tabla para que se pueda eliminar en cascada. Por lo tanto si queremos eliminar ahora al cliente "Ana Botella", tan sólo tendriamos que ejecutar lo sisguiente:

PLAIN TEXT
MySQL:
  1. DELETE FROM cliente WHERE id_cliente=3;

Si haceis una consulta ahora en las dos bases de datos, "clientes" y "privilegios", vereis como se ha eliminado "Ana Botella" de las dos tablas.

Al igual que la eliminación también podemos actuar en cascada con las actualizaciones con la sentencia "ON UPDATE CASCADE".

Esto es todo, espero ayudaros a entender un poco mejor la integridad referencial en Mysql y la relación entre tablas.

Vía: MySql-Hispano - Integridad referencial en MySQL

Posts Relacionados