Qué hacer si al crear FOREIGN KEY da error

En mi caso tengo una tabla users con un campo library_id y una tabla libraries con un campo id int PRIMARY KEY y autonumérico.

En MySql y en postgreSQL la consulta es la misma:

ALTER TABLE users ADD CONSTRAINT fk_users_library_id 
           FOREIGN KEY (library_id) REFERENCES libraries (id); 

El error fue:

#1452 - Cannot add or update a child row: a foreign key constraint fails
(nenuro.#sql-1_a04, CONSTRAINT fk_users_library_id FOREIGN KEY (library_id) REFERENCES libraries (id))

Las causas de error pueden ser múltiples pero las más comunes y lo que se tiene que revisar es:

1. Inconsistencia de datos, registros huerfanos: La tabla users tiene filas con library_id que no existen en la tabla libraries.
   
   Por ejemplo, si users.library_id tiene el valor 5, pero no existe ninguna fila en libraries con id = 5, esto causará el error. Para esto ejecutamos la siguiente sentencia, igual para MySql y postgreSQL

   
   SELECT DISTINCT library_id
   FROM users
   WHERE library_id IS NOT NULL
   AND library_id NOT IN (SELECT id FROM libraries);
   
   
   

   Para buscar los valores de library_id en users no tienen un valor correspondiente en libraries.
   
   Si se encuentran valores huérfanos lo que hay que hacer es actualizar las tablas o eliminar esos valores.
   
   Se pueden borrar con esta consulta, igual para MySql y postgreSQL, aunque primero comprobamos

   
   SELECT * FROM users
   WHERE library_id IS NOT NULL
   AND library_id NOT IN (SELECT id FROM libraries);
   
   
   

   Y después borramos

   
   DELETE FROM users
   WHERE library_id IS NOT NULL
   AND library_id NOT IN (SELECT id FROM libraries);
   
   
   

2. Valores NULL: Si library_id permite valores NULL en la tabla users, pero la columna id de libraries no admite valores NULL o la restricción FOREIGN KEY no está configurada para permitir NULL, el error se desencadenará.
   
   Esto lo podemos ver buscando valores null en la tabla users:

   
   SELECT COUNT(*) FROM users WHERE library_id IS NULL;
   
   
   

3. Incompatibilidad de tipos de datos: La columna de clave foránea library_id y la columna referenciada id en libraries deben tener el mismo tipo de datos y atributos (INT, UNSIGNED, etc.).
   
   
4. Verificar que el índice no esté ya creado. Esto lo podemos hacer con la consulta para MySql

   
   SHOW INDEX FROM users;
   SHOW INDEX FROM libraries;
   
   
   

   Y para postgreSQL

   
   SELECT
       indexname,
       indexdef
   FROM
       pg_indexes
   WHERE
       tablename = 'users' or tablename = 'libraries'
   ORDER BY tablename;
   
   
   
   

   O desde el terminal

   \di users*
   \di libraries*

   
   
5. Atributos de la Columna ( Tipo dato, Charset y Collation).
   
   Hay que verificar que el tipo de dato y el collation sean iguales, es decir, los dos int o los dos varchar(6).
   
   Si uno es varchar y el otro char por ejemplo, fallará.
   
   Podemos ver la estructura de de las tablas para MySqlcon:

   
   DESCRIBE users; 
   DESCRIBE libraries;
   
   
   

   o con:

   
   SHOW FULL COLUMNS FROM users; 
   SHOW FULL COLUMNS FROM libraries;
   
   
   

   También podemos ver la estructura de las tablas.

   
   SHOW CREATE TABLE users;
   
   
   

   Para postgreSQL se puede ver la estructura de una tabla con esta sentencia

   
   SELECT 
       column_name, 
       data_type, 
       is_nullable, 
       column_default
   FROM 
       information_schema.columns
   WHERE 
       table_name = 'users';
   
   
   

Y después de todo lo visto no hay forma de ver el error o corregirlo y seguis queriendo tener esa FOREIGN KEY lo que se puede hacer es desactivar la comprobación de FOREIGN KEY crear la clave y volver a activar la comprobación, pero esto solo sirve para MySql

SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE users ADD CONSTRAINT `fk_users_library_id` 
     FOREIGN KEY (`library_id`) REFERENCES libraries (`id`);

SET FOREIGN_KEY_CHECKS = 1;

PostgreSQL no permite desactivar todas las claves foráneas con un simple SET. Y si se necesita realizar muchos procesos de cargas o migraciones lo menor es desactivar temporalmente constraints (eliminarlas y luego restaurarlas).

Pero hacer esto último es dejar el error sin solucionar.

Resumiendo un poco el tema, los siguientes puntos aplican a MySQL, PostgreSQL y SQL Server:

1. Tipos de datos deben coincidir.
2. La columna hija y la columna referenciada deben tener el mismo tipo, longitud y atributos (por ejemplo, INTEGER no puede referenciar un BIGINT).
3. La columna referenciada debe tener una PRIMARY KEY o UNIQUE Los tres motores requieren que la columna en la tabla padre tenga una restricción PRIMARY KEY o UNIQUE.
4. No debe haber datos huérfanos en la tabla hija Si ya hay registros en la tabla hija que no coinciden con la tabla padre, no se puede crear la clave foránea.

Y esto es todo, feliz programming!!!
Saludos
Alex
:-)
/

Si te ha gustado el artículo compartelo en:      

---

---

---

---

---