CODEWITHSUNDEEP
sqlpostgresql
Rafael Furlanetto Casamaximo
Rafael Furlanetto Casamaximo

Reputation: 1

Insert or Update on Table Violates Foreign Key Constraint with Postgres

i'm working on my database assignement and i'm getting an error when I try to insert a row in my database (Postgres)

    CREATE TABLE IntegracaoPrecos.Loja(
    id SERIAL,
    nome CHAR(60) NOT NULL,
    CONSTRAINT id_loja PRIMARY KEY(id)
);


CREATE TABLE IntegracaoPrecos.Empresa(
    id SERIAL,
    descricao_curta CHAR(60),
    numero_jogos INT,
    website CHAR(60),
    CONSTRAINT id_empresa PRIMARY KEY(id)
);

CREATE TABLE IntegracaoPrecos.Jogo(
    id SERIAL,
    nome CHAR(60) NOT NULL,
    genero CHAR(60),
    linguagens_suportadas CHAR(60),
    suporte_a_controle BOOLEAN,
    nome_empresa CHAR(60),
    gratuito BOOLEAN,
    idade_requerida INT,
    descricao_curta CHAR(60),
    descricao_longa CHAR(500),
    id_empresa INT,
    CONSTRAINT id_jogo PRIMARY KEY(id),
    CONSTRAINT fk_nome_empresa FOREIGN KEY(id_empresa)
        REFERENCES IntegracaoPrecos.Empresa(id)
);


CREATE TABLE IntegracaoPrecos.LojaJogos(
    id_loja INT,
    id_jogo INT,
    preco_jogo NUMERIC(6, 2),
    loja_crawl CHAR(10),
    data_crawl DATE,
    CONSTRAINT pk_loja_jogos PRIMARY KEY(id_loja, id_jogo, data_crawl),
    CONSTRAINT fk_id_loja FOREIGN KEY(id_loja)
        REFERENCES IntegracaoPrecos.Loja(id),
    CONSTRAINT fk_id_jogo FOREIGN KEY(id_jogo)
        REFERENCES IntegracaoPrecos.Jogo(id)
);

The row I'm trying to insert is:

INSERT INTO lojajogos (id_loja, id_jogo, preco_jogo, loja_crawl)
VALUES (1, 2, 30, 'Steam');

Are there conceptual errors? How can I insert this row correctly?

Upvotes: 0

Views: 11479

Answers (1)

at54321
at54321

Reputation: 11866

In your LojaJogos table you have two foreign key constraints. That means, at the time you try to insert values 1 for id_loja and 2 for id_jogo, you must already have records with such IDs in the corresponding tables.

That's what foreign key constraints are really all about - they protect you from inserting an invalid ID in the referencing table.

Upvotes: 3

Related Questions