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 )
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.
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;