re: 7.1.2 and foreign key unique constraint.

Greetings,

I ran accross this problem upon upgrading our database from 7.0.3
to 7.1.2:

ERROR: UNIQUE constraint matching given keys for referenced
table "some_table" not found

Looking through the mailing lists i came across a couple of
discussions regarding this very same error:

http://fts.postgresql.org/db/mw/msg.html?mid=1022876
http://fts.postgresql.org/db/mw/msg.html?mid=1024107

I have always used foreign keys for data integrity purposes.
Basically using them to make sure one table only contains values
that are "allowed" (read: are in the foriegn table field).

A simplified _example_ of my tables follows:

--
--
-- Candidate stories for publishing.
--
create table news_stories
(
id int4 not null, -- not a primary key
media int2 not null, -- 1 = text
-- 2 = audio
-- 3 = video
-- etc ...
--
-- Note that there are NO primary/unique keys here.
-- A story might have accompanying audio or video tracks
-- that would share the same id.
--
-- e.g., id , media type
-- values ( 1000, 1 ) -- the story
-- values ( 1000, 2 ) -- the audio interview
-- values ( 1000, 3 ) -- mpeg footage
--
-- One can "almost" think of the primary key being a
-- composite of (id,media). But just for the sake of
-- argument lets ignore this fact, if possible.

author varchar(64),
-- some other fields go here
);

--
--
-- Assistant editors would review stories from news_stories
-- and after approving them for publishing would enter them
-- into the following table.
--
create table approved_stories
(
id int4 not null
references news_stories(id),
editor varchar(64),
-- some other fields go here
);

--
--
-- The chief editor would then select the stories to be
-- published from the approved list of stories above.
--
create table published_stories
(
id int4 not null
references approved_stories(id),
-- some other fields ...
);

I've been using the foreign key to have a constraint on the
values that would be "allowed" in the approved_stories.id field.
The id must be a value from a record already in news_stories.

The pre-condition for a published story would then be one that
is not only a "news story", but one that has been "approved" by
an assistant editor.

Apparently, I've been using the wrong "tool" to enforce the
pre-conditions in this case.

My question now becomes: How can one enforce the pre-conditions
outlined above if the pre-condition for a foreign key is that it
has to be unique itself.

Thanks for your time,
--
patrick keshishian

Gnu __ _
-o)/ / (_)__ __ ____ __
/\\ /__/ / _ \/ // /\ \/ /
_\_v __/_/_//_/\_,_/ /_/\_\