Sqlite relationship between tables

Question

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 arises when I try to add foreign keys from the table times on the table jogos.

Answer 1

The problem is you’re defining two PRIMARY KEY on your table.

And it can only have A PRIMARY KEY per table.

-- -----------------------------------------------------
-- Table `Jogos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Jogos` (
  `_id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,       -- UMA
  `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`),     -- DUAS

Erase the PRIMARY KEY of _id and it will work:

-- -----------------------------------------------------
-- Table `Jogos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Jogos` (
  `_id` INTEGER NOT NULL 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`),