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

> 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

Yeah, we fixed this to follow spec (sql actually requires that the
references be to a unique or primary key constraint)

For references to news_stories, you probably need to break news_stories
into two more normalized tables that actually have candidate keys.
I'd guess one would be id and fields that depend only on id and the other
would be id, media type and fields that depend on both of those.

I'm not so sure for the other constraint (still trying to think that case
through)

Hi Stephan,

Thanks for your reply and suggestions.  I was hoping for a
solution that would not require me to break apart any of the
tables or employ new ones.

But apparently i have no choice.

One would think that there would a such a construct defined in
SQL for specifying a field in a table with restriction placed on
its values based on values in a 'foreign table field'.

I'm sure i wouldn't be the only person that would utilize such a
feature.


Thanks again,




On Thu, Aug 02, 2001 at 03:20:24PM -0700, Stephan Szabo wrote:
>
> > 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
>
> Yeah, we fixed this to follow spec (sql actually requires that the
> references be to a unique or primary key constraint)
>
> For references to news_stories, you probably need to break news_stories
> into two more normalized tables that actually have candidate keys.
> I'd guess one would be id and fields that depend only on id and the other
> would be id, media type and fields that depend on both of those.
>
> I'm not so sure for the other constraint (still trying to think that case
> through)

--
patrick keshishian

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

On Fri, 3 Aug 2001, patrick keshishian wrote:

> Thanks for your reply and suggestions.  I was hoping for a
> solution that would not require me to break apart any of the
> tables or employ new ones.
>
> But apparently i have no choice.
>
> One would think that there would a such a construct defined in
> SQL for specifying a field in a table with restriction placed on
> its values based on values in a 'foreign table field'.

Technically, you would be able to handle this through a subquery
in a check constraint, however we don't support this currently
(it's a non-trivial problem to solve for a potentially complicated
query).