本文介绍了PostgreSQL中的外键匹配的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 只要好奇，如果我有这张表： CREATE TABLEpost（idSERIAL ，revisionINTEGER NOT NULL DEFAULT 0，summaryCHARACTER VARYING NOT NULL，descriptionTEXT NOT NULL，user_idINTEGER NOT NULL 参考user（id）MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT，post_type_idINTEGER NOT NULL REFERENCESpost_type（id） （）， PRIMARY KEY（id，revision）$ b $ UPDATE CASCADE $ b）; 来存储帖子，而这张表： post_idINTEGER NOT NULL，assembly_seat_idINTEGER NOT NULL REFERENCESassembly_seat（id ）MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT， PRIMARY KEY（post_id））; 并且我希望我的 post_id 字段指向到 post（id），我该怎么做？我已经尝试了以下词组： $ $ p $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ID）MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT， 但是我得到这个错误： $ b 错误：没有唯一的约束匹配给定的关键字的引用表后 $ b 在这种情况下， post_state（asembly_seat_id）的值不会改变。 $ b 解决方案外键约束可以跨多列。您可以将修订列添加到表 post_state 。 CREATE TEMP TABLE post（ post_id serial NOT NULL ，revision integer NOT NULL DEFAULT 0 ，summary text NOT NULL ，描述文本NOT NULL ，user_id整数NOT NULL REFERENCES用户（id）MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT ，post_type_id integer NOT NULL REFERENCES post_type（id）MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT ，ctime timestamptz DEFAULT NOW（），PRIMARY KEY（post_id，revision））; CREATE TEMP TABLE post_state（ post_id integer NOT NULL ，revision integer NOT NULL ，assembly_seat_id integer NOT NULL REFERENCES assembly_seat（id）MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT ，PRIMARY KEY（post_id，revision） ，FOREIGN KEY（post_id，revision）参考文献（post_id，revision） ）; 阅读外键限制。 我使用 post_id 作为表 post 的主键列。不要使用 id 作为列名。如果你加入一堆表格，你最终会得到一堆所有名字 id 的列。令人遗憾的是，一些半智能的ORM习惯于这样做。另外，可能更好的设计使独一无二 post_id 在表格后中添加一个表格 post_revision 与 post 之间有1：1关系。 Just curious, if I have this table:CREATE TABLE "post" ( "id" SERIAL, "revision" INTEGER NOT NULL DEFAULT 0, "summary" CHARACTER VARYING NOT NULL, "description" TEXT NOT NULL, "user_id" INTEGER NOT NULL REFERENCES "user" ("id") MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT, "post_type_id" INTEGER NOT NULL REFERENCES "post_type" ("id") MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT, "ctime" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), PRIMARY KEY("id", "revision"));to store posts, and this table:CREATE TABLE "post_state" ( "post_id" INTEGER NOT NULL, "assembly_seat_id" INTEGER NOT NULL REFERENCES "assembly_seat" ("id") MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT, PRIMARY KEY("post_id"));and I want my post_id field to point to post(id), how do I do it? I have tried with the following phrase: "post_id" INTEGER NOT NULL UNIQUE, REFERENCES "post" ("id") MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT,but I am getting this error: ERROR: there is no unique constraint matching given keys for referenced table "post"The values of post_state(asembly_seat_id) do not change in this case. 解决方案 A foreign key constraint can span multiple columns. You could just add the column revision to the table post_state.CREATE TEMP TABLE post ( post_id serial NOT NULL ,revision integer NOT NULL DEFAULT 0 ,summary text NOT NULL ,description text NOT NULL ,user_id integer NOT NULL REFERENCES user (id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT ,post_type_id integer NOT NULL REFERENCES post_type (id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT ,ctime timestamptz DEFAULT NOW() ,PRIMARY KEY(post_id, revision));CREATE TEMP TABLE post_state ( post_id integer NOT NULL ,revision integer NOT NULL ,assembly_seat_id integer NOT NULL REFERENCES assembly_seat (id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT ,PRIMARY KEY(post_id, revision) ,FOREIGN KEY (post_id, revision) REFERENCES post (post_id, revision));Read the manual about foreign key constraints.I am using the name post_id for the primary key column of table post. Don't use id as column name. If you join a bunch of tables you end up with a bunch of columns all names id. Regrettably, some half-wit ORMs are in the habit of doing that.Alternatively, it might be better design to have unique post_id in table post and add a table post_revision with a n:1 relation to post. 这篇关于PostgreSQL中的外键匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！
