Failed to add the foreign key constraint. Missing index for constraint * in the referenced table *

system info

SHOW VARIABLES LIKE “%version%”;

when i create table in database with following statements it sucked.

CREATE TABLE flavors (
    created_at DATETIME,
    updated_at DATETIME,
    deleted_at DATETIME,
    deleted INTEGER,
    platform_id VARCHAR(36) NOT NULL,
    flavor_id VARCHAR(36) NOT NULL,
    PRIMARY KEY (platform_id, flavor_id)
);

CREATE TABLE flavor_links (
    created_at DATETIME,
    updated_at DATETIME,
    deleted_at DATETIME,
    deleted INTEGER,
    flavor_id VARCHAR(36) NOT NULL,
    platform_id VARCHAR(36) NOT NULL,
    href TEXT,
    PRIMARY KEY (flavor_id, platform_id),
    FOREIGN KEY(flavor_id, platform_id) REFERENCES flavors (flavor_id, platform_id)
)

with following error logs

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'flavor_links_ibfk_1' in the referenced table 'flavors'

Answer

The ordering of columns is important!

FOREIGN KEY (flavor_id, platform_id) REFERENCES flavors (flavor_id, platform_id)

is not the same as:

FOREIGN KEY (platform_id, flavor_id) REFERENCES flavors (platform_id, flavor_id)