CODEWITHSUNDEEP
mysqlsqldatabasephpmyadmin
Etienne
Etienne

Reputation: 95

SQL - phpmyadmin - Foreign Key errno: 150

I already seen many topics which have the same issue as me but no one had a good answer for my case.

I've made a model and I get a SQL script from this software. The issue is that I have the errno: 150 with a Foreign Key in PhpMyAdmin. I checked, both keys have the same type but the issue is still here.

Can you help me to resolve this issue please ? I had :

#1005 - Can't create table `candidathlon`.`#sql-21ac_3b` 
(errno: 150"Foreign key constraint is incorrectly formed")

With the request :

ALTER TABLE participer_a 
  ADD CONSTRAINT FK_participer_a_DateEvenement FOREIGN KEY (DateEvenement) 
  REFERENCES Evenement(DateEvenement)

This is my tables which have this problem : 

CREATE TABLE Evenement(
    NumEvenement  int (11) Auto_increment  NOT NULL ,
    NomEvenement  Varchar (25) NOT NULL ,
    DateEvenement Date NOT NULL ,
    PRIMARY KEY (NumEvenement ,DateEvenement ) ,
    INDEX (NomEvenement)
)ENGINE=InnoDB;

CREATE TABLE Candidat(
    DateNaissance     Date NOT NULL ,
    NumTelPort        Varchar (25) NOT NULL ,
    NumTelFixe        Varchar (25) NOT NULL ,
    Adresse           Varchar (25) NOT NULL ,
    TrouverEntreprise Bool NOT NULL ,
    InscritCFA        Bool NOT NULL ,
    NumPersonne       Int NOT NULL ,
    NumStatut         Int NOT NULL ,
    NumVille          Int NOT NULL ,
    DateApprenti      Date ,
    NumInstit         Int NOT NULL ,
    PRIMARY KEY (NumPersonne ) ,
    INDEX (TrouverEntreprise ,InscritCFA )
)ENGINE=InnoDB;


CREATE TABLE participer_a(
    ParticipeEvent     Bool NOT NULL ,
    CommentaireEvent   Varchar (100) ,
    SouhaiteParticiper Bool NOT NULL ,
    NumPersonne        Int NOT NULL ,
    NumEvenement       Int NOT NULL ,
    DateEvenement      Date NOT NULL ,
    PRIMARY KEY (NumPersonne ,NumEvenement ,DateEvenement )
)ENGINE=InnoDB;

ALTER TABLE participer_a ADD CONSTRAINT FK_participer_a_NumPersonne FOREIGN KEY (NumPersonne) REFERENCES Personne(NumPersonne);
ALTER TABLE participer_a ADD CONSTRAINT FK_participer_a_NumEvenement FOREIGN KEY (NumEvenement) REFERENCES Evenement(NumEvenement);
ALTER TABLE participer_a ADD CONSTRAINT FK_participer_a_DateEvenement FOREIGN KEY (DateEvenement) REFERENCES Evenement(DateEvenement);

Upvotes: 0

Views: 134

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

What you want to do:

ALTER TABLE participer_a
    ADD CONSTRAINT FK_participer_a_DateEvenement
        FOREIGN KEY (DateEvenement) REFERENCES Evenement(DateEvenement)

The keys available on the table:

PRIMARY KEY (NumEvenement ,DateEvenement ) ,
INDEX (NomEvenement)

Neither of these starts with DateEvenement, which is is needed for a MySQL foreign key constraint. You would need:

INDEX (DateEvenement)

I imagine what you really want is something like this:

CREATE TABLE Evenement(
    NumEvenement  int (11) Auto_increment  NOT NULL ,
    NomEvenement  Varchar (25) NOT NULL ,
    DateEvenement Date NOT NULL ,
    PRIMARY KEY (NumEvenement),
    INDEX (DateEvenement) ,
    UNIQUE (NomEvenement)
);

NumEvenement is unique on every row. An auto_increment is usually the primary key in a table that has such a column. I am guessing that the name of the event is actually unique. The date might be as well, but at a minimum, you need an index on it.

Upvotes: 2

Related Questions