Re: foreign key constraint versus referential integrity constraint

> 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

You have the direction wrong. Coupons are created and go out with orders, so the set of coupons defined in rvOrders is the total set known of by the system and serves to define the associated domain. rvRedemptions has the set of coupons that have been redeemed, and of course it's not possible to redeem an inexisting coupon. So there's an inclusion dependency from rvRedemptions to rvOrders, which is then enforced using a foreign key, in that direction. Notice that a foreign key can't be used to enforce this constraint unless CouponID is a candidate key of rvRedemptions.

> Or is a foreign key constraint a synonym for a referential integrity constraint?

It is an SQL mechanism, and not particularly powerful or convenient one at that, to implement referential integrity constraints. On the theoretical side the slightly more general concept of inclusion dependency is used instead of referential integrity constraint; it additionally applies to the case where multiple such constraints can have arbitrary overlap in their attributes, and the "target" side need not constitute a candidate key. The definition is "one projection must remain the subset of another".

--
Sampo