Failed to add the foreign key constraint, MySQL-CodePudding

I have this error on PopSQL :

Error: Failed to add the foreign key constraint. Missing index for constraint 'etape__ibfk_1' in the referenced table 'etapexprojet_' Error Code: ER_FK_NO_INDEX_PARENT

This is my code, i'm only creating tables and it has a problem in the "Etape_" part, i really, don't know why

I'm really, really new to SQL. Like... this is my first time doing this

--Client
CREATE TABLE IF NOT EXISTS jegere.client_ (
    idClient INT PRIMARY KEY,
    nomClient VARCHAR(45) NOT NULL,
    adresse VARCHAR(45) NOT NULL UNIQUE,
    telephone VARCHAR(45) NOT NULL UNIQUE,
    adresseCourriel VARCHAR(45) NOT NULL UNIQUE
    );

--Employés
CREATE TABLE IF NOT EXISTS jegere.Employe_ (
    idEmploye INT PRIMARY KEY,
    nomEmploye VARCHAR(45) NOT NULL,
    adresse VARCHAR(45) NOT NULL UNIQUE,
    telephone VARCHAR(45) NOT NULL UNIQUE,
    adresseCourriel VARCHAR(45) NOT NULL UNIQUE
    );

 --Projet
CREATE TABLE IF NOT EXISTS jegere.Projet_ (
      idProjet INT PRIMARY KEY, 
      idClient INT ,
      nomProjet VARCHAR(45) NOT NULL ,
      dateDebut DATE NOT NULL ,
      dateFin DATE,
      idResponsable INT NOT NULL ,
      FOREIGN KEY (idClient ) REFERENCES jegere.Client_ (idClient ),
      FOREIGN KEY (idResponsable ) REFERENCES jegere.Employe_ (idEmploye )
);

--RessourcesProjet
CREATE TABLE IF NOT EXISTS jegere.RessourcesProjet_ (
    idProjet INT NOT NULL,
    idEmploye INT NOT NULL,
    nbrHeure INT NOT NULL,
    PrixHeure FLOAT NOT NULL,
    PRIMARY KEY(idProjet, idEmploye),
    FOREIGN KEY(idProjet) REFERENCES projet_(idProjet),
    FOREIGN KEY(idEmploye) REFERENCES employe_(idEmploye)
);

--Etape X Projet
CREATE TABLE IF NOT EXISTS jegere.EtapexProjet_ (
    idEtape INT NOT NULL,
    idProjet INT NOT NULL,
    dateDebut DATE NOT NULL ,
    dateFin DATE,
    PRIMARY KEY(idProjet, idEtape),
    FOREIGN KEY(idProjet) REFERENCES projet_(idProjet)
);

--Etapes
CREATE TABLE IF NOT EXISTS jegere.Etape_ (
    idEtape INT,
    nomEtape VARCHAR(45) NOT NULL,
    Livrable VARCHAR(100) NOT NULL,
    PRIMARY KEY(idEtape),
    FOREIGN KEY(idEtape) REFERENCES etapexprojet_(idEtape)
);

CodePudding user response：

The referenced column in a foreign key needs to be indexed, so you need to add an index on the idEtape column in etapexprojet:

--Etape X Projet
CREATE TABLE IF NOT EXISTS jegere.EtapexProjet_ (
    idEtape INT NOT NULL,
    idProjet INT NOT NULL,
    dateDebut DATE NOT NULL ,
    dateFin DATE,
    PRIMARY KEY(idProjet, idEtape),
    INDEX (idEtape),
    FOREIGN KEY(idProjet) REFERENCES projet_(idProjet)
);

Having it as part of the primary key isn't sufficient. A prefix of an index is also an index, but idEtape is a later part of the primary key index, so it's not indexed by itself.

Another solution is to change the order of the primary key:

    PRIMARY KEY(idEtape, idProject),

CodePudding user response：

Your last table etape references only the column idetape, but all referenced columns need an index , primary key or UNIQUE constraint.

so add a KEY to your EtapexProjet_ for the column idetape, and you can run the code

CREATE TABLE IF NOT EXISTS EtapexProjet_ (
    idEtape INT NOT NULL,
    idProjet INT NOT NULL,
    dateDebut DATE NOT NULL ,
    dateFin DATE,
    PRIMARY KEY(idProjet, idEtape),
    KEY(idEtape),
    FOREIGN KEY(idProjet) REFERENCES Projet_(idProjet)
);

see https://dbfiddle.uk/z9QFoH0c