非空外键作为数据库标准
背景：今天，在使用 SQL Server Reporting Services 时出现了一个问题。报告查看器中的 SSRS 下拉参数似乎不允许您指示（空）选项，以便您可以看到该参数为空的报告。基本上，表 A 是一个空引用表 B 的表；由于报告使用表 B 来填充下拉列表，因此没有空值可显示为选项，因此您无法选择所有具有空 B 的 A。
我真正的问题来自于潜在的本能反应我正在回答的管理类型对上述问题的反应。当我解释发生了什么时，她发布了一项新的要求，即所有外键必须为非空，并且每个实体都应该插入一个“默认”记录，这是一个新标准，似乎是为了解决报告工具中的这个问题。基本上，如果您有 Cat 表，那么 Cat.Owner 永远不应该为 null，而应该引用 Person 表中的默认记录，即“默认”Person。
虽然这可能有助于解决 SSRS 问题，但可能会损害使用数据库的服务和应用程序的开发/维护，因为它们现在不仅必须考虑空值（此时允许空值），而且还要查找并正确使用空值。 “默认”记录。我想过说服她放弃这项任务，但在决定这样做之前，我想从经验丰富的人那里收集一些信息。
有人可以权衡这可能有什么帮助或伤害吗？
有人有这个作为数据库标准吗？有什么问题、发展或其他方面我应该注意的吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
NULL 字段意味着没有值。
因此用 NULL 填充缺失的关系是合乎逻辑且正确的。
有时，在某些特殊情况下，我看到使用问题中描述的默认记录。但这是“特殊情况”并且“有时”使用。
恕我直言，强制将此政策作为数据库开发标准是愚蠢的、不正确的并且具有潜在危险。我不会冒险尝试这样的设计所引起的所有可能的复杂情况：我猜你已经想象到这意味着什么了。
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.
然而，如果 CAT 是 OWNED_CAT，那么它必须有一个 PERSON 作为所有者，如果它被放弃，它不应该在该表中。
但是，如果 CAT 是 ANY_CAT（有/没有所有者），则该废弃的 CAT 不应与任何人关联。
对我来说，为了报告的目的在 PERSON 表中创建虚拟或默认记录确实具有商业意义（对于您的经理而言），但我对此感觉不“正确”。
不能给你太多，只能给你 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.