Hero

Como evitar la revision de restricción de llaves foréneas en MySQL

Mayo 10, 2013

kenneth
MySQL
SQL

Cuando trabajamos con MySQL, en nuestras tablas podemos definir llaves foráneas, para hacer referencia a otras tablas. En general MySQL, en declaraciones SQL como ‘insert’, ‘delete’ o ‘update’ se verifican las restricciones UNIQUE y las FOREIGN KEY fila por fila, para así mantener la consistencia de los datos. Existen ocasiones muy particulares donde deseamos que esta revisión no se lleve a cabo, supongamos que contamos con el siguiente esquema de base de datos:

<pre title="Esquema de base de datos">CREATE TABLE roles (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(200) NOT NULL UNIQUE
);

CREATE TABLE users (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(200) NOT NULL UNIQUE,
IdRole INT NOT NULL,
CONSTRAINT fk_user_role FOREIGN KEY (IdRole)
REFERENCES Roles(Id)
);

Ahora revisaremos con “SHOW TABLES”, y observaremos que existen las dos tablas recientemente creadas (roles, users), se muestra a continuación:

tablas roles users

Entonces, a continuación agregaremos unos datos de pruebas, primero en la tabla de “roles” para luego crear unos usuarios y relacionarlos con un rol específico, podemos utilizar el siguiente código:

<pre title="Agregando datos">INSERT INTO roles (Name) VALUES ('Admin'), ('User'), ('Anonymous');
INSERT INTO users (Name, IdRole) VALUES ('User1', 1), ('User2', 2), ('User3', 3);

Una vez con la información ingresada, si nos solicitan eliminar la tabla ‘roles’ podríamos utilizar la instrucción “DROP TABLE”, pero si al hacerlo obtendremos un error del sistema, puesto que existen campos referenciando a los campos de la tabla ‘roles’ que se esta intentando borrar, el error seria similar al presentado a continuación:

<pre title="DROP TABLE roles">DROP TABLE roles;

-- ERROR 1217 (23000): 
-- Cannot delete or update a parent row: a foreign key constraint fails

Para estos casos en particular, necesitamos que MySQL no revise las restricciones de llaves foráneas para lograr cumplir con la tarea que nos han solicitado, entonces para estos casos vamos a indicarle a MySQL que no revise las restricciones utilizando la siguiente declaración:

<pre title="Evita revisar restricciones de llaves foráneas">SET FOREIGN_KEY_CHECKS=0;

Advertencia esta exclusión de la verificación se aplica para todas las tablas, se recomienda colocar el sistema en mantenimiento o colocar a los usuario diferentes al administrador para que ingresen al sistema solo en modo lectura.

Una vez hecho esto, podemos eliminar la tabla sin problema alguno, ahora bien, no debemos olvidar volver a configurar nuevamente MySQL para que continúe revisando las restricciones de llaves foráneas, el código se puede ver como se muestra a continuación:

<pre title="Eliminar tabla y habilita revisar restricciones de llaves foráneas">DROP TABLE roles;
SET FOREIGN_KEY_CHECKS=1;

Es necesario ingresar la información necesaria para que los foreign keys cumplan con las restricciones antes de habilitarlo y de esta forma mantener la consistencia de los datos.

Ahora si revisamos nuevamente con “SHOW TABLES”, veremos que solamente queda la tabla llamada “users”, como se observa en la imagen:

tabla uses

Espero que les sea de mucha ayuda, para situaciones particulares como estas,

Saludos,

Recibe consejos y oportunidades de trabajo 100% remotas y en dólares de weKnow Inc.