Error 1452 Mysql

Asked

Viewed 272 times

0

I’m starting in mysql, so don’t notice the badly made tables. Finally 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 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');

Starts from error 1452 cannot add or update to Child Row a Foreign key Constraint fails, someone knows what’s wrong with foreign keys?

mysql sql sql-insert

1 answer

1

in its second line of historical Insert, already has an error:

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

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

thus, in the cited passage Asima, it does not correspond to any record in the class table.

I mean, in the class table, you don’t have a record like this:

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

ps. I have already stopped checking on the second line, the others I have not even checked.

I also put in Sqlfiddle: http://sqlfiddle.com/#! 9/ff45e0

Browser other questions tagged mysql sql sql-insert

You are not signed in. Login or sign up in order to post.