Re: Does entity integrity imply entity identity?
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 07 Aug 2009 00:01:40 -0300
Message-ID: <4a7b9914$0$23738$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.
Date: Fri, 07 Aug 2009 00:01:40 -0300
Message-ID: <4a7b9914$0$23738$9a566e8b_at_news.aliant.net>
Mr. Scott wrote:
> "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.
The specific limitations of whatever shitty product you are using are irrelevant here.
Toodles. Received on Fri Aug 07 2009 - 05:01:40 CEST