外键的默认值与 null
我有一个关于在数据库中外键列使用 null 与默认值的问题。在设计数据库时，我发现了很多关于 null 与默认值的相反意见，但不完全是针对外键（主要优点和缺点是什么）。
目前我正在设计一个新的数据库，它将为不同的 Web 应用程序和其他具有不同数据访问方法（ORM、存储过程）的系统存储大量数据，并且我希望在尽可能最低的级别（数据库）实现通用规则。 （所以稍后在应用程序中不必担心此规则）。
举个例子，假设我有一个用户表 User，其国籍 NationalityID 的外键列是主键 CountryID对于表国家。
现在我有两个/三个选项：
A：我允许 NationalityID 列（以及数据库中所有其他类似的外键列）为空，并坚持始终处处检查空值的常见方法（应用应用程序中的规则）
或
B：我为每个外键分配一个默认值，假设为“-1”，并在每个关系表中放入以“-1”作为键的附加列，所有其他数据为“无数据”（对于此示例中的国家/地区表中我放置了 CountryID 为“-1”的列，对于 CountryName 我设置了“无数据”）。因此，每次我想知道用户的国籍时，我总是会得到结果，而无需额外的代码规则（不需要我检查它是否为空）。
或
C：我可以禁止外键为空值。但这确实是我想要避免的事情。 （如果不是附加数据（用户国籍），我需要有一个选项至少存储基本数据（用户姓名））
那么 B 是好方法吗？我在这里缺少什么？通过这种方法我失去的会比我得到的更多吗？我可能会遇到哪些问题（除了要小心关系表中始终有 ID 值为“-1”的附加列（表示没有数据））？
您对外键默认值的好/坏经历是什么？
谢谢
I have a question about using null vs. default value for foreign key columns in database. I found a lot of opposite opinions about null vs. default values when designing databases but not exactly for foreign keys (what are main pros and cons).
Currently I'm designing a new database which will store a lot of data for different web applications and other systems with different data access approaches (ORM, stored procedures) and I want to implement general rules on the lowest level as possible (database). (So not to worry about this rules later in applications).
To give you an example let's say that I have a table of users User with foreign key column for his nationality NationalityID which is a primary key CountryID for table Country.
Now I have two/three options:
A: I allow NationalityID column (and all other similar foreign key columns in database) to be null and just stick with common approach of checking always and everywhere for null (applying rules in application)
or
B: I assign a default value for every foreign key to be let's say "-1" and put in every relation table additional column with "-1" as a key and all other data as "No data" (for this example in Country table I put column with CountryID of "-1" and for CountryName I set "No data"). So every time I will want to know users nationality I will always get result without additional code rules (no need for me to check if it's null or not).
or
C: I can disallow null value for foreign keys. But this is really something what I want to avoid. (I need to have an option to store at least basic data (users name) if not the additional data (users nationality))
So is B good approach or not? What am I missing here? Do I lose more that I gain with this approach? Which problems could I have (in addition to be careful to always have additional column in relational tables with ID value of "-1" which says there is "No data")?
What is your good/bad experience with foreign key default values?
thank you
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
如果标准化，这不会成为问题。
不要将国籍放入
USER表中，而是创建一个User_Nationality表，将用户链接到 <另一个表中的 code>Country_ID。如果他们在该查找表中有一个条目，那就太好了。如果不是，则不需要为其存储
NULL或默认值。您需要强制执行 FK 关系，而允许 NULL 会违背这一点。您也不希望仅仅为了填充字段而编造可能不准确的信息，这从一开始就否定了需要该字段的意义。
使用查找表，您可以完全绕过它。
这也将允许您改变主意并在以后选择您的选项之一。
如果您使用视图，则可以选择将缺失数据视为 NULL 或默认值，而无需更改基础数据。
If you normalize this won't be an issue.
Instead of putting nationality in the
USERtable, make aUser_Nationalitytable that links users toCountry_IDin the other table.If they have an entry in that lookup table, great. If not, you don't need to store a
NULLor default value for it.You need to enforce FK relationships, and allowing
NULLgoes against that. You also don't want to make up information that may not be accurate just to populate a field, which negates the point of requiring the field in the first place.Use lookup tables and you can bypass that entirely.
This will also allow you to change your mind and choose one of your options down the road.
If you use views, you can choose to treat missing data as a
NULLor a default value without needing to alter the underlying data.就我个人而言，我认为即使您的数据库中有一个键为 -1 的非条目条目，您仍然会执行检查以查看是否要为每个单独的字段显示“无数据”。
我会坚持使用 NULL。 NULL 意味着没有数据，这里就是这种情况。
Personally, I would feel that even if you have a non-entry entry in your database with a key of -1, you would still be performing a check to see whether you want to display 'No Data' or not for each individual field.
I would stick to NULLs. NULL is meant to mean the absence of data, which is the case here.
B 是一种糟糕的方法。记住处理空值比必须弄清楚你使用了什么幻数然后你仍然必须处理它们更容易。使用数字 1。但我最喜欢 JNK 的想法。
B is a terrible approach. It is easier to remeber to handle nulls than to have to figure out what magic number you used and then you still have to handle them. Use number 1. But I like JNKs idea best.
我建议选项 D。如果不是所有用户都有定义的国籍，那么该信息不属于用户表。创建一个名为 UserNationality 的表，该表以 UserId 为键。
I suggest option D. If not all users have a defined nationality then that information doesn't belong in the user table. Create a table called UserNationality keyed on UserId.
我喜欢你的 B 解决方案。也许可以将值映射到其他实体，因此您有 Country 和 NullCountry ，它扩展了 Country 并映射到 id=-1 的行，并且在其方法中具有特殊代码，以便于处理特殊情况。
一个问题可能是对该外键进行外连接会更加困难。
编辑：不，外连接应该没有问题，因为不需要进行外连接。
I like your B solution. Maybe it will be possible to map the values into other entities, so you have Country, and NullCountry that extends Country and is mapped to row with id=-1 and have special code in its methods to make it easy to handle special cases.
One problem is probably that it will be harder to do outer joins on that foreign key.
EDIT: no, there should be no problem with outer joins, because there would be no need to do outer joins.