Re: Multiple References on one Foreign Key

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.

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.

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.