Re: Multiple foreign keys on same field

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/