Home -> Usenet -> comp.databases.theory -> foreign key constraint versus referential integrity constraint

foreign key constraint versus referential integrity constraint

From: Keith H Duggar <duggar_at_alum.mit.edu>
Date: Wed, 21 Oct 2009 08:29:15 -0700 (PDT)
Message-ID: <76f4fd43-fa76-4c6f-9a32-9e1f4040d754_at_r36g2000vbn.googlegroups.com> 





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

Received on Wed Oct 21 2009 - 17:29:15 CEST












Original text of this message