Não estou conseguindo criar chave estrangeira no banco a seguir.

Olá, Ricardo

Como acredito que você já esteja com as tabelas criadas, ficaria mais fácil se compartilhasse como está fazendo pra criar a sua chave estrangeira.

Mas no geral, no código que você está escrevendo falta adicionar a parte de criação de index da chave, que é obrigatório na hora de criar uma. Alguns SGBD já criam automaticamente, mas pelo visto você vai precisar adicionar isso ao seu código.

Olá Caio,

Refiz o banco com algumas chaves estrangeiras e primarias, e continua com a falha , poderia me ajudar com essa falha ? Vou colocar o código abaixo:

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'tbhistorico_ibfk_3' in the referenced table 'tbturma'

USE universidade;

CREATE TABLE TBALUNOS( MAT int , NOME varchar (100), ENDERECO varchar(150), CIDADE varchar (50) , PRIMARY KEY (MAT));

CREATE TABLE TBDISCIPLINAS( COD_DISC varchar (5) , NOME_DISC varchar (50), CARGA_HOR time, PRIMARY KEY (COD_DISC));

CREATE TABLE TBPROFESSORES( COD_PROF int , NOME varchar (100), ENDERECO varchar(150), CIDADE varchar (50), PRIMARY KEY (COD_PROF));

CREATE TABLE TBTURMA( COD_DISC varchar (5), COD_TURMA tinyint, COD_PROF int, ANO date, HORARIO time, PRIMARY KEY (HORARIO), FOREIGN KEY (COD_DISC) REFERENCES TBDISCIPLINAS (COD_DISC), FOREIGN KEY (COD_PROF) REFERENCES TBPROFESSORES (COD_PROF));

CREATE TABLE TBHISTORICO( MAT int, COD_DISC varchar(5), COD_TURMA tinyint, COD_PROF int, ANO date, FREQUENCIA float, NOTA float, PRIMARY KEY (MAT, COD_DISC, COD_TURMA, ANO), FOREIGN KEY (MAT) REFERENCES TBALUNOS (MAT), FOREIGN KEY (COD_DISC) REFERENCES TBTURMA (COD_DISC), FOREIGN KEY (COD_TURMA) REFERENCES TBTURMA (COD_TURMA), FOREIGN KEY (COD_PROF) REFERENCES TBTURMA (COD_PROF), FOREIGN KEY (ANO) REFERENCES TBTURMA (ANO));

Olá, Ricardo

Existem alguns problemas com a criação da sua TBHISTORICO:

1. FOREIGN KEY (COD_DISC) REFERENCES TBTURMA (COD_DISC) - Você não pode criar uma chave estrangeira em uma tabela sem que ela esteja referenciando uma chave primária de outra tabela. No caso, a coluna COD_DISC da tabela TBTURMA não é uma chave primária.
2. FOREIGN KEY (COD_TURMA) REFERENCES TBTURMA (COD_TURMA) - Você não pode criar uma chave estrangeira em uma tabela sem que ela esteja referenciando uma chave primária de outra tabela. No caso, a coluna COD_TURMA da tabela TBTURMA também não é uma chave primária.
3. FOREIGN KEY (COD_PROF) REFERENCES TBTURMA (COD_PROF) - Você não pode criar uma chave estrangeira em uma tabela sem que ela esteja referenciando uma chave primária de outra tabela. No caso, a coluna COD_PROF da tabela TBTURMA também não é uma chave primária.
4. FOREIGN KEY (ANO) REFERENCES TBTURMA (ANO) - Você não pode criar uma chave estrangeira em uma tabela sem que ela esteja referenciando uma chave primária de outra tabela. No caso, a coluna ANO da tabela TBTURMA também não é uma chave primária.

Acredito que a estrutura correta seria:

CREATE TABLE TBHISTORICO( MAT int, COD_DISC varchar(5), COD_TURMA tinyint, COD_PROF int, ANO date, FREQUENCIA float, NOTA float, PRIMARY KEY (MAT, COD_DISC, COD_TURMA, ANO), FOREIGN KEY (MAT) REFERENCES TBALUNOS (MAT), FOREIGN KEY (COD_DISC) REFERENCES TBDISCIPLINAS (COD_DISC), FOREIGN KEY (COD_PROF) REFERENCES TBPROFESSORES (COD_PROF));

Lembrando apenas que exclui a chave estrangeira da coluna de ANO, pois dentro das tabelas que você passou, a coluna ANO não é chave primária de nenhuma delas, ou seja, não existe uma tabela dedicada a trazer detalhes da tabela ANO.

Também exclui a chave estrangeira da coluna COD_TURMA, pois na tabela que mandou, a chave primária da TBTURMAS é a coluna HORÁRIO e não a COD_TURMA, então caso revise e venha a editar isso na tabela, pra adicionar a chave estrangeira depois com a tabela criada, você pode executar o comando abaixo para adicionar a chave estrangeira:

ALTER TABLE TBHISTORICO ADD FOREIGN KEY(COD_TURMA)
REFERENCES TBTURMAS (COD_TURMA)

Oi Caio, entendi e consegui corrigi. Muito obrigado.

Eu tenho uma dúvida em duas questões, poderia me ajudar ?

Estou tentando atender essa questao, exibe a msg de erro abaixo.

select A.MAT as Matricula, A.nome, AVG(H.nota) from alunos A join historico H on H.MAT=A.MAT where H.COD_DISC='POO'

11:55:15 select A.MAT as Matricula, A.nome, AVG(H.nota) from alunos A join historico H on H.MAT=A.MAT where H.COD_DISC='POO' LIMIT 0, 1000 Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'universidade.A.MAT'; this is incompatible with sql_mode=only_full_group_by 0.000 sec

Olá, Ricardo

Funções como AVG, SUM, MAX, MIN são o que chamamos de funções de agregação, ou seja, elas vão pegar todos os valores de uma determinada coluna e agregar (juntar) a partir da lógica de agrupamento que você determinar. Essa lógica de agrupamento é passada através da cláusula GROUP BY, tanto que se olhar na sua mensagem de erro vai notar que é justamente o que ele está reclamando:

In aggregated query without GROUP BY

Como você quer mostrar a média das notas dos alunos por nome e matrícula, depois da cláusula WHERE você precisa adicionar o GROUP BY, assim:

SELECT A.MAT as Matricula, A.nome, AVG(H.nota) 
FROM alunos A 
JOIN historico H ON H.MAT=A.MAT 
WHERE H.COD_DISC='POO'
GROUP BY A.MAT, A.nome