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.

Improve this question
7
  • 1
    You can only create a foreign key that references a single row in the other table. Since post_id is not unique (only the post_id/revision combination is unique), you can't reference it. Commented May 9, 2013 at 12:49
  • OH, thanks for the reply, silly me facepalm Commented May 9, 2013 at 12:51
  • @JoachimIsaksson: You may be interested in my answer that contradicts your comment in parts. Commented May 10, 2013 at 0:55
  • @ErwinBrandstetter yea, re-modeled the tables already :) still thanks for the answer tho Commented May 10, 2013 at 1:09
  • 1
    @ErwinBrandstetter I didn't say anything about single columns in the foreign key, just that it needs to reference a single row (which is exactly what you changed it to do :) ). There are (as far as I know) basically two ways of making it reference a unique row, either make it reference the composite primary key or add a unique index to the column (post.id) he was trying to reference. Adding unique breaks the primary key in post and the foreign key change breaks what I was reading that he was attempting to do, hang a post_state on a post, not a specific revision of a post. Commented May 10, 2013 at 5:13

1 Answer 1

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Excellent answer. SQL is easy: once you master the syntax, everything reduces to a data modelling problem
@Erwin yea, I actually re-designed the model and have another post_revision table as you mentioned :P

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.