Error 1452 Mysql

0

I'm starting mysql, so do not look at bad tables. Anyway I have the following tables:

    create table Alunos (
MAT int,
nome varchar (45),
endereco varchar (45),
cidade varchar (45),
constraint primary key (MAT)
);

create table Disciplinas (
COD_DISC int,
nome_disc varchar (45) not null,
carga_hor int,
primary key (COD_DISC)
);

create table Professores (
COD_PROF int,
nome varchar (45) not null,
endereco varchar (45),
cidade varchar (45),
primary key (COD_PROF)
);

create table Turma(
COD_DISC int,
COD_TURMA int,
COD_PROF int,
disci varchar (5),
ano int,
horario varchar (45),
primary key (COD_TURMA,COD_DISC,COD_PROF,ano),
constraint FK_Disc foreign key (COD_DISC) references Disciplinas (COD_DISC),
constraint FK_Prof foreign key (COD_PROF) references Professores (COD_PROF)
);

create table if not exists Historico (
MAT int,
COD_DISC int,
COD_TURMA int,
COD_PROF int,
ano int,
frequencia int,
nota int,
primary key (MAT,COD_DISC,COD_TURMA,COD_PROF,ano),
constraint FK_histalun foreign key (MAT) references Alunos (MAT),
constraint FK_histfisc foreign key (COD_DISC,COD_TURMA,COD_PROF,ano) references Turma (COD_Disc,COD_TURMA,COD_PROF,ano)
);

And I added the following data:

insert into Alunos values 
('2015010101', 'JOSE DE ALENCAR', 'RUA DAS ALMAS', 'NATAL'),
('2015010102', 'JOÃO JOSÉ', 'AVENIDA RUY CARNEIRO', 'JOÃO PESSOA'),
('2015010103', 'MARIA JOAQUINA', 'RUA CARROSSEL', 'RECIFE'),
('2015010104', 'MARIA DAS DORES', 'RUA DAS LADEIRAS', 'FORTALEZA'),
('2015010105', 'JOSUÉ CLAUDINO DOS SANTOS', 'CENTRO', 'NATAL'),
('2015010106', 'JOSUÉLISSON CLAUDINO DOS SANTOS', 'CENTRO', 'NATAL');



insert into Disciplinas values
(1, 'BANCO DE DADOS', '100'),
(2, 'PROGRAMAÇÃO COM ACESSO A BANCO DE DADOS', '100'),
(3, 'AUTORIA WEB', '50'),
(4, 'ENGENHARIA DE SOFTWARE', '80')
;

insert into Professores values 
('212131', 'NICKERSON FERREIRA', 'RUA MANAÍRA', 'JOÃO PESSOA'),
('122135', 'ADORILSON BEZERRA', 'AVENIDA SALGADO FILHO', 'NATAL'),
('192011', 'DIEGO OLIVEIRA', 'AVENIDA ROBERTO FREIRE', 'NATAL')

;


insert into Turma values
(1, '1', '212131', 'BD', '2015', '11H-12H'),
(1, '2', '212131', 'BD', '2015', '13H-14H'),
(2, '1', '192011', 'POO', '2015', '08H-09H'),
(3, '1', 192011, 'WEB', '2015', '07H-08H'),
(4, '1', 122135, 'ENG', '2015', '10H-11H');

But when I try to put the data in the historical table:

insert into Historico values 
('2015010101', '1', '1', '212131', '2015', '100', '10'),
('2015010102', '2', '2', '122135', '2015', '75', '7'),
('2015010103', '3', '2', '122135', '2016', '45', '2'),
('2015010104', '4', '1', '192011', '2016', '75', '4'),
('2015010105', '4', '1', '212131', '2014', '60', '3'),
('2015010101', '4', '1', '212131', '2015', '100', '9'),
('2015010102', '1', '2', '122135', '2015', '75', '7'),
('2015010103', '2', '3', '122135', '2016', '45', '2'),
('2015010104', '3', '4', '192011', '2016', '75', '6'),
('2015010105', '4', '5', '212131', '2014', '60', '3'),
('2015010101', '4', '1', '212131', '2015', '100', '10'),
('2015010102', '4', '2', '122135', '2015', '75', '7'),
('2015010103', '1', '3', '122135', '2016', '45', '2'),
('2015010104', '2', '4', '192011', '2016', '75', '6'),
('2015010105', '3', '5', '212131', '2014', '60', '3'),
('2015010101', '3', '1', '212131', '2015', '100', '10'),
('2015010102', '4', '2', '122135', '2015', '75', '7'),
('2015010103', '4', '3', '122135', '2016', '45', '1'),
('2015010104', '1', '4', '192011', '2016', '75', '4'),
('2015010105', '2', '5', '212131', '2014', '60', '3'),
('2015010101', '2', '1', '212131', '2015', '100', '10'),
('2015010102', '3', '2', '122135', '2015', '75', '7'),
('2015010103', '4', '3', '122135', '2016', '45', '2'),
('2015010104', '4', '4', '192011', '2016', '75', '4'),
('2015010105', '1', '5', '212131', '2014', '60', '3');

It starts from the error 1452 can not add or update the child row the foreign key constraint fails, does anyone know what is wrong with the foreign keys?

    
asked by anonymous 13.05.2017 / 01:32

1 answer

0

In your second line of insert history, you already have an error:

('2015010102', '2', '2', '122135', '2015', '75', '7'),

The history table has a foreign key where, COD_DISC,COD_TURMA,COD_PROF,ano must be in the class table.

So, in the above asimated excerpt, it does not correspond to any record in the class table.

That is, in the class table, you do not have a record like this:

(2, '2', 122135, 'ENG', '2015', '10H-11H');

ps. I already stopped the verification in the second line, the others I did not verify.

I also put it in SQLFiddle: link

    
07.07.2017 / 16:16