CODEWITHSUNDEEP
sqlpostgresql
FD3
FD3

Reputation: 1956

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
)

Upvotes: 1

Views: 22814

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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.

Upvotes: 1

Thom Brown
Thom Brown

Reputation: 2039

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;

Upvotes: 3

Related Questions