Sqlite relationship between tables

1

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.

    
asked by anonymous 18.11.2014 / 04:48

1 answer

5

The problem is that you are setting two PRIMARY KEY in your table.

And can only have ONE 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

Delete PRIMARY KEY from _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'),
    
18.11.2014 / 18:53