Insert or Update on Table Violates Foreign Key Constraint with Postgres

Question

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?

There is no way we can comment on conceptual/design issues.. — P.Salmon
– P.Salmon, Commented Feb 14, 2022 at 11:54
What rows did you insert into LOJA and JOGO before inserting into LOJAJOGOS? — Lennart - Slava Ukraini
– Lennart - Slava Ukraini, Commented Feb 14, 2022 at 12:02

at54321 · Accepted Answer · 2022-02-14 12:05:26Z

3

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.

edited Feb 14, 2022 at 12:05

answered Feb 14, 2022 at 11:57

at54321

12.3k5 gold badges51 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert or Update on Table Violates Foreign Key Constraint with Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related