Re: quick FK question

  • From: Gints Plivna <gints.plivna@xxxxxxxxx>
  • To: JSweetser@xxxxxxxx
  • Date: Tue, 15 Dec 2009 23:45:09 +0200

2009/12/15 Sweetser, Joe <JSweetser@xxxxxxxx>:
> I'm having trouble understanding FK's and NULL's.  It appears I can
> insert a null value to a foreign key which is not what I would expect.
> Is the "solution" to put NOT NULL on the column definition?

Solution to WHAT?
What and where is the problem? :)

If the problem is that FK column must be always filled according to
business, then yes - not null constraint is the way to go.
If the problem is that you feel uncomfortable with null FK columns,
then you just provided valid examples. And please, please don't do as
I've seen once from some duhvelopers - they created all FK columns NOT
NULL. Unfortunately of course there were cases with FK columns where
actual value could not be provided. So what did they do? An obvious
solution! ;) Added one row with id = -1 as a stub to all db tables.
And filled "all should be NULL FK columns" with that derived -1 value.
Ahhhh, that was nightmare! I'm completely sure there are many reports
and other functionality that actually counts these stub -1 rows as
actual business rows if the soft somewhere exists any more....
So effectively they masked NULL values as -1 and just knocked the
(possible) problem deeper, but it really fought back in many other
places. And yes I was veeery angry these days when had to overcome
that stupid thing :)

Gints Plivna

Other related posts: