Re: Multiple References on one Foreign Key - Mailing list pgsql-general

On Thu, 3 Apr 2003, Network Administrator wrote:

> Quoting Stephan Szabo <sszabo@megazone23.bigpanda.com>:
>
> > On Thu, 3 Apr 2003, Network Administrator wrote:
> >
> > > I hope this is the right place to ask this question.  I've search the
> > archives
> > > the news groups and could not find anything so I apologize if this has
> > been
> > > answered previously.
> > >
> > > Here is my sample table:
> > >
> > > CREATE TABLE master2 (
> > >   id int primary key,
> > >   name varchar(32),
> > >   nickname varchar(32),
> > >   unique (name,nickname)
> > > );
> > >
> > >
> > > This issue is this:
> > >
> > > create table reftest2 (
> > >  id int primary key,
> > >  invited varchar(32),
> > >  foreign key (invited) references master2 (name,nickname) on update
> > > cascade on delete cascade
> > > );
> > >
> > >
> > > This produces the following message:
> > >
> > > "ERROR:  CREATE TABLE: number of referencing and referenced attributes
> > > for
> > > foreign key disagree"
> > >
> > > The idea is that I want to have invited in reftest2 be constrainted
> > > against name
> > > and nickname in the master2 table (actually I really don't want to
> > > "unique
> > > (name,nickname)" in master2, I'd rather have name and nickname be
> > > unique within
> > > themselves if possible)
> > >
> > > Is this possible?
> >
> > Not directly using the above schema and foreign keys.  We might be able to
> > make some suggestions on a workaround depending on what the constraints of
> > the solution need to be.
>
> Well, didn't want to get to specific because part of the problem is that the
> constaints are/were an afterthought (i.e. someone said, "can you do this?") to
> the applicate design so the "right" thing to do is to correct for this
> particular scenario is to use a different primary key- name or nickname but not
> both.  For what I want to do, I really don't need to change the uniqueness of
> name and nickname- I could choose one but I wanted to know if I **could** use
> both in concept.
>
> How would you re-write the schema so that you use a n-column contraint for one
> feild in another table?

You'd have to do something like the id field with another table to get
around having multiple columns, but if using ids doesn't work for
you, it wouldn't here either.

> > You could write a custom trigger set to handle the case.  This probably
> > gets you the closest to what you want but probably involves the most work.
>
> Hmmmm, there's one I didn't think about- triggers are being generated anyway.

In fact, you could base it off the ri trigger code
(backend/utils/adt/ri_triggers.c).

You'd want to change how it treats its arguments and the generated
queries to change the referential actions from something like
WHERE keycol1 = $1 [AND ... ]
to something like
WHERE (keycol1 = $1 OR keycol1 = $2)
and then change the code that gets the values for the placeholders,

and the insert/update on fk check from something like
WHERE keycol1 = $1
to something like
WHERE (keycol1 = $1 OR keycol2 = $1)

[Since I haven't tried this, I may have made a mistake on the above
fragments or may be forgetting something]