Re: foreign key constraint versus referential integrity constraint

"Keith H Duggar" <duggar_at_alum.mit.edu> wrote in message news:ee296634-d56d-4b09-942f-4b0999136a3e_at_l33g2000vbi.googlegroups.com...
> Thanks to everyone. I think I understand more clearly now the
> inherent asymmetry of foreign key constraints. But now then my
> question is what is the common name for the semantic constraint
> that "attribute RV1:X and RV2:Y are sematincally joinable"?
>
> Because simply having the same type is not enough for example
> both OrderID and CouponID might both have type GenericID and
> yet we may want to state that it doesn't make sense to join
> them. Likewise attribute names for joinable attributes may be
> different in different relations (ie needing rename).
>
> In other words suppose we go with Mr. Scott's reformulation
> but with some additional type information (name : type) and
> attribute name changes in Redemptions for argument sake
>
> Orders {
> OrderID : GenericID }
>
> Coupons {
> OrderID : GenericID ,
> CouponID : GenericID }
>
> Redemptions {
> CID : GenericID ,
> OID : GenericID }
>
> where CID is the redeemed coupon's ID and OID is the ID of the
> order on which the coupon was redeemed (not the order the coupon
> was shipped with).
>
> So as above, we cannot tell it is ok to join CID with CouponID
> from the attribute name, since they are different, nor can we
> assume they can be joined simply because they are both type of
> GenericID because for example so is OID which is not appropriate
> to join. So if we wanted to express the additional constraint
> that is semantically appropriate to join CouponID with CID
> what kind of constraint would this be? Likewise if we wanted
> to express it is semantically inappropriate to join OrderID
> and CID?

If you use generic ids then I think you would have to explicitly specify the ISA relationships between the objects represented in the database, but if you use a separate domain or type for each kind of object, then the relationships become implicit. I would like to explore this further, but I don't have the time right now.

>
> By the way, Mr. Scott, I removed O# from your R because it
> seemed redundant to me since we can recover the OrderID of the
> order the coupon was shipped with from the Orders relation (if
> we assume that CouponID is a candidate key of Coupons). Is
> that not correct?

You can indeed recover the OrderID from the coupon in a join, but how would you declare the constraint that coupons can't be applied to the order with which they were shipped?

>
> Thanks again for the help!
>
> KHD
Received on Thu Oct 22 2009 - 15:40:08 CEST