Re: Can FK be nullable/optional by design?

#1

"Bruce Lewis" <brlspam (AT) yahoo (DOT) com> wrote

Quote:

net__space (AT) hotmail (DOT) com (Andy) writes:

General statement: FK should not be nullabe to avoid orphans in DB.

I don't see the reasoning behind this statement. Any column that
references keys to another table should be explicitly specified as such
to avoid orphans.

If that column may sometimes be unknown/unspecified for perfectly valid
records, I see no reason not to make it nullable.
snip

I agree. In fact, I am working on a system right now where we have this case
exactly. We have invoices that may or may not be associated with a contract.
We have FK for the contract number in the invoice table (over simplified for
sake of argument) and it must be, by definition, nullable because business
rules allow for it.

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

#2

I would characterize a broken relationship (RI enforced through application
code) as an orphan.

#3

"DataMan" <dataman (AT) ev1 (DOT) net> wrote

Quote:

I would characterize a broken relationship (RI enforced through application
code) as an orphan.

Yes, if the broken relationship was mandatory. In which case you have
either a bug in your application or an unauthorised data change -
which is why you shouldn't be enforcing RI in the application unless
you have no choice, in which case you may be using the wrong DBMS.

Regards,

Eric

#4

"Tobin Harris" <tobin_dont_you_spam_me (AT) breathemail (DOT) net> wrote:

Quote:

Just out of interest, what would you describe as the fundamentals?

Tobes

entities, attributes, and relationships!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Can FK be nullable/optional by design?

comp.databases comp.databases

Re: Can FK be nullable/optional by design? - 12-02-2003 , 07:37 AM

Re: Can FK be nullable/optional by design? - 12-10-2003 , 09:29 AM

Re: Can FK be nullable/optional by design? - 12-11-2003 , 05:08 AM

Re: Can FK be nullable/optional by design? - 12-13-2003 , 03:49 PM