Null-valued foreign keys...?

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