0
Good evening, you guys, I am trying to create a table with two foreign keys, but is giving the error of Cannot add Foreign key Constraint, I do not understand why, follows below the SQL statement.
follows the table Matricula
+--------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------+------+-----+---------+-------+
| aluno_codMatricula | int(11) | NO | PRI | NULL | |
| turma_codTurma | int(11) | NO | PRI | NULL | |
+--------------------+---------+------+-----+---------+-------+
follows the table creation statement with Foreign Keys
CREATE TABLE Chamada(codChamada INT NOT NULL AUTO_INCREMENT, data DATE NOT NULL, presente BOOL NOT NULL, matricula_aluno_codMatricula INT NOT NULL, matricula_turma_codTurma INT NOT NULL, PRIMARY KEY(codChamada), FOREIGN KEY (matricula_aluno_codMatricula) REFERENCES Matricula(aluno_codMatricula), FOREIGN KEY (matricula_turma_codTurma) REFERENCES Matricula(turma_codTurma)) Engine=Innodb;
however error occurs: ERROR 1215 (HY000): Cannot add Foreign key Constraint
So you posted the primary key of the Registration table is a composite key (aluno_codMatricula, turma_codTurma) and not just the aluno_codMatricula field. Shouldn’t the primary key of the Matricula table be just aluno_codMatricula? Or student enrollment may vary from class to class in your data model?
– anonimo
@anonimo I think q read wrong, is a field only every key, is not composed, the first
matricula_aluno_codMatricula
and the secondmatricula_turma_codTurma
, but it is strange two Foreign Keys to the same table, will this be right?– Ricardo Pontual
The two fields contain PRI in the Key column. If it is not a composite key then the software you used to display is totally counterintuitive.
– anonimo
is a table generated when you have relation N to N, I need to know in the call who is the student by the enrollment code and which class he belongs to.
– Juliano Blank