Re: Foreign superkey support

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.

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.

-- 
Jon