Re: foreign key constraint versus referential integrity constraint

From: Mr. Scott <>
Date: Thu, 22 Oct 2009 09:40:08 -0400
Message-ID: <>

"Keith H Duggar" <> wrote in message
> 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!
Received on Thu Oct 22 2009 - 15:40:08 CEST

Original text of this message