POSTGRESQL。对表执行Insert或update操作违反了外键约束
我是Posgresql的新人。我有5个表，我正在尝试向表中插入属性。当我第二次尝试插入时，在'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.
我的方案在这里
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
)回答 2
Stack Overflow用户
发布于 2020-05-21 17:07:14
您需要在难度表中使用id为9的相应条目，以便问题表中的引用id_difficulty列。
例如，如果您的难度表包含：
id | name
----+----------------
1 | easy
2 | reasonable
3 | difficult
4 | very difficult
5 | impossible您只能将问题表中的行的id_difficulty设置为这些id值之一。如果设置为6、12或1到5以外的任何值，则会失败，因为这些值由外键中的值约束。
id_difficulty、id_category和id_creator列不应该使用serial，因此应该删除它们的默认值：
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;Stack Overflow用户
发布于 2020-05-21 19:43:36
Postgres now recommends using generated always as instead of serial.如果这样做，那么类型将更简单地对齐：
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
);这使得正在发生的事情变得更加清晰。外键引用的数据类型需要与主键的数据类型匹配。Postgres知道serial实际上就是int。但是使用generated always，很明显它们是相同的。
此外，generated always as与标准SQL更加一致。
https://stackoverflow.com/questions/61930906
复制相似问题
腾讯云开发者
