sql - Foreign key matching in PostgreSQL -

just curious, if have 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 time zone default now(),     primary key("id", "revision") ); 

to store posts, , 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 want post_id field point post(id), how do it? have tried following phrase:

    "post_id" integer not null unique,         references "post" ("id") match simple             on update restrict             on delete restrict, 

but getting error:

error: there no unique constraint matching given keys referenced table "post"

the values of post_state(asembly_seat_id) not change in case.

a foreign key constraint can span multiple columns. add column revision 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 manual foreign key constraints.

i using name post_id primary key column of table post. don't use id column name. if join bunch of tables end bunch of columns names id. regrettably, half-wit orms in habit of doing that.

alternatively, might better design have unique post_id in table post , add table post_revision n:1 relation post.