Re: Does entity integrity imply entity identity?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 06 Aug 2009 17:45:05 -0300
Message-ID: <4a7b40d2$0$23762$9a566e8b_at_news.aliant.net>


Mr. Scott wrote:

> <snip>
> 

>>>>How, exactly? If you have columns K, A, B, and C such that for a given
>>>>K
>>>>it is permissible for the values for A, B or C to be at present
>>>>unknown,
>>>>but not both A and B, how would you lay out the tables and referential
>>>>constraints without using nulls?
>>>
>>>Here's a more concrete example. Software can be delivered to a customer
>>>over the internet or through the mail or both, but in order to deliver
>>>over
>>>the internet there must be an e-mail address, and in order to deliver
>>>through the mail there must be a snail-mail address. There may also be a
>>>delivery contact phone number. So for a given order K, there must be at
>>>least one of an e-mail address A or a snail-mail address B, and there
>>>may be
>>>a delivery phone number C.
>>
>>Three separate relations: {K -> A}, {K -> B}, {K -> C}. It doesn't
>>enforce the requirement for at least A or B, but neither does {K -> A,
>>B, C} where A, B and C are nullable.
> 
> CREATE TABLE T
> (
>     K INT NOT NULL PRIMARY KEY,
>     A VARCHAR(40) NULL,
>     B VARCHAR(160) NULL,
>     C VARCHAR(20) NULL,
>     CHECK (A IS NOT NULL OR B IS NOT NULL)
> )
> 
> enforces the requirement for either A or B. 

So, NULL does nothing to enforce the constraint. A check constraint enforces the requirement, which is exactly what I would use: a check constraint. Received on Thu Aug 06 2009 - 22:45:05 CEST

Original text of this message