Re: Does entity integrity imply entity identity?

"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message news: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.

Null makes it possible to define a check constraint in this case because check constraints cannot reference other tables. Received on Fri Aug 07 2009 - 03:18:38 CEST