Access 2010多列索引中不允许多个Null值 [英] Not allowing multiple Null values in an Access 2010 multi-column Index

问题描述

我正在尝试在 Access 2010 中创建一个表，该表不允许在两个字段中重复，但在其中一个字段中允许空值，前提是只有一个空值（所以没有重复值/ null）。

我的表格字段如下， ID 字段设置为主键，计划不允许 CostCode / TeamID 中的重复项，但是 TeamID 对于 CostCode <的每个实例可以为Null一次/ code>。

图片下面显示我不能添加 CostCode 和 TeamID 两次，如果它们都有值，但我可以添加一个 CostCode 在 TeamID 中使用Null值两次。

无论如何要实现这一目标吗？< br>
我读过我可以给TeamID一个空字符串的默认值（或0，因为它永远不会是TeamID）但我想尽可能使用Null，因为那是空字符串或0表示。

编辑：

在JJ32的评论和周末之后通过我的思考将TeamID值放入一个单独的表中。

然后我会在 tbl_BranchDetail 和 tbl_CostCodes 以及在$ code> tbl_CostCodeM2MJoin 和 tbl_Teams 之间的多人多人联接。

这将删除在Many-2-Many表中出现的Null值，我的查询现在将读作：

  SELECT M2M.BranchID 
，M2M.CostCodeID 
，TM2M.TeamID 
，CC.CostCode 
，TM.TeamName 
 FROM（（tbl_CostCodes CC INNER JOIN tbl_CostCodeM2MJoin M2M ON CC.ID = M2M.CostCodeID）
 LEFT JOIN tbl_CostCodeToTeamM2MJoin TM2M ON（M2M.BranchID = TM2M.BranchID AND 
 M2M.CostCodeID = TM2M.CostCodeID）） 
 LEFT JOIN tbl_Teams TM ON TM2M.TeamID = TM.ID 
  

解决方案

我认为不可能在唯一的复合索引中禁止重复的空值，因为没有两个Null被认为是相等的。

所以在你的例子中上面你有三个独特的行，一个有一个TBC / 1和2的组合与TBC / null的组合。

遗憾的是，我知道的唯一答案是选择一些非空值来表示TeamID中的null，然后在应用程序中将结果显示为空。 / p>

I'm trying to create a table in Access 2010 which will not allow duplicates in two fields, but will allow nulls in one of those fields providing there is only a single null value (so no duplication of value/null).

My table fields are as below with the ID field set as a Primary Key and the plan is to not allow duplicates in CostCode/TeamID but TeamID can be Null once for each instance of a CostCode.

The picture below shows that I can't add a CostCode and TeamID twice if they both have values, but I can add a CostCode twice with Null values in TeamID.

Is there anyway to achieve this?
I've read I could give TeamID a default value of an empty string (or 0 as that will never be a TeamID) but I'd like to use Null if possible as that is what the empty string or 0 would represent.

EDIT:
After the comment from JJ32 and a weekend to think it through I've gone with putting the TeamID value into a separate table.
I would then have a Many-2-Many join between tbl_BranchDetail and tbl_CostCodes and a Many-2-Many join between tbl_CostCodeM2MJoin and tbl_Teams.

This will remove Null values from occurring in either Many-2-Many table and my query will now read as:

SELECT       M2M.BranchID
            ,M2M.CostCodeID
            ,TM2M.TeamID
            ,CC.CostCode
            ,TM.TeamName
FROM        ((tbl_CostCodes CC INNER JOIN tbl_CostCodeM2MJoin M2M ON CC.ID = M2M.CostCodeID)
                               LEFT JOIN tbl_CostCodeToTeamM2MJoin TM2M ON (M2M.BranchID = TM2M.BranchID AND
                                                                           M2M.CostCodeID = TM2M.CostCodeID))
                               LEFT JOIN tbl_Teams TM ON TM2M.TeamID = TM.ID     

解决方案

I don't believe it is possible to disallow duplicate nulls in a unique composite index since no two Nulls are ever considered equal.

So in your example above you'd have three unique rows, one with a combination of TBC/1 and two with a combination of TBC/null.

The only answer I know of, unfortunately, is to choose some non-null value to represent null in TeamID, and then display the result as empty within the application.

这篇关于Access 2010多列索引中不允许多个Null值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！