Re: Does entity integrity imply entity identity?

From: Mr. Scott <do_not_reply_at_noone.com>
Date: Thu, 6 Aug 2009 16:13:57 -0400
Message-ID: <xKydnQ3OFdYYpObXnZ2dnUVZ_rmdnZ2d_at_giganews.com>


<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. Received on Thu Aug 06 2009 - 22:13:57 CEST

Original text of this message