Re: quick FK question
Date: Tue, 15 Dec 2009 23:45:09 +0200
Message-ID: <6e49b6d00912151345k37ea5996wd35c87d9de0a5969_at_mail.gmail.com>
2009/12/15 Sweetser, Joe <JSweetser_at_icat.com>:
> 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
http://www.gplivna.eu
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 15 2009 - 15:45:09 CST