Re: foreign key constraint versus referential integrity constraint

On 21 oct, 17:49, Tegiri Nenashi <tegirinena..._at_gmail.com> wrote:
> 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.
Quite frankly, I do not see how that could bring any value in the context of arbitrary cardinalities...

Regards... Received on Wed Oct 21 2009 - 21:00:10 CEST