About relationship between tables:
I tried to run the following code and SQLite returns me the following error:
table "Games" has more than one primary key
-- -----------------------------------------------------
-- Table 'times'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'times' (
'_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
'nome' VARCHAR(45) NOT NULL,
'sigla' VARCHAR(3) NOT NULL,
'imagem' MEDIUMTEXT NUL);
-- -----------------------------------------------------
-- Table 'Jogos'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'Jogos' (
'_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
'rodada' INT NOT NULL,
'data_hora' VARCHAR(45) NOT NULL,
'local' VARCHAR(100) NOT NULL,
'gols_mandante' VARCHAR(45) NULL,
'gols_visitante' VARCHAR(45) NULL,
'id_mandante' INT NOT NULL,
'id_visitante' INT NOT NULL,
PRIMARY KEY ('_id', 'id_mandante', 'id_visitante'),
CONSTRAINT 'fk_Jogos_times1'
FOREIGN KEY ('id_mandante')
REFERENCES 'times' ('_id')
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT 'fk_Jogos_times2'
FOREIGN KEY ('id_visitante')
REFERENCES 'times' ('_id')
ON DELETE NO ACTION
ON UPDATE NO ACTION)
;
CREATE INDEX 'fk_Jogos_times1_idx' on 'Jogos' ('id_mandante' ASC);
CREATE INDEX 'fk_Jogos_times2_idx' on 'Jogos' ('id_visitante' ASC);
It seems that the error occurs when I try to add the foreign keys from the times
table to the jogos
table.