formed - MySQL Creando tablas con claves externas dando errno: 150

foreign key mysql console

Question

Estoy intentando crear una tabla en MySQL con dos claves externas, que hacen referencia a las claves principales en otras 2 tablas, pero obtengo un error errno: 150 y no creará la tabla.

Aquí está el SQL para las 3 tablas:

CREATE TABLE role_groups (
  `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
  `name` varchar(20),
  `description` varchar(200),
  PRIMARY KEY (`role_group_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50),
  `description` varchar(200),
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB;

create table role_map (
  `role_map_id` int not null `auto_increment`,
  `role_id` int not null,
  `role_group_id` int not null,
  primary key(`role_map_id`),
  foreign key(`role_id`) references roles(`role_id`),
  foreign key(`role_group_id`) references role_groups(`role_group_id`)
) engine=InnoDB;

Cualquier ayuda sería muy apreciada.

Answer 1

¿Cuál es el estado actual de su base de datos cuando ejecuta este script? ¿Está completamente vacío? Su SQL funciona bien para mí al crear una base de datos desde cero, pero errno 150 generalmente tiene que ver con dejar caer y recrear las tablas que son parte de una clave externa. Tengo la sensación de que no estás trabajando con una base de datos 100% nueva y fresca.

Si está extrayendo un error al "origen" de su archivo SQL, debe poder ejecutar el comando "SHOW ENGINE INNODB STATUS" desde el indicador de MySQL inmediatamente después del comando "fuente" para ver información de error más detallada.

También puede consultar la entrada del manual:

Si vuelve a crear una tabla que se descartó, debe tener una definición que se ajuste a las restricciones de clave externa que la hacen referencia. Debe tener los nombres y tipos de columnas correctos, y debe tener índices en las claves referenciadas, como se indicó anteriormente. Si no se cumplen, MySQL devuelve el error número 1005 y se refiere al error 150 en el mensaje de error. Si MySQL informa un error número 1005 desde una instrucción CREATE TABLE, y el mensaje de error se refiere al error 150, la creación de la tabla falló debido a que una restricción de clave externa no se formó correctamente.

- Manual de referencia de MySQL 5.1 .

Answer 2

(Notas laterales demasiado grandes para un comentario)

No hay necesidad de una identificación AUTO_INCREMENT en una tabla de mapeo; desaste de eso.

Cambie la PRIMARY KEY a (role_id, role_group_id) (en cualquier orden). Esto hará que los accesos sean más rápidos.

Como probablemente desee mapear ambas direcciones, también agregue un INDEX con esas dos columnas en el orden opuesto. (No hay necesidad de hacerlo UNIQUE ).

Más consejos: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Answer 3

Asegúrese de que las claves externas no estén enumeradas como únicas en el elemento primario. Tuve el mismo problema y lo resolví delimitándolo como no único.

Answer 4

Asegúrese de que las propiedades de los dos campos que intenta vincular con una restricción sean exactamente las mismas.

A menudo, la propiedad ''unsigned'' en una columna de identificación lo alcanzará.

ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;

Answer 5

Como señaló @andrewdotn, la mejor manera es ver el error detallado ( SHOW ENGINE INNODB STATUS; ) en lugar de solo un código de error.

Una de las razones podría ser que un índice ya existe con el mismo nombre, puede estar en otra tabla. Como práctica, recomiendo prefijar el nombre de la tabla antes del nombre del índice para evitar tales colisiones. por ejemplo, en lugar de idx_userId usa idx_userActionMapping_userId .

Answer 6

Consejo útil, use SHOW WARNINGS; después de probar su consulta CREATE , recibirá el error y la advertencia más detallada:

    ---------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                 |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+
| Warning |  150 | Create table ''fakeDatabase/exampleTable'' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error   | 1005 | Can''t create table ''exampleTable'' (errno:150)                                                                                                                                                                           |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+

Entonces, en este caso, ¡es hora de volver a crear mi mesa!

