Re: Multiple References on one Foreign Key

Quoting Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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?

> Some notes:
> In general, the values of name/nickname probably need to be unique
> across the set of both, otherwise which row is being referenced?
>
> Depending on what you're doing, you might be better off adding a reference
> to the id field of master2 rather than to the name. This would involve an
> extra step on inserting into reftest2, although you could do it in a
> trigger, but wouldn't really work for on update cascade.

Yea, I thought about that too but that would result in some application logic
changes that I can't make right now- you know the drill- I need more hours in
the day :)

> 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.

Thanks for the reply Stephan!

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com