Re: Does entity integrity imply entity identity?

"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message news:4a7b06a0$0$23781$9a566e8b_at_news.aliant.net...
> Mr. Scott wrote:
>
>> "Mr. Scott" <do_not_reply_at_noone.com> wrote in message
>> news:BPadnZQTbZN6EefXnZ2dnUVZ_tadnZ2d_at_giganews.com...
>>
>>>"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message
>>>news:4a7a425c$0$23748$9a566e8b_at_news.aliant.net...
>>>
>>>>Mr. Scott wrote:
>>>>
>>>>
>>>>>"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message
>>>>>news:4a7a22e9$0$23782$9a566e8b_at_news.aliant.net...
>>>>>
>>>>>
>>>>>>Bob Badour wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Mr. Scott wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message
>>>>>>>>news:4a799414$0$23781$9a566e8b_at_news.aliant.net...
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>Mr. Scott wrote:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message
>>>>>>>>>>news:4a784836$0$23766$9a566e8b_at_news.aliant.net...
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>Mr. Scott wrote:
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>>>The entity integrity rule is generally presented as a subrule
>>>>>>>>>>>>>under Codd's rule 10.
>>>>>>>>>>>
>>>>>>>>>>>I don't know why you would say that when the description of the
>>>>>>>>>>>so-called "entity integrity" basically restates Rule 2 verbatim.
>>>>>>>>>>
>>>>>>>>>>I don't think that's true. Although the entity integrity rule
>>>>>>>>>>implies the guaranteed access rule, the guaranteed access rule
>>>>>>>>>>doesn't imply the entity integrity rule. The guaranteed access
>>>>>>>>>>rule doesn't say anything at all about nulls.
>>>>>>>>>
>>>>>>>>>As Martha would say: This is a good thing. Null was a bad idea in
>>>>>>>>>the first place.
>>>>>>>>
>>>>>>>>Why was it a bad idea?
>>>>>>>
>>>>>>>Entire books have been written on that subject. I suggest you read
>>>>>>>one or two of them. See for example Date's various _Writings..._
>>>>>>>books.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>Is there a reasonable alternative?
>>>>>>>
>>>>>>>Yes. Nothing at all is a reasonable alternative to anything as bad as
>>>>>>>null. If it ain't broken, don't try to fix it. If it is broken, don't
>>>>>>>break it worse.
>>>>>>
>>>>>>Perhaps I should clarify: Your question amounts to asking if one has a
>>>>>>reasonable alternative to poking oneself in the eye with an icepick.
>>>>>
>>>>>If the requirement is to record in the database as much information as
>>>>>is available, then how can the fact that a value is at present unknown
>>>>>be recorded?
>>>>
>>>>By stating so in the database. That's what relations do: make
>>>>statements.
>>>
>>>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.
>
> How exactly does NULL allow one to declare the appropriate integrity?

I posted this in response to Nilone's post

CREATE TABLE T
(

    K INT NOT NULL PRIMARY KEY,

    A VARCHAR(40) NULL,
    B VARCHAR(160) NULL,
    C VARCHAR(20) NULL,