Answer 7

Cuando la restricción de clave externa se basa en el tipo de varchar , entonces, además de la lista proporcionada por marv-el la columna de destino debe tener una restricción única.

Answer 8

Definitivamente no es el caso, pero encontré este error bastante común y no evidente. El objetivo de una FOREIGN KEY podría no ser PRIMARY KEY . La respuesta que se vuelve útil para mí es:

Una LLAVE EXTRAÑA siempre debe apuntarse a un campo VERDADERO DE PRIMERA CLAVE de otra tabla.

CREATE TABLE users(
   id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40));

CREATE TABLE userroles(
   id INT AUTO_INCREMENT PRIMARY KEY,
   user_id INT NOT NULL,
   FOREIGN KEY(user_id) REFERENCES users(id));

Answer 9

El mensaje "errno 150" genérico de MySQL " significa que una restricción de clave externa no se formó correctamente ." Como probablemente ya sepa si está leyendo esta página, el mensaje de error genérico "errno: 150" es realmente inútil. Sin embargo:

Puede obtener el mensaje de error real ejecutando SHOW ENGINE INNODB STATUS; y luego busca el LATEST FOREIGN KEY ERROR en la salida.

Por ejemplo, este intento de crear una restricción de clave externa:

CREATE TABLE t1
(id INTEGER);

CREATE TABLE t2
(t1_id INTEGER,
 CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));

falla con el error Can''t create table ''test.t2'' (errno: 150) . Eso no le dice a nadie nada útil, aparte de que es un problema de clave externa. Pero ejecutar SHOW ENGINE INNODB STATUS; y dirá:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

Dice que el problema es que no puede encontrar un índice. SHOW INDEX FROM t1 muestra que no hay ningún índice para la tabla t1 . Solucione eso, por ejemplo, definiendo una clave principal en t1 , y la restricción de clave externa se creará con éxito.

Answer 10

En mi caso, esto se debió al hecho de que el campo que era un campo de clave externa tenía un nombre demasiado largo, es decir. foreign key (some_other_table_with_long_name_id) . Intenta algo más corto. El mensaje de error es un poco engañoso en ese caso.

Además, como @Jon se mencionó anteriormente, las definiciones de campo tienen que ser las mismas (tenga cuidado con el subtipo unsigned ).

Answer 11

Esto generalmente ocurre cuando intentas crear un archivo fuente en una base de datos existente. Suelta todas las tablas primero (o el DB mismo). Y luego el archivo fuente con SET foreign_key_checks = 0; al principio y SET foreign_key_checks = 1; al final.

Answer 12

He encontrado otra razón por la que esto falla ... nombres de tablas sensibles a mayúsculas y minúsculas.

Para esta definición de tabla

CREATE TABLE user (
  userId int PRIMARY KEY AUTO_INCREMENT,
  username varchar(30) NOT NULL
) ENGINE=InnoDB;

Esta definición de tabla funciona

CREATE TABLE product (
  id int PRIMARY KEY AUTO_INCREMENT,
  userId int,
  FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId)
) ENGINE=InnoDB;

mientras que este falla

CREATE TABLE product (
  id int PRIMARY KEY AUTO_INCREMENT,
  userId int,
  FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId)
) ENGINE=InnoDB;

El hecho de que funcionó en Windows y falló en Unix me llevó un par de horas averiguarlo. Espero que ayude a alguien más.

Answer 13

MySQL Workbench 6.3 para Mac OS.

Problema: errno 150 en la tabla X al intentar hacer ingeniería directa en un diagrama DB, 20 de 21 intentos exitosos, 1 falla. Si se eliminaron FK en la tabla X, el error se movió a una tabla diferente que no estaba fallando antes.

Cambié todas las tablas del motor a myISAM y funcionó bien.

Answer 14

Para las personas que están viendo este hilo con el mismo problema:

