MySQL can't apply foreign key constraint

Question

I'm trying to make a database on phpmyadmin, but I have few problems to link my tables. Here are two of them.

First:

CREATE TABLE IF NOT EXISTS bati(
    code_bati CHAR(50) NOT NULL,
    code_parcelle CHAR(50) NOT NULL,
    surface_bati DOUBLE(15,2),
    ID_proprietaire CHAR(50),
    largeur DOUBLE(15,2),
    superficie DOUBLE(15,2),
    orientation CHAR(2),
    vocation VARCHAR(50),
    globalid VARCHAR(50),
    date_save DATE NOT NULL,
    date_last_mod DATE NOT NULL,
    ID_interne CHAR(50) NOT NULL,
    PRIMARY KEY(code_bati, code_parcelle)
)ENGINE = INNODB;

Second:

CREATE TABLE IF NOT EXISTS parcelle(
    fid_edi_parc INT UNSIGNED,
    code_lot CHAR(50),
    ID_parc CHAR(50),
    num SMALLINT(4),
    code_com CHAR(50),
    code_sec CHAR(50),
    code_sub_sec CHAR(50),
    figplan VARCHAR(50),
    code_apart VARCHAR(50),
    code_modif VARCHAR(50),
    code CHAR(50),
    code_parcelle CHAR(50),
    fid_perimetre_ppr DOUBLE(15,2),
    globalID CHAR(50),
    date_save DATE NOT NULL,
    date_last_mod DATE NOT NULL,
    ID_interne CHAR(50) NOT NULL,
    PRIMARY KEY(code, code_parcelle)
)ENGINE = INNODB;

Then I excecute this command to link them:

    ALTER TABLE bati
        ADD CONSTRAINT bati__code_parcelle__fkey 
        FOREIGN KEY (code_parcelle) 
        REFERENCES parcelle(code_parcelle);

And I get this error:

#1215 - Cannot add foreign key constraint

I look for solutions on the internet (including this one MySQL Cannot Add Foreign Key Constraint), but I can not solve my problem, can you help me / explain why this error.

(sorry for my english, I'm french)

A foreign key relationship really only makes sense if you are referencing a full primary or unique key. — Gordon Linoff, Mar 03 '19 at 03:13

score 0 · Accepted Answer · answered Mar 03 '19 at 01:51

You need an index on the column being referenced, or at least it must appear in first position in a multi-column index.

From the MySQL foreign keys documentation:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.

I think that the simplest solution here would be to change the order of the columns in the primary key of table parcelle, ie change:

PRIMARY KEY(code, code_parcelle)

To:

PRIMARY KEY(code_parcelle, code)

With this change, code_parcelle now appears in the first position in the composite index, which makes it possible to reference it as a foreign key.

Demo on DB Fiddle.

NB: it would probably be a good idea to create a separate table to store the code_parcelle, and then refer to it from all other tables.

MySQL can't apply foreign key constraint

1 Answers1