Re: foreign key constraint versus referential integrity constraint

On Oct 21, 8:29 am, Keith H Duggar <dug..._at_alum.mit.edu> wrote:
> I'm trying to understand the relationship between referential
> integrity and foreign key constraints. My example is suppose we
> have the ubiquitous supplier who sends the usual Orders with
> the usual info but also includes a Coupon that can be redeemed
> on future Orders:
>
> Orders { OrderID, CustomerID, ... usual stuff ..., CouponID }
>
> and the database tracks if a Coupon is redeemed and if so the
> order it was redeemed on:
>
> Redemptions { CouponID, OrderID }
>
> Assume there are relvars rvOrders and rvRedemptions of type
> Orders and Redemptions respectively. So if a coupon is redeemed
> an entry will be added to rvRedemptions with the OrderID of the
> order coupon was redeemed on (not he order it was mailed out
> with).
>
> So Coupons may or may not be redeemed so there may be CouponID
> appearing in rvOrders that have no entry in rvRedemptions.
>
> Now it seems to me that 1) conceptually to me rvOrders:CouponID
> is a foreign key to rvRedemptions 2) referential integrity is
> violated since rvOrders can have CouponID not appearing in
> rvRedemptions 3) this does not seem "evil" to me.
>
> Please help me understand where my thinking is going wrong. To
> that end here are some questions. Is rvOrders:CouponID in fact
> a foreign key to rvRedemptions? Or is a foreign key constraint
> a synonym for a referential integrity constraint? If it is not
> a foreign key then what does one call the constraint that joins
> on rvOrders:CouponID and rvRedemptions:CouponID are semantically
> correct? If it is a foreign key then does it violate referential
> integrity? If so is this evil and why?
>
> Thanks in advance!
>
> KHD
I prefer the term "inclusion dependency": projection of one relation (that is rvRedemptions v [CouponID]) is a supepersetset of projection of the other (i.e. rvOrders v [CouponID]). I thought that all three terms are the same; perhaps with foreign key constraint adding some insignificant matter, like the "smaller" set being unique. Received on Wed Oct 21 2009 - 17:49:54 CEST