Re: foreign key constraint versus referential integrity constraint

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?

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?

Thanks again for the help!

KHD Received on Thu Oct 22 2009 - 05:22:06 CEST