POSTGRESQL. Insert or update on table violates foreign key constraint

Question

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
)

Thom Brown · Accepted Answer · 2020-05-21 09:17:52Z

4

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;

edited May 21, 2020 at 9:17

answered May 21, 2020 at 9:07

Thom Brown

2,0698 silver badges14 bronze badges

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

6 Comments

FD3 Over a year ago

thanks for helping. this number is incrementing after every attemp to INSERT

Thom Brown Over a year ago

I think those columns have been incorrectly set to serial. That means that if the value isn't explicitely specified when inserting a row, it will pick the next value from its sequence. For columns that reference a column on another table, that shouldn't be the case. Those should just be int columns without a sequence. You can disable the sequences, and I'll update my answer with the DDL for that.

FD3 Over a year ago

I solved it another way setting all foreign keys from all tables in INSERT definition. But I do think that it is not a pretty solution

Thom Brown Over a year ago

I would still suggest you drop the defaults on those columns as the sequence doesn't serve a purpose.

FD3 Over a year ago

yes, but difficulty level I must pass every time I guess.

|

Gordon Linoff · Accepted Answer · 2020-05-21 11:43:35Z

Postgres now recommends using generated always as instead of serial. If you do this, then the types will align much more simply:

CREATE TABLE question (
  id int generated always as identity PRIMARY KEY,
  text varchar,
  correct_answer varchar,
  incorrect_answer1 varchar,
  incorrect_answer2 varchar,
  incorrect_answer3 varchar,
  id_difficulty int REFERENCES difficulty(id),
  id_category int REFERENCES category (id),
  id_creator int REFERENCES game (id)
);

CREATE TABLE difficulty (
  id int generated always as identity PRIMARY KEY,
  name varchar
);

This makes what is happening much clearer. The data type for a foreign key reference needs to match the data type of the primary key. Postgres knows that serial is really int. But using generated always, it is obvious that they are the same.

In addition, generated always as is more consistent with standard SQL.

Collectives™ on Stack Overflow

POSTGRESQL. Insert or update on table violates foreign key constraint

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related