Error 1215 MySQL (Error Code: 1215 Cannot add foreign key constraint)

Question

good morning. I've a problem with a database I'm designing. I have 5 tables and one is many to many relation. It table has multiple foreign key from the others tables. I create the database with MySQL Worckbecnh and when it's creating the table "Partido" it gives me an error 1215 with the foreign keys. I was looking for attributes with differents types, primary keys that doesn't defined but I think that I don't find nothing wrong in my code I put here the code for yoy can help me. Thank you.

CREATE TABLE PABELLON (
    Codigo_pabellon INT NOT NULL,
    Nombre VARCHAR(30),
    Codigo_localidad INT(3),
    PRIMARY KEY (Codigo_pabellon),
    FOREIGN KEY (Codigo_localidad)
        REFERENCES LOCALIDAD (Codigo_localidad)
);

CREATE TABLE JORNADA (
    Codigo_jornada INT NOT NULL AUTO_INCREMENT,
    Numero INT(2),
    Codigo_Competicion INT NOT NULL,
    PRIMARY KEY (Codigo_jornada)
);
CREATE TABLE EQUIPO (
    NIF VARCHAR(9) NOT NULL,
    Nombre VARCHAR(30),
    Direccion VARCHAR(20),
    Telefono VARCHAR(20),
    PRIMARY KEY (NIF)
);
CREATE TABLE EQUIPO_ARBITRAL (
    DNI VARCHAR(9),
    Licencia INT(3),
    Nombre VARCHAR(20),
    Apellidos VARCHAR(20),
    Email VARCHAR(20),
    Telefono INT(9),
    Cuenta INT(20),
    Cod_localidad INT(3),
    PRIMARY KEY (DNI)
);
CREATE TABLE PARTIDO (
    Codigo_Partido INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    EquipoA VARCHAR(9) NOT NULL,
    EquipoB VARCHAR(9) NOT NULL,
    Fecha DATE,
    Hora TIME,
    Codigo_Pabellon INT NOT NULL,
    Codigo_jornada INT,
    ArbPrin VARCHAR(9),
    ArbAux VARCHAR(9),
    Anotador VARCHAR(9),
    Crono VARCHAR(9),
    Op24 VARCHAR(9),
    FOREIGN KEY (EquipoA , EquipoB)
        REFERENCES EQUIPO (NIF , NIF),
    FOREIGN KEY (ArbPrin , ArbAux , Anotador , Crono , Op24)
        REFERENCES EQUIPO_ARBITRAL (DNI , DNI , DNI , DNI , DNI),
    FOREIGN KEY (Codigo_Pabellon)
        REFERENCES PABELLON (Codigo_Pabellon),
    FOREIGN KEY (Codigo_jornada)
        REFERENCES JORNADA (Codigo_Jornada)
);

Please run show engine innodb status to find out the actual error message. — Álvaro González
– Álvaro González, Commented Mar 6, 2014 at 10:17
LATEST FOREIGN KEY ERROR ------------------------ 2014-03-06 11:25:45 1534 Error in foreign key constraint of table gestion/partido: 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. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. — temerariomalaga
– temerariomalaga, Commented Mar 6, 2014 at 10:29

Ulrich Thomas Gabor · Accepted Answer · 2014-03-06 11:49:00Z

1

Actually I do not understand what your want to achieve by the multi column FKs?

Why do you add an FK like

FOREIGN KEY (EquipoA , EquipoB)
    REFERENCES EQUIPO (NIF , NIF),

over two columns? These columns are independent from each other, besides they reference the same table. Therefore you should define two FKs like

FOREIGN KEY (EquipoA)
    REFERENCES EQUIPO (NIF),
FOREIGN KEY (EquipoB)
    REFERENCES EQUIPO (NIF)

You most likely want to change this accordingly in all FK definitions, which reference one column in the referenced table multiple times.

edited Mar 6, 2014 at 11:49

answered Mar 6, 2014 at 11:17

Ulrich Thomas Gabor

6,6844 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ulrich Thomas Gabor Over a year ago

Sorry, changed my answer accordingly.

temerariomalaga Over a year ago

With the last you say now I can create that table. Thank you very much.

Collectives™ on Stack Overflow

Error 1215 MySQL (Error Code: 1215 Cannot add foreign key constraint)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related