MySQL Error 1215 creating foreign key

Question

My question is about MySQL, I keep getting an error (Error 1215: Cannot add Foreign key Constraint) while trying to forward engineer a schema to a db server, I've got two parent tables:

CREATE TABLE IF NOT EXISTS alunos (
    idAluno INT NOT NULL AUTO_INCREMENT,
    NomeAluno VARCHAR(100) NOT NULL,
    nifAluno VARCHAR(15) NOT NULL,
    moradaAluno VARCHAR(255) NOT NULL,
    telefoneAluno VARCHAR(9) NOT NULL,
    emailAluno VARCHAR(255) NOT NULL DEFAULT "Nao fornecido",
    PRIMARY KEY(idAluno, nifAluno)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS cursos (
    idCurso INT NOT NULL AUTO_INCREMENT,
    nomeCurso VARCHAR(50) NOT NULL,
    horas INT NOT NULL,
    PRIMARY KEY(idCurso, nomeCurso)
) ENGINE=INNODB;

And this is my child table:

CREATE TABLE IF NOT EXISTS inscritos (
    id INT NOT NULL AUTO_INCREMENT,
    Nome VARCHAR(100) NOT NULL,
    Morada VARCHAR(255) NOT NULL,
    Naturalidade VARCHAR(45) NOT NULL,
    NIF VARCHAR(15) NOT NULL,
    email VARCHAR(255) NOT NULL DEFAULT "Nao fornecido",
    Telefone VARCHAR(9) NOT NULL,
    Curso VARCHAR(50) NOT NULL,
    Horas INT NOT NULL,
    Inicio DATE NOT NULL,
    Validade DATE NOT NULL,
    Atividade VARCHAR(45) NOT NULL,

    PRIMARY KEY(id),
    INDEX(NIF),
    INDEX(Curso),

    FOREIGN KEY(NIF)
        REFERENCES alunos(nifAluno)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY(Curso)
        REFERENCES cursos(nomeCurso)
        ON UPDATE RESTRICT ON DELETE RESTRICT

) ENGINE=INNODB;

I've looked through the code over and over and I can't seem to find the error when assigning the foreign keys.

Thanks in advance.

I contributed to a checklist for foreign key errors here: https://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 Check it out and see if any of these apply in your case. I think this is because your nifAluno is not the leftmost column in the primary key of the alunos tables. — Bill Karwin, Jan 05 '18 at 03:06
Thanks Bill, already checked it out but it didn't work as it should have, I think I'm following all the prerequisites in your list but still the error persists — Single, Jan 05 '18 at 03:43

score 0 · Accepted Answer · answered Jan 05 '18 at 03:08

0

Because, NIF and Curso aren't primary/unique key in inscritos table. Creating index doesn't mean you are creating key on same column. So, just for your information. The referenced columns in the Parent table must be the left-most columns of a key. Best if the key is PRIMARY KEY or UNIQUE KEY.

As @Bill commented, he has an answer where he has prepared a checklist, you may refer to make sure, you won't get any other error.

answered Jan 05 '18 at 03:08

Ravi

30,829
42
119
173

@Single like what ? – Ravi Jan 05 '18 at 03:23
All I need to do is putting the parent keys before the id's like 'nomeCurso' before 'idCurso', so they can be the left-most columns in the tables. – Single Jan 05 '18 at 03:28
@Single That's for developer convenience. Putting columns in left-most doesn't mean, they get key by default. The key-point is you need to assign some key to those column. – Ravi Jan 05 '18 at 03:31
yes but I'm assigning them as foreign keys in the child table inscritos, how can those columns be both primary and foreign keys? Do they really need to be primary keys? – Single Jan 05 '18 at 03:35
Besides both referenced columns are primary keys in the parent tables – Single Jan 05 '18 at 03:39
@Single You are really getting confused with primary key and foreign key. Also, as I mentioned, it can be unique key, you are not bound to create primary for each column. – Ravi Jan 05 '18 at 04:08

MySQL Error 1215 creating foreign key

1 Answers1