mysql error with foreign key

Question

I am creating a template but I am running into an error when I am going to create foreign MYSQL keys from a "Cannot add Foreign key Constraint" error. follows my sql.

use fatec;


create table aluno(
 ra int not null primary key,
 nome varchar(255),
 cidade varchar(255),
 endereco varchar(255)
);


create table diciplinas(
 id int not null  primary key auto_increment,
 nome varchar(255),
 carga_hor int
);


create table professores (
 id int not null primary key  auto_increment,
 nome varchar(255),
 cidade varchar(255),
 endereco varchar(255)

);



create table turmas(
 id int not null,
 id_diciplina int not null, 
 id_prof int not null,
 ano int,
 horario varchar(5),
 primary key(id)
 CONSTRAINT FK_diciplinas foreign key(id_diciplina) references diciplinas(id),
 CONSTRAINT FK_professor foreign key(id_prof) references professor (id)

);

Answer 1

Problem with table names right. I know it’s confusing. Have table aluno, and the table professores creates tremendous confusion in the singular and plural.

A hint: plural everything: professores, turmas and alunos, or everything puts everything in the singular.

Barter:

create table turmas(
    id int not null,
    id_diciplina int not null, 
    id_prof int not null,
    ano int,
    horario varchar(5),
    primary key(id)
    CONSTRAINT FK_diciplinas foreign key(id_diciplina) references diciplinas(id),
    CONSTRAINT FK_professor foreign key(id_prof) references professor (id)
);

For:

create table turmas(
    id int not null primary key,
    ano int,
    id_diciplina int not null,
    id_prof int not null,
    id int not null,
    horario varchar(5),
    foreign key(id_diciplina) references diciplinas(id),
    foreign key(id_prof) references professores(id) 
);