Thread: Multiple foreign keys on same field

I'm in a situation where it would be useful to bind a field in a table via
foreign keys to N other tables simultaneously. The table holds a common
type of info which all those other tables use. The many tables refer to the
common table by keeping references to its serial field.

By doing this, I could ensure that when a row in any of the many tables is
deleted or updated, the effect travels to the common table.

So far, I've been successful in defining more than one foreign key on the
same field in the lone table, tied to fields in different tables. (I half
expected it not to work though).

However, it seems that inserting values in the commons table is a
showstopper: it expects that field value to exists not in only one, but in
ALL bound tables simultaneously.

Is it possible to work around this issue? Perhaps by telling it not to
check for such things when rows are inserted in the common table? I expect
that would break the whole referential integrity thingy, but that would be
useful right about now. Really, I don't want the entire referential
integrity thing, I just want the automatic delete/update half.

--
Ciprian Popovici

On Tue, Dec 14, 2004 at 02:06:24 +0200,
  Ciprian Popovici <ciprian@zuavra.net> wrote:
> I'm in a situation where it would be useful to bind a field in a table via
> foreign keys to N other tables simultaneously. The table holds a common
> type of info which all those other tables use. The many tables refer to the
> common table by keeping references to its serial field.
>
> By doing this, I could ensure that when a row in any of the many tables is
> deleted or updated, the effect travels to the common table.
>
> So far, I've been successful in defining more than one foreign key on the
> same field in the lone table, tied to fields in different tables. (I half
> expected it not to work though).
>
> However, it seems that inserting values in the commons table is a
> showstopper: it expects that field value to exists not in only one, but in
> ALL bound tables simultaneously.

Are you sure you don't really want the foreign key relation to go in the
other direction?

On Tue, Dec 14, 2004 at 02:06:24AM +0200, Ciprian Popovici wrote:

> I'm in a situation where it would be useful to bind a field in a table via
> foreign keys to N other tables simultaneously. The table holds a common
> type of info which all those other tables use. The many tables refer to the
> common table by keeping references to its serial field.

I'm not sure I understand.  Do you want the foreign key constraints
to be in the common table or in the many tables?  That is, does the
common table refer to the many tables or do the many tables refer
to the common table?  Could you post some CREATE TABLE statements
that show what you're trying to do?

> By doing this, I could ensure that when a row in any of the many tables is
> deleted or updated, the effect travels to the common table.

That implies that the foreign key constraints are in the commmon
table and have ON UPDATE CASCADE and ON DELETE CASCADE clauses.
Is that what you mean?

> So far, I've been successful in defining more than one foreign key on the
> same field in the lone table, tied to fields in different tables. (I half
> expected it not to work though).
>
> However, it seems that inserting values in the commons table is a
> showstopper: it expects that field value to exists not in only one, but in
> ALL bound tables simultaneously.

Right.  If you have multiple foreign key constraints then they must
all be met for the insert to succeed.

> Is it possible to work around this issue? Perhaps by telling it not to
> check for such things when rows are inserted in the common table? I expect
> that would break the whole referential integrity thingy, but that would be
> useful right about now. Really, I don't want the entire referential
> integrity thing, I just want the automatic delete/update half.

Do you mean that you don't need foreign key constraints to enforce
referential integrity, but only to cascade changes to another table?
If so, have you considered using triggers instead?  Or have I
misunderstood what you're trying to do?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, 13 Dec 2004 23:00:45 -0700 Michael Fuhr <mike@fuhr.org> wrote:
> Do you mean that you don't need foreign key constraints to enforce
> referential integrity, but only to cascade changes to another table?
> If so, have you considered using triggers instead?  Or have I
> misunderstood what you're trying to do?

No, you understood right. Yes, I will have to go with triggers instead.
Thanks for the input.

--
Ciprian Popovici