Add multiple FK in one Mysql table

Question

I'm having trouble to add two FK in my table "UserSong". I receive the error "Error Code: 1215. Cannot add foreign key constraint"

This is my code:

DROP database if exists SmartPiano;
CREATE database SmartPiano;
USE SmartPiano;
DROP TABLE IF exists User;
CREATE table User(
    username VARCHAR(255),
    password VARCHAR(255),
    email VARCHAR(255),
    PRIMARY KEY (username,email)
);
DROP TABLE IF exists Song;
CREATE TABLE Song(
    id INT,
    name VARCHAR(255),
    public BOOLEAN,
    path VARCHAR(255),
    reproduction INT,
    PRIMARY KEY (id)
);
DROP TABLE IF exists UserSong;
CREATE TABLE UserSong(
    usernameUser VARCHAR(255),
    emailUser VARCHAR(255),
    id INT,
    PRIMARY KEY (usernameUser,emailUser,id), 
    FOREIGN KEY (usernameUser) REFERENCES User(username),
    FOREIGN KEY (emailUser) REFERENCES User(email)

);

You may like this checklist that I contributed to: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Apr 06 '20 at 20:47

score 0 · Accepted Answer · answered Apr 06 '20 at 18:40

0

It is missing an INDEX on email for your foreign key

So use

CREATE table User(
    username VARCHAR(255),
    password VARCHAR(255),
    email VARCHAR(255),
    INDEX  (email),
    PRIMARY KEY (username,email)
);

answered Apr 06 '20 at 18:40

nbk

45,398
8
30
47

Add multiple FK in one Mysql table

1 Answers1