Hay muchas razones para obtener errores como este. Para obtener una lista bastante completa de las causas y soluciones de los errores de clave externa en MySQL (incluidos los que se analizan aquí), consulte este enlace:

MySQL Foreign Key Errors y Errno 150

Answer 15

Para otros que encuentran esta entrada de SO a través de Google: asegúrese de no estar intentando realizar una acción SET NULL en una columna de clave externa (para ser) definida como "NOT NULL". Eso causó gran frustración hasta que recordé hacer un CHECK ENGINE INNODB STATUS.

Answer 16

Por favor, asegúrese de que al principio

1. estás usando tablas InnoDB.
2. el campo para FOREIGN KEY tiene el mismo tipo y longitud (!) que el campo fuente.

Tuve el mismo problema y lo solucioné. Tenía INT sin signo para un campo y solo entero para otro campo.

Answer 17

También vale la pena comprobar que no está operando accidentalmente en la base de datos incorrecta. Este error ocurrirá si la tabla externa no existe. ¿Por qué MySQL tiene que ser tan críptico?

Answer 18

Tuve el mismo problema con ALTER TABLE ADD FOREIGN KEY .

Después de una hora, descubrí que estas condiciones deben cumplirse para no obtener el error 150:

1. La tabla principal debe existir antes de definir una clave externa para referenciarla. Debe definir las tablas en el orden correcto: primero la tabla Parent, luego la tabla Child. Si ambas tablas se refieren entre sí, debe crear una tabla sin restricciones FK, luego crear la segunda tabla, luego agregar la restricción FK a la primera tabla con ALTER TABLE .

2. Las dos tablas deben soportar restricciones de clave externa, es decir ENGINE=InnoDB . Otros motores de almacenamiento ignoran silenciosamente las definiciones de clave externa, por lo que no devuelven ningún error o advertencia, pero la restricción FK no se guarda.

3. Las columnas a las que se hace referencia en la tabla Parent deben ser las columnas más a la izquierda de una clave. Mejor si la clave en el elemento primario es PRIMARY KEY o UNIQUE KEY .

4. La definición FK debe hacer referencia a la (s) columna (s) PK en el mismo orden que la definición PK. Por ejemplo, si el FK REFERENCES Parent(a,b,c) , el Parent''s PK no debe definirse en columnas en orden (a,c,b) .

5. Las columnas PK en la tabla Parent deben ser del mismo tipo que las columnas FK en la tabla Child. Por ejemplo, si una columna PK en la tabla Parent está UNSIGNED , asegúrese de definir UNSIGNED para la columna correspondiente en el campo Tabla secundaria.

   Excepción: la longitud de las cadenas puede ser diferente. Por ejemplo, VARCHAR(10) puede hacer referencia a VARCHAR(20) o viceversa.

6. Cualquier columna FK de tipo cadena debe tener el mismo conjunto de caracteres y colación que la (s) columna (s) PK correspondiente (s).

7. Si ya hay datos en la tabla secundaria, cada valor en la (s) columna (s) FK debe coincidir con un valor en la (s) columna (s) PK de tabla principal. Verifique esto con una consulta como:

   SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK 
   WHERE Parent.PK IS NULL;

   Esto debe devolver cero (0) valores no coincidentes. Obviamente, esta consulta es un ejemplo genérico; debe sustituir sus nombres de tabla y columnas.

8. Ni la tabla principal ni la tabla TEMPORARY pueden ser una tabla TEMPORARY .

9. Ni la tabla Parent ni la tabla Child pueden ser una tabla PARTITIONED .

10. Si declara un FK con la opción ON DELETE SET NULL , entonces la (s) columna (s) FK deben ser anulables.

11. Si declara un nombre de restricción para una clave externa, el nombre de la restricción debe ser único en todo el esquema, no solo en la tabla en la que se define la restricción. Dos tablas pueden no tener su propia restricción con el mismo nombre.

Espero que esto ayude.