Re: Can FK be nullable/optional by design?

"Tobin Harris" <tobin_dont_you_spam_me_at_breathemail.net> wrote in message <news:braub1$1cceh$1_at_ID-135366.news.uni-berlin.de>...

> "Andy" <net__space_at_hotmail.com> wrote in message
> news:edb90340.0311301114.19718061_at_posting.google.com...
> > Hi All!
> >
> > General statement: FK should not be nullabe to avoid orphans in DB.
> >
> > Real life:
> > Business rule says that not every record will have a parent. It is
> > implemented as a child record has FK that is null.

> I'm not too hot on all this, but here is what I was lead to believe: If
> Client *must* belong to at least one group, then the client is dependent on
> the group - it cannot exist without it. Therefore, it's primary key would
> (at least logically) be a composite, where the group pk forms part of the
> clients composite primary key. This would ensure that a client cannot exist
> without a group!?
>
> This might look like:
> Client(GroupID PK, ClientID PK, Name )

Did you really mean to claim that ALL non-nullable attributes MUST 'logically' be included as part of the primary key?!

> Otherwise, if the Client could optionally belong to one Group, the
> relationship would be captured in a link table, as you suggested in B?
>
> GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

This would avoid the null nonsense until someone does an outer join.

--
Joe Foster <mailto:jlfoster%40znet.com>   L. Ron Dullard <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!