re: 7.1.2 and foreign key unique constraint. - Mailing list pgsql-general

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 __/_/_//_/\_,_/ /_/\_\