Skip to content
Advertisement

POSTGRESQL. Insert or update on table violates foreign key constraint

I am new in Posgresql. I have 5 tables and I am trying to INSERT properties to tables. When I tried to Insert 2nd time, I have this error in ‘pgadmin’.

ERROR: insert or update on table "question" violates foreign key constraint "question_id_difficulty_fkey" DETAIL: Key (id_difficulty)=(9) is not present in table "difficulty". SQL state: 23503.

my schema is here

  id SERIAL PRIMARY KEY,
  name varchar
);

CREATE TABLE question (
  id SERIAL PRIMARY KEY,
  text varchar,
  correct_answer varchar,
  incorrect_answer1 varchar,
  incorrect_answer2 varchar,
  incorrect_answer3 varchar,
  id_difficulty SERIAL REFERENCES difficulty(id),
  id_category SERIAL REFERENCES category (id),
  id_creator SERIAL REFERENCES game (id)
);

CREATE TABLE difficulty (
  id SERIAL PRIMARY KEY,
  name varchar
);

CREATE TABLE category (
  id SERIAL PRIAMRY KEY,
  name varchar
);

CREATE TABLE user (
  id SERIAL PRIMARY KEY,
  name varchar
)


Advertisement

Answer

You would need a corresponding entry in the difficulty table with an id of 9, so that a referencing id_difficulty column in the question table.

For example, if your difficulty table contained:

 id |      name      
----+----------------
  1 | easy
  2 | reasonable
  3 | difficult
  4 | very difficult
  5 | impossible

You could only set id_difficulty for rows in the question table to one of those id values. If you set 6, or 12 or anything other than 1 to 5, it would fail because the values are constrained by the values in the foreign key.

The id_difficulty, id_category and id_creator columns shouldn’t be using serial, so these should have their defaults dropped:

ALTER TABLE question ALTER COLUMN id_difficulty DROP DEFAULT;
ALTER TABLE question ALTER COLUMN id_category DROP DEFAULT;
ALTER TABLE question ALTER COLUMN id_creator DROP DEFAULT;
4 People found this is helpful
Advertisement