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>
>>>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.
Date: Thu, 06 Aug 2009 17:45:05 -0300
Message-ID: <4a7b40d2$0$23762$9a566e8b_at_news.aliant.net>
Mr. Scott wrote:
> <snip> >>>>may be
>>>>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
>>>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