Обсуждение: Multiple References on one Foreign Key
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 d elete 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? -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.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. 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.
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? > 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
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]