处理复合主键的表我想包含一个可为空的列
假设一个表可能如下所示：
userId INT (foreign key to a users table)
profileId INT (foreign key to a profiles table)
value INT
假设在此表中保存了用户的首选项。应根据当前用户和当前用户选择的配置文件加载首选项。这意味着 userId 和 profileId 的组合是唯一的，可以用作复合主键。
但随后我想添加保存默认值的功能，如果数据库中未保存特定 profileId 的值，则应使用该默认值。我的第一个想法是将 profileId 列设置为可为空，并表示 profileId 为 null 的行包含默认值。但是我无法使用涉及此表的复合主键，因为 可为 null 的列不能成为主键的一部分。
那么解决这个问题的“最佳”方法是什么？完全放弃主键并没有主键吗？生成一个我永远不需要的标识列作为主键？创建一个虚拟配置文件以链接到配置文件表中？为默认值创建一个单独的表（这是保证 userId 没有多个默认值的唯一选项？？）？
更新：我考虑了 Dmitry 的答案，但毕竟它有一个缺点，我什至无法在 userId 和 profileId 两列上创建唯一约束（如果 profileId 为 null，MySQL 将允许重复值，并且 DB2 将拒绝创建唯一约束）可空列的约束）。因此，对于 Dmitry 的解决方案，我将不得不忍受没有数据库一致性检查的情况。这是可以接受的吗？或者这是不可接受的（毕竟一致性检查是关系数据库的主要功能）。你的理由是什么？
Assume a table that may look like this:
userId INT (foreign key to a users table)
profileId INT (foreign key to a profiles table)
value INT
Say that in this table preferences for users are saved. The preference should be loaded according to the current user and the profile that the current user has selected. That means that the combination of userId and profileId is unique and can be used as a composite primary key.
But then I want to add the ability to also save a default value that should be used if no value for a specific profileId is save in the database. My first idea would be to set the profileId column to nullable and say that the row that has null as profileId contains the default value. But then I can't use a composite primary key that involves this table, because nullable columns can't be part of a primary key.
So what's the "best" way to work around this? Just drop the primary key completely and go without primary key? Generate an identity column as primary key that I never need? Create a dummy profile to link to in the profile table? Create a separate table for default values (which is the only option that guarantees that no userId has multiple default values??)?
Update: I thought about Dmitry's answer but after all it has the drawback that I can't even create a unique constraint on the two columns userId and profileId (MySQL will allow duplicate values if profileId is null and DB2 will refuse to even create a unique constraint on a nullable column). So with Dmitry's solution I will have to live without this consistency check of the DB. Is that acceptable? Or is that not acceptable (after all consistency checks are a major feature of relational DBs). What is your reasoning?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
AND
AND
德米特里的答案是一个很好的答案，但由于您的案例本质上涉及交集表，因此还有另一种好方法来解决此问题。对于您的情况，我也喜欢创建默认用户配置文件的想法，您可以在代码中使用该配置文件来建立默认设置。这很好，因为它可以保持数据模型干净，而无需引入额外的候选键。您需要在这个虚拟/默认配置文件中清楚地了解它是什么。您可以为其指定一个清晰的名称，例如“默认用户”，并确保除管理员之外的任何人都可以访问用户凭据。
该解决方案的另一个优点是，您可以作为默认用户登录并使用系统的 GUI 来修改默认值，而不必通过数据库访问工具来处理数据。根据您商店的政策，程序员直接访问数据表可能很困难或不可能。使用经过测试/批准的 GUI 来修改默认值可以消除大量繁文缛节，并防止对数据造成某些类型的意外损坏。
底线：主键很重要。在事务系统中，每个表都应该至少有一个唯一索引，其中之一应该是主键。您始终可以通过向每个表添加代理（自动增量）键来强制执行此操作。即使您这样做，您通常仍然希望尽可能使用自然唯一索引。您通常可以通过这种方式在表格中查找所需内容。
在用户表中创建默认用户条目并不是作弊或黑客行为，而是按照预期的方式使用表结构，并且它允许您添加可用的唯一约束基于用户 ID 和配置文件 ID 的组合，无论您是否使用代理键发明了附加的、任意的唯一约束。
Dmitry's answer is a good one, but since your case involves what is essentially an intersection table, there is another good way to solve this. For your situation I also like the idea of creating a default user profile that you can use in your code to establish default settings. This is good because it keeps your data model clean without introducing extra candidate keys. You would need to be clear in this dummy/default profile that this is what it is. You can give it a clear name like "Default User" and make sure that nobody but the administrator has access to the user credentials.
One other advantage of this solution is that you can sign on as the default user and use your system's GUI to modify the defaults rather than having to fiddle with the data through DB access tools. Depending on the policies in your shop, direct access to the data tables by programmers may be hard or impossible. Using the tested/approved GUIs for modifying defaults removes a lot of red tape and prevents some kinds of accidental damage to the data.
Bottom Line: Primary keys are important. In a transactional system every table should have a at least one unique index one of which should be the primary key. You can always enforce this by adding a surrogate (auto increment) key to every table. Even if you do, you still generally want a natural unique index whenever possible. This is how you will generally find what you're looking for in a table.
Creating a Default User entry in your user table isn't a cheat or a hack, it's using your table structure the way it's meant to be used and it allows you to put a usable unique contraint on the combination of user ID and profile ID, regardless of whether you invent an additional, arbitrary unique constraint with a surrogate key.
这是
UNIQUE约束在NULL列上的正常行为。它允许一行数据具有NULL值。然而，这不是我们想要的本专栏的行为。我们希望该列接受唯一值并接受多个 NULL 值。这可以通过使用计算列并向计算列添加约束而不是默认空值来实现。
请参阅下面的文章将在这方面为您提供更多帮助：
具有多个 NULL 值的唯一列
This is the normal behaviour of
UNIQUEconstrain on aNULLcolumn. It allows one row of data withNULLvalues. However, that is not the behaviour we want for this column. We want the column to accept unique values and also accept multiple NULL values.This can be achieved using a computed column and adding a contraint to the computed column instead default null value.
Refer below article will help you more in this matter:
UNIQUE Column with multiple NULL values
我总是总是在表上使用主自动增量键，即使它是多余的；它只是为我提供了一种非常简单的方法来识别我想要稍后访问或在其他地方引用的记录。我知道它并不能直接回答你的问题，但它确实使主键情况变得更简单。
然后创建一个唯一的二级索引 UserProfileIDX(UserID, ProfileID)，但不是主键。
I always always always use a primary auto_increment key on a table, even if its redundant; it just gives me a fantastically simple way to identify a record I want to access later or refer to elsewhere. I know it doesn't directly answer your question, but it does make the primary key situation simpler.
Then create a secondary index UserProfileIDX(UserID, ProfileID) that's unique, but not the primary key.