incorrectly - MySQL Foreign Key Error 1005 errno 150

foreign key mysql mariadb

Question

Estoy haciendo una pequeña Base de datos con MySQL Workbench. Tengo una mesa principal, llamada "Immobili", que tiene una clave principal compuesta por cuatro columnas: (Comune, Via, Civico, Immobile).

Ahora, también tengo otras tres tablas, que tienen la misma clave primaria (Comune, Via, Civico, Immobile), pero estos campos también están referenciados en la tabla Immobili.

Primera pregunta: ¿Puedo hacer una clave principal que también sea una clave externa?

Segunda pregunta: cuando intento exportar los cambios, dice: Ejecutar script SQL en el servidor

# ERROR: Error 1005: Can''t create table ''dbimmobili.condoni'' (errno: 150)

CREATE  TABLE IF NOT EXISTS `dbimmobili`.`Condoni` (

  `ComuneImmobile` VARCHAR(50) NOT NULL ,
  `ViaImmobile` VARCHAR(50) NOT NULL ,
  `CivicoImmobile` VARCHAR(5) NOT NULL ,
  `InternoImmobile` VARCHAR(3) NOT NULL ,
  `ProtocolloNumero` VARCHAR(15) NULL ,
  `DataRichiestaSanatoria` DATE NULL ,
  `DataSanatoria` DATE NULL ,
  `SullePartiEsclusive` TINYINT(1) NULL ,
  `SullePartiComuni` TINYINT(1) NULL ,
  `OblazioneInEuro` DOUBLE NULL ,
  `TecnicoOblazione` VARCHAR(45) NULL ,
  `TelefonoTecnico` VARCHAR(15) NULL ,
  INDEX `ComuneImmobile` (`ComuneImmobile` ASC) ,
  INDEX `ViaImmobile` (`ViaImmobile` ASC) ,
  INDEX `CivicoImmobile` (`CivicoImmobile` ASC) ,
  INDEX `InternoImmobile` (`InternoImmobile` ASC) ,

  PRIMARY KEY (`ComuneImmobile`, `ViaImmobile`, `CivicoImmobile`, `InternoImmobile`) ,

  CONSTRAINT `ComuneImmobile`
    FOREIGN KEY (`ComuneImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`ComuneImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `ViaImmobile`
    FOREIGN KEY (`ViaImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`ViaImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `CivicoImmobile`
    FOREIGN KEY (`CivicoImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`CivicoImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `InternoImmobile`
    FOREIGN KEY (`InternoImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`InternoImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB

Mostrando el estado del motor:

Error en la restricción de clave externa de la tabla dbimmobili / valutazionimercato:

No se puede encontrar un índice en la tabla a la que se hace referencia en el que las columnas a las que se hace referencia aparecen como las primeras columnas, o el tipo de columnas en la tabla y la tabla a la que se hace referencia no coinciden con la restricción. Tenga en cuenta que el tipo de almacenamiento interno de ENUM y SET cambió en tablas creadas con> = InnoDB-4.1.12, y tales columnas en tablas antiguas no pueden ser referenciadas por tales columnas en nuevas tablas.

Donde estoy haciendo mal?

Answer 1

Primera pregunta: ¿Puedo hacer una clave principal que también sea una clave externa?

Sí. De hecho, para el banco de trabajo MySQL tengo el hábito de usar solo claves principales para claves externas. Realmente reduce los errores aleatorios recibidos, como el error: 150 establecido en la pregunta.

# ERROR: Error 1005: No se puede crear la tabla ''dbimmobili.condoni'' (errno: 150)

Esto tiene algo que ver con los índices, o más específicamente, cómo MySQL workbench interpreta y trabaja con ellos. Realmente se confunde si cambia mucho en el modelo (por ejemplo, claves externas, índices, órdenes col) todo en la misma operación de ingeniería directa, especialmente si ya hay una base de datos en el otro extremo . Por ejemplo, no creo que los índices creados automáticamente se eliminen automáticamente después de eliminar una clave externa.

Esto es lo que hice para arreglar el error que recibió. Tenga en cuenta que parece engorroso, pero en comparación con la cantidad de tiempo que pasé usando otros métodos, no lo es.

1. Haga que todas las claves externas claves principales en la tabla de búsqueda (el 1 en el 1 en muchos).

En mi caso, esto implicaba cambiar id como pk a nombre de usuario en tbl_users, a nombre de usuario Y compañía en tbl_companies, y al nombre de usuario AND company AND contact en tbl_company_contacts. Es una aplicación para que múltiples usuarios ingresen múltiples contactos de la empresa, lo que permite la superposición y la ocultación de los contactos de otros usuarios.

2. Elimine todas las relaciones de diagrama y todos los índices de tabla que no sean claves primarias.

Esto soluciona la mayoría de los problemas de índice que realmente son causados ​​por un banco de trabajo con errores de MySQL.

3. Si hace esto de principio a fin, suelte el esquema en el servidor para que mysql workbench no se confunda con los índices existentes y carezca de ellos en el modelo (el problema se debe al índice y a la relación de clave externa, no al índice solo )

Esto reduce muchas de las decisiones que la base de datos, el servidor y el banco de trabajo Mysql tienen que hacer mucho. Estas decisiones sobre cómo reenviar algo de ingeniería son complicadas e inteligentes, pero imperfectas.

4. Ahora, considero esto de vuelta al punto de partida (generalmente después de diseñar demasiado rápido sin un proceso escalonado). Todavía tengo todas las tablas, pero están limpias en esta etapa. Ahora tú solo:

En primer lugar, reenvíe al ingeniero solo para asegurarse de que las tablas (sin relaciones) funcionen como se esperaba.

Siga la cadena de relaciones hacia abajo a través de las claves principales, comenzando en la parte superior de la tabla (soy mi caso tbl_users to tbl_companies). Después de cada relación, siempre reenviará la ingeniería para asegurarse de que se ejecute , luego guarde el modelo y cierre, luego realice ingeniería inversa del modelo para asegurarse de que sea necesario . Esto le permite aislar rápidamente los problemas a medida que surgen, en mi caso, el índice remanente utilizado por claves externas borradas antiguas (sucedidas 2-3 veces).

Y tadda, allá donde necesitabas estar.

Answer 2

1. Asegúrese de que ambas tablas usen el mismo tipo de motor.
2. Asegúrese de que los campos que está indexando tengan el mismo tipo y longitud.

Answer 3

Aún no tengo la reputación de votar la sugerencia de Steve, pero resolvió mi problema.

En mi caso, recibí este error porque las dos tablas se crearon usando diferentes motores de base de datos, uno fue Innodb y el otro MyISAM.

Puede cambiar el tipo de base de datos usando: ALTER TABLE t ENGINE = MYISAM;

@see http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html

Answer 4

Al crear una restricción de clave externa, MySQL requiere un índice utilizable tanto en la tabla de referencia como en la tabla a la que se hace referencia. El índice en la tabla de referencia se crea automáticamente si no existe uno, pero el de la tabla a la que se hace referencia debe crearse manualmente ( Source ). El tuyo parece estar perdido.

Caso de prueba:

CREATE TABLE tbl_a (
    id int PRIMARY KEY,
    some_other_id int,
    value int
) ENGINE=INNODB;
Query OK, 0 rows affected (0.10 sec)

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
ERROR 1005 (HY000): Can''t create table ''e.tbl_b'' (errno: 150)

Pero si agregamos un índice en some_other_id :

CREATE INDEX ix_some_id ON tbl_a (some_other_id);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

Esto a menudo no es un problema en la mayoría de las situaciones, ya que el campo al que se hace referencia es a menudo la clave principal de la tabla a la que se hace referencia, y la clave principal se indexa automáticamente.

Answer 5

Cuando hay 2 columnas para claves primarias, forman una clave primaria compuesta, por lo tanto, debe asegurarse de que en la tabla a la que se hace referencia también haya 2 columnas del mismo tipo de datos.

Answer 6

En mi caso, el error se debió a que la tabla de referencing es MyISAM donde la tabla de referring era InnoDB .

Converted motor de tabla Converted de MyISAM to InnoDB resuelve el problema para mí.

ALTER TABLE table_name ENGINE=InnoDB;

Answer 7

Estaba recibiendo el mismo error. Descubrí la solución que había creado la clave principal en la tabla principal como BIGINT UNSIGNED y la declaraba como una clave externa en la segunda tabla como BIGINT.

Cuando declare mi clave foránea como BIGINT UNSIGED en la segunda tabla, todo funcionó bien, incluso no fue necesario crear ningún índice.

Así que era un desajuste de tipo de datos entre la clave principal y la clave externa :)

Answer 8

Me doy cuenta de que esta es una publicación anterior, pero ocupa un lugar destacado en Google, así que estoy agregando lo que descubrí para MI problema. Si tiene una mezcla de tipos de tabla (p. Ej. MyISAM e InnoDB), también obtendrá este error. En este caso, InnoDB es el tipo de tabla predeterminado, pero una tabla necesita una búsqueda de texto completo, por lo que se migró a MyISAM. En esta situación, no puede crear una clave externa en la tabla InnoDB que hace referencia a la tabla MyISAM.

Answer 9

MySQL es notoriamente irritable, especialmente en lo que respecta a claves externas y factores desencadenantes. Ahora estoy en el proceso de poner a punto una de esas bases de datos y me encontré con este problema. No es evidente ni intuitivo, así que aquí va:

Además de verificar si las dos columnas que desea referenciar en la relación tienen el mismo tipo de datos, también debe asegurarse de que la columna de la tabla a la que hace referencia sea un índice. Si está utilizando MySQL Workbench, seleccione la pestaña "Índices" al lado de "Columnas" y asegúrese de que la columna a la que hace referencia la clave foránea sea un índice. De lo contrario, cree uno, asígnele un nombre significativo y asígnele el tipo "INDICE".

Una buena práctica es limpiar las tablas involucradas en las relaciones para asegurarse de que los intentos anteriores no crearon índices que no quiere o no necesita.

Espero que haya sido útil, algunos errores de MySQL son enloquecedores de seguir.

Answer 10

No es su caso específico, pero vale la pena señalar que este error puede ocurrir si intenta hacer referencia a algunos campos en una tabla que no son la clave principal completa de esa tabla. Obviamente esto no está permitido.

Answer 11

Para mí, estaba tratando de hacer coincidir un campo indexado normal en una tabla secundaria, con una clave primaria en la tabla primaria, y de forma predeterminada algunas GUI frontend de MySQL como Sequel Pro configuran la clave principal como no firmada, por lo que debes asegurarte que el campo de tabla hijo no está firmado también (a menos que estos campos puedan contener entradas negativas, luego asegúrese de que ambos estén firmados).

Answer 12

Preste atención a los parámetros CHARSET y COLLATE cuando crea una tabla. En términos de problemas FOREIGN KEY algo así:

CREATE TABLE yourTableName (
....
....
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

En mi caso, no pude crear la tabla con referencias FOREIGN KEY. Primero obtuve el Código de error 1005, que prácticamente no dice nada. Luego agregué COLLATE y finalmente el mensaje de error quejándose de CHARSET.

Error Code: 1253. COLLATION ''utf8_unicode_ci'' is not valid for CHARACTER SET ''latin1''

Después de esa corrección mi problema fue resuelto.

Answer 13

Si alguien tiene este error con relaciones FK / PK aparentemente bien formadas y usó la herramienta visual, intente eliminar las columnas fk ofensivas y volver a agregarlas en la herramienta. Continuamente recibía este error hasta que rediseñé las conexiones que aclararon los problemas.

Answer 14

Si su clave es CHAR / VARCHAR o algo de ese tipo, otro posible problema es la intercalación diferente. Compruebe si el juego de caracteres es el mismo.

Answer 15

Tenía exactamente el mismo problema, pero la solución a mi problema era completamente diferente. Tenía, en algún otro lugar de la base de datos, una clave externa con el mismo nombre. Eso causó el error 1005.

Cambiar el nombre de mi clave externa a algo más específico para esa situación resolvió el problema.

Answer 16

Tuve este error y encontré el motivo del error en mi caso. Todavía estoy respondiendo a esta publicación anterior porque tiene una posición bastante alta en Google.

Las variables de ambas de la columna que quería vincular eran enteros, pero una de las entradas tenía marcada ''unsigned''. Simplemente desmarcando que solucionó mi error.

Answer 17

Verifique que las claves externas tengan exactamente el mismo tipo que el campo que tiene en esta tabla. Por ejemplo, ambos deben ser Integer (10) o Varchar (8), incluso el número de caracteres.