Null-valued foreign keys...?

From: Lane Wimberley <lane_at_wayport.net>
Date: Sat, 20 Oct 2001 22:23:38 -0500
Message-ID: <3BD23FBA.5CDCEC94_at_wayport.net>



In practice, I was wondering how folks view null-valued

foeign keys; that is, allowing the value of a foreign key

to be null. My guess is that a purist might argue that

foreign keys should never have a null value. If a relationship

is optional, then this should be modelled using a separate relating

table. The presence of a row in that table relating rows in two

other tables indicates a relationship, and an absence indicates no

relationship. Of course, this make joins a little more involved.

In practice, I imagine that many DB engineers feel that it is

reasonable to allow null as a valid foreign key value. The value

null simply indicates no relationship.

To complicate matters, I can imagine that there are cases

where the optional nature of a particular relationship is only

temporary, in which case we might say that a foreign key can

have a null value for a while, but ultimately it should have a

valid, non-null value.

Does anyone have a particularly strong opinion one way or

another? What do the modelling theorists say? Is there a

good engineering practice, an accepted convention?

Thanks!


Lane Wimberley             8303 N. MoPac, Suite A-300  Austin, TX 78759
Wayport, Inc.                512.519.6195 (voice)    512.519.6200 (fax)

Microsoft Corp., concerned by the growing popularity of the free 32-bit operating system for Intel systems, Linux, has employed a number of top programmers from the underground world of virus development. Bill Gates stated yesterday: "World domination, fast -- it's either us or Linus". Mr. Torvalds was unavailable for comment ...

  • Robert Manners, rjm_at_swift.eng.ox.ac.uk, in comp.os.linux.setup
Received on Sun Oct 21 2001 - 05:23:38 CEST

Original text of this message