mysql error with foreign key

3

I'm creating a modeling but I'm stumbling on an error when I create foreign keys. MYSQL gives a "Can not add foreign key constraint" error. follow 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)

);
    
asked by anonymous 04.09.2016 / 01:37

1 answer

0

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

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

Exchange:

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)
);

By:

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) 
);
    
04.09.2016 / 01:42