Re: Foreign superkey support
From: paul c <toledobythesea_at_oohay.ac>
Date: Tue, 08 Aug 2006 17:38:44 GMT
Message-ID: <E_3Cg.348530$iF6.170940_at_pd7tw2no>
>
> An example: We have Items, each with a Type, and want to arrange them in
> Groups. Each Group also has a Type, and an Item may only be in Groups
> with the same Type. (Whether the relationship between Items and Groups
> is many-to-one or many-to-many doesn't really matter; I'll assume
> many-to-many. I'll also assume, in case it matters, that a Group may
> exist without any Items.)
>
> Straightforward relational modelling of this produces the relvars
> Item{_I#_, Type}, Group{_G#_, Type} and ItemGroup{_I#, G#_}---with
> foreign keys from ItemGroup to Item and Group. However, in order to
> (declaratively) support the constraint that Items can only be in Groups
> of the same Type, we would need an ASSERTION-type constraint involving
> the join of all three.
>
> An alternative is to add a Type attribute to ItemGroup, i.e.
> ItemGroup{_I#, G#_, Type}, and use foreign superkeys {I#, Type} and {G#,
> Type} to Item and Group.
Date: Tue, 08 Aug 2006 17:38:44 GMT
Message-ID: <E_3Cg.348530$iF6.170940_at_pd7tw2no>
Jon Heggland wrote:
> Bob Badour wrote:
>> Jon Heggland wrote: >>> Bob Badour wrote: >>>> Why include them in the referencing relation at all? >>> Pragmatic reasons. For instance as an alternative to an ASSERTION-type >>> join-based constraint, which many(? most? Does anybody know?) DMBSs >>> don't support. >> I don't follow your reasoning. If you simply omitted the dependent >> attributes from the referencing relation, normal foreign key constraints >> would suffice.
>
> An example: We have Items, each with a Type, and want to arrange them in
> Groups. Each Group also has a Type, and an Item may only be in Groups
> with the same Type. (Whether the relationship between Items and Groups
> is many-to-one or many-to-many doesn't really matter; I'll assume
> many-to-many. I'll also assume, in case it matters, that a Group may
> exist without any Items.)
>
> Straightforward relational modelling of this produces the relvars
> Item{_I#_, Type}, Group{_G#_, Type} and ItemGroup{_I#, G#_}---with
> foreign keys from ItemGroup to Item and Group. However, in order to
> (declaratively) support the constraint that Items can only be in Groups
> of the same Type, we would need an ASSERTION-type constraint involving
> the join of all three.
>
> An alternative is to add a Type attribute to ItemGroup, i.e.
> ItemGroup{_I#, G#_, Type}, and use foreign superkeys {I#, Type} and {G#,
> Type} to Item and Group.
Please pardon my confusion about the lingo - isn't a foreign key just a shortcut notation for an ASSERTION-type constraint? Ie., what the system must do is the same for both, eg., ItemGroup{I#} join Item{I#} = ItemGroup{I#} would be the assertion that means the same as ItemGroupFKItem(Item{I#}) and similarly for superkeys.
Perhaps all I'm saying is that the basic relational algebra doesn't need a foreign key concept which I admit is a deviation from the specific question. But as long as people like the shortcut notation, I think the example is a good reason for allowing super foreignkeys or foreign superkeys!
p Received on Tue Aug 08 2006 - 19:38:44 CEST