foreign key constraint versus referential integrity constraint

From: Keith H Duggar <>
Date: Wed, 21 Oct 2009 08:29:15 -0700 (PDT)
Message-ID: <>

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