Allow zero or null values in foreign key columns? 2

[fsteeman]

Maybe I sound like a heretic, but when explicitly adding relationship constraints to the database, is it possible in SQL SERVER (I am using Management Studio Express) to allow a foreign key to have null values or values that can not be found in the related table (like 'zero')?

Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[danvlas]

Thanks Rudy, I know . To be completely honest, I learnt this the hard way.


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[unclejimbob]

Just looking back at some of my replies and came across this one. As the questions are posed I figure I may as well answer them

"So, you would replace 'Unknown' with 'Virtually unknown, but specified'.
Heck, yes - but see my other comments above for provisos and disclaimers

"I don't see the difference between the two.."
Its subtle but its there, one way means...

"I don't know the value for some reason"

and the other means...

"I don't know the value but I know the reason why I don't know"

In my business, in fact I would say that in ANY business that depends upon reporting to some extent this is crucial, why let the piece of information that you don't know a value (in your case, because it is time dependent), reside in your head or on a bit of paper or as 'general knowledge known inside a company' when you can have it reside in a repository for _everyone_ to understand ?

"moreover I could say that it's an additional burden for the user or programmer to enter and manage another value instead of accepting the reality: I don't know what I should enter, why would I enter anything?"
Its not if you use a default...which is what I usually do,
my programmers never have to wonder what, if anything, they need to put into a column, once they see it has a default they know they do not have to worry about it - and all my lookups have defaults

"You may forget to enter that record. Somehow, any record can be deleted."
If you have physically modelled your database to reflect the relationship then again you would expect the row to exist, 'forgetting to enter the record' is simply not an option, after all it IS a relationship. All a NULL says to me is that sometimes its treated as a relationship and sometimes its not - which I personally find unacceptable.

In a complex ERP you will have hundreds of relationships. Can you manage an extra record for any table just to replace the Null?"
Heck yes (and I do), although in this particular case remember that we are not talking about _all_ relationships (for example associative entity types/many-to many), just those that support relationships to 'lookup-type' tables

"For me, the possibility to leave some values unallocated represents a facility. It's an open discussion and everyone does what they feel appropriate. But for myself, Null remains the value to have when I don't know the real value at that moment."

Fair enough. Here is an example that follows on from your situation:

Your manager asks a new IT employee to write a report that shows a summary of all ingots created by grade.

Here is the summary data...

Grade 1 10 ingots
Grade 2 20 ingots
Not graded 100 ingots

Simple, right ?

So they create the report joining each instance of an ingot to the grade table and find that they have two groups Grade 1 and Grade 2 with a total of 30 ingots.

Oh dear.

Not a probem I hear you say, you simply left join the ingots to the grade table and voila.

So your report now looks like this:

Grade 1 10
Grade 2 20
null(or however your reporting tool represents an unknown value) 100

Not very nice looking is it ?

No problem, you just change the report with some code to turn 'null' into the string 'Not yet graded' which gives you this:

Grade 1 10
Grade 2 20
Not yet graded 100

Congratulations, you've just side-stepped your RDBMS and put a piece of valuable information into external storage.

But wait, there's more and it gets even better...

Now what happens is that another manager doesn't like the term 'Not yet graded' - and as the column is NULL he has no clue as to what would be an appropriate value - he wants to see 'Ungraded', so you create another report (or modify the existing one and add yet more code) to handle this.

Now you have the same piece of information being referenced in two separate ways, bring on the battle of the printouts and of course the auditors will have a field day

I too learnt this the hard way - never again.

Doesn't it make more sense to simply have an agreement across the organization that an ungraded ingot with have a grade of 'Ungraded' rather than 'any-old thing-we-can-dream-up-on-the-spur-of-the-moment' ?

For me the ability add a single record to a table in order to avoid having to answer the same question ("Under what circumstances would we not know the grading of the ingot ?") multiple times from newbies to my organization is in itself a saving, not to mention my suppliers, customers, employees and auditors all referring to the same piece of non-knowledge by the same terminology is a real bonus. I know it sounds funny, but believe me, in my situation I would much prefer to be able to report in a consistent, standard and meaningful way the status of an object - even if the status is 'I don't know the status'



happy days all
ujb