I have a situation that I need to ask a question. I have a table as below:
CREATE TABLE confrontos(
id_confrontos INT AUTO_INCREMENT NOT NULL,
dt_confronto DATE NOT NULL,
id_estadios INT NOT NULL,
***id_clubes*** INT NOT NULL,
***id_clubes*** INT NOT NULL,
id_treinadores INT NOT NULL,
escore1 INT NOT NULL,
escore2 INT NOT NULL,
CONSTRAINT PK_confrontos PRIMARY KEY(id_confrontos),
CONSTRAINT FK_confrontos_estadios foreign key (id_estadios)
REFERENCES estadios (id_estadios),
CONSTRAINT FK_confrontos_clubes foreign key (id_clubes)
REFERENCES clubes (id_clubes),
CONSTRAINT FK_confrontos_treinadores foreign key (id_treinadores)
REFERENCES treinadores (id_treinadores)
);
I've already seen that MySql does not allow you to create this table for duplicity in the id_clubes
field. What would be the best way to create this table?