Re: foreign key constraint versus referential integrity constraint

Keith H Duggar 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
Perhaps a clue lies in asking whether it is a requirement that a coupon is only issued once and if so, how the design should reflect that (presumably a coupon can be redeemed only once).

(Since I first saw Codd's 1970 paper I've never been able to understand why on the one hand he talked about "symmetrical" access but on the other he used the very restrictive term "foreign key".) For me, a "reference" is a reference even if most dbms' have similar restrictions to Codd's term. It seems arbitrary and unnecessarily doctrinaire to restrict references to only primary keys and unique indexes because I think if one wants a particular constraint, one should not have specify two constraints, eg., a 'reference' as well as a key or index. This doesn't prevent a language from offering shortcuts that are understood to involve both.) Received on Wed Oct 21 2009 - 21:15:34 CEST