solucion - MySQL no puede crear restricción de clave externa

foreign key mysql ejemplo

Question

Tengo algunos problemas para crear una clave externa a una tabla existente en una base de datos mysql.

Tengo la tabla exp :

+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| EID | varchar(45) | NO | PRI | NULL | |
| Comment | text | YES | | NULL | |
| Initials | varchar(255) | NO | | NULL | |
| ExpDate | date | NO | | NULL | |
| InsertDate | date | NO | | NULL | |
| inserted_by | int(11) unsigned | YES | MUL | NULL | |
+-------------+------------------+------+-----+---------+-------+

y quiero crear una nueva tabla llamada sample_df referencia a esto, usando lo siguiente:

CREATE TABLE sample_df (
df_id mediumint(5) unsigned AUTO_INCREMENT primary key,
sample_type mediumint(5) unsigned NOT NULL,
df_10 BOOLEAN NOT NULL,
df_100 BOOLEAN NOT NULL,
df_1000 BOOLEAN NOT NULL,
df_above_1000 BOOLEAN NOT NULL,
target INT(11) unsigned NOT NULL,
assay MEDIUMINT(5) unsigned zerofill NOT NULL,
insert_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
inserted_by INT(11) unsigned NOT NULL,
initials varchar(255),
experiment VARCHAR(45),
CONSTRAINT FOREIGN KEY (inserted_by) REFERENCES user (iduser),
CONSTRAINT FOREIGN KEY (target) REFERENCES protein (PID),
CONSTRAINT FOREIGN KEY (sample_type) REFERENCES sample_type (ID),
CONSTRAINT FOREIGN KEY (assay) REFERENCES assays (AID),
CONSTRAINT FOREIGN KEY (experiment) REFERENCES exp (EID)
);

Pero me sale el error:

ERROR 1215 (HY000): Cannot add foreign key constraint

Para obtener más información, lo hice:

SHOW ENGINE INNODB STATUS/G

De lo que obtuve:

FOREIGN KEY (experiment) REFERENCES exp (EID)
):
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.

Para mí, los tipos de columna parecen coincidir, ya que ambos son varchar (45). (También traté de configurar la columna del experiment para que no sea nula, pero esto no lo solucionó) Así que supongo que el problema debe ser que Cannot find an index in the referenced table where the referenced columns appear as the first columns . Pero no estoy muy seguro de lo que esto significa, o cómo verificarlo / corregirlo. ¿Alguien tiene alguna sugerencia? ¿Y qué significan las first columns ?

SOLUCIÓN:

Resolví mi problema. Tuve una clasificación diferente para la tabla exp (utf8_unicode_ci) y luego la predeterminada para la base de datos (utf8_general_ci). Entonces agregando

CHARACTER SET utf8 COLLATE utf8_unicode_ci

al final de la tabla, la consulta de creación solucionó mi problema.

Pero todavía tengo curiosidad sobre lo que significan las first columns . ¿Tiene algo que ver con los índices?

Answer 1

Como se mencionó @Anton, esto podría deberse a los diferentes tipos de datos. En mi caso, tenía la clave primaria BIGINT (20) y traté de configurar la tecla de acceso directo con INT (10)

Answer 2

De acuerdo con http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html

MySQL requiere índices en claves externas y claves referenciadas para que las verificaciones de claves externas puedan ser rápidas y no requieran un escaneo de tabla. En la tabla de referencia, debe haber un índice donde las columnas de clave externa se enumeran como las primeras columnas en el mismo orden.

InnoDB permite que una clave externa haga referencia a cualquier columna de índice o grupo de columnas. Sin embargo, en la tabla a la que se hace referencia, debe haber un índice donde las columnas a las que se hace referencia aparecen como las primeras columnas en el mismo orden.

Por lo tanto, si el índice en la tabla a la que se hace referencia es existente y consta de varias columnas, y la columna deseada no es la primera, se producirá el error.

La causa de nuestro error se debió a la violación de la siguiente regla:

Las columnas correspondientes en la clave externa y la clave a la que se hace referencia deben tener tipos de datos similares. El tamaño y el signo de los tipos enteros deben ser iguales. La longitud de los tipos de cuerda no tiene que ser la misma. Para columnas de cadenas no binárquicas (caracteres), el conjunto de caracteres y la intercalación deben ser iguales.

Answer 3

En mi caso, resultó que la columna referenciada no fue declarada primaria o única.

https://.com/a/18435114/1763217

Answer 4

Este error también puede ocurrir si la tabla de referencias y la tabla actual no tienen el mismo conjunto de caracteres.

Answer 5

Hacer referencia a la misma columna más de una vez en la misma restricción también produce esto Cannot find an index in the referenced table error de Cannot find an index in the referenced table , pero puede ser difícil de detectar en tablas grandes. Dividir las restricciones y funcionará como se esperaba.

Answer 6

La solución sugerida por @ austen-hoogen resolvió mi problema. Pero debido a los bajos puntajes de reputación, no puedo votar ni comentar sobre su solución. En mi caso, la clave principal era un número entero, incremento automático y sin signo. Pero la clave externa (columna de la tabla de referencia) se firmó tipo. Lo cambié a unsigned y puedo crear una relación con éxito.

Answer 7

Simplemente lanzando esto en la mezcla de posibles causas, me encontré con esto cuando la columna de la tabla de referencia tenía el mismo "tipo" pero no tenía la misma firma.

En mi caso, la columna de la tabla a la que se hace referencia era TINYINT UNSIGNED y mi columna de la tabla de referencia tenía TINYINT SIGNED. Alinear ambas columnas resolvió el problema.