Non-Null Foreign Keys as Database Standard

Question

Background: today, an issue arose while using SQL Server Reporting Services. It seems that SSRS drop-down parameters in the report viewer don't allow you to indicate a (null) option so that you can see a report where that parameter is null. Basically, table A is a table nullably referencing table B; since the report uses table B to populate the drop-down, there are no nulls to show as an option, and thus you can't select all the the A's that have a null B.

My real question comes from the potential knee-jerk reaction to the above problem by a management type to whom I'm answering. When I explained what was going on, she issued a new mandate that all foreign keys must be non-null and that every entity should have a "default" record inserted, a new standard seemingly to solve this problem in the reporting tool. Basically, if you have a Cat table, then Cat.Owner should never be null, but should instead reference a default record in the Person table, the "default" Person.

While this may help the SSRS problem, it may hurt development/maintenance of services and applications using the database, since they'd now not only have to account for nulls (which were allowed to this point) but also have look for and properly use the "default" record. I thought about trying to talk her off the mandate, but I'd like to glean some information from the experienced before I decide to do that.

Can somebody weigh in on what this may help or hurt?

Anyone had this as a database standard? Any issues, development or otherwise, I should be mindfull of?

Answer 1

NULL fields mean no value.

So it's logical and correct to fill missing relation with NULLs.

I've seen, sometimes, in some special cases, the use of a default record as described in the question. But that was a "special case" and used "sometimes".

IMHO forcing this policy as a DB developement standard is just dumb, incorrect and potentially dangerous. I won't adventure in all possible complications arising from such a design: I guess you already imagine what that would mean.

Answer 2

While, if the CAT is an OWNED_CAT, then it must have a PERSON as the owner, if it is abandoned, it should NOT be in that table.

However if the CAT is an ANY_CAT (with/without owner), then that abandoned CAT shouldn't be associated with any PERSON.

To me, creating a dummy or default record in the PERSON table for the sake of the reporting does make business sense (for your manager) but I do not feel 'right' about this.

Can't offer you much, just my 2 cents.