Re: foreign key constraint versus referential integrity constraint

"Keith H Duggar" <duggar_at_alum.mit.edu> wrote in message news: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
A foreign key constraint is an inclusion dependency and an inclusion dependency is a referential constraint, but there are referential constraints that are not inclusion dependencies and there are inclusion dependencies that are not foreign key constraints. For example, a constraint that states that if there is a row in one table there cannot be a corresponding row in a different table is neither an inclusion dependency nor a foreign key constraint but is still a referential constraint nonetheless. Also, a constraint that states that whenever there is a row in one table there must be at least one corresponding row in another is not a foreign key constraint, but it is still an inclusion dependency.

I think the first problem with your example above is that there are three different kinds of facts about three different kinds of objects that need to be represented. There are orders, there are coupons, and there are redeptions. A coupon is associated with the order that it was shipped with, but there may be orders that aren't shipped with coupons. A redemption associates a coupon with an order, presumably other than the one that the coupon was shipped with. So basically, the three kinds of facts are those that assert that there is an order, O, those that assert that a coupon was shipped with a particular order, C, and those that assert that a coupon was redeemed on a particular order, R. C implies O because coupons are shipped with orders, R implies C because a there has to be a coupon before it can be redeemed, and also R implies O because there has to be an order on which to redeem the coupon.

Since there are three kinds of facts, there should be three tables:

one for orders, O{O#}

one for coupons, C{C#, O#}

    with a foreign key from C[O#] to O[O#],

and one for redemptions, R{C#, CO#, O#}

    with foreign keys from R[O#] to O[O#]     and from R[C#, CO#] to C[C#, O#],
    along with the constraint that CO# != O#

To be able to enforce the constraint CO# != O# requires a foreign key constraint that is not just key based but superkey based.

Hope this helps. Received on Thu Oct 22 2009 - 03:59:54 CEST