sql - PostgreSQL中的外键匹配

只是好奇，如果我有这张桌子：

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

要存储帖子和此表：

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

我想让我的post_id字段指向post(id)，我该怎么做？我试过以下短语：

    "post_id" INTEGER NOT NULL UNIQUE,
        REFERENCES "post" ("id") MATCH SIMPLE
            ON UPDATE RESTRICT
            ON DELETE RESTRICT,

但我有个错误：
错误：没有与引用表“post”的给定键匹配的唯一约束
在这种情况下，post_state(asembly_seat_id)的值不会改变。

最佳答案

外键约束可以跨越多个列。您可以将列revision添加到表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)
);

阅读关于foreign key constraints的手册。
我使用名称post_id作为表post的主键列。不要使用id作为列名。如果你加入一堆表，你会得到一堆列，所有的名字都id。遗憾的是，有些愚蠢的人有这样做的习惯。
或者，最好在tablepost_id中使用uniquepost，并添加一个与post_revision具有n:1关系的tablepost。

关于sql - PostgreSQL中的外键匹配，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16462098/