主页 / dba / 问题 / 141241
Accepted
Martin F
Asked: 2016-06-15 13:53:59 +0800 CST

如何允许键的一部分为空并强制使用空值的记录的唯一性？

  • 772

我有一个Engagement包含四个字段的 Access 表：

Emp_id, Year, Week, Act_id

它记录员工何时/正在从事某项活动。每个字段都是数字，每个字段都构成复合主键的一部分。该应用程序的语义是每个条目（每个Emp_id- Year- Week-Act_id组合）必须是唯一的。也就是说，虽然员工通常会有不同的年周和不同的活动，但员工有时可以在不同的年周从事相同的活动，甚至在同一年的周内从事不同的活动。不允许使用空值。一切正常。

现在我需要扩展/修改语义，以允许与任何给定员工活动相关联的未知（或者更恰当地说，未公开）年-周值。当然，尝试输入带有空 Year-Week 的行会导致“索引或主键不能包含 Null 值”。所以我需要改变桌子的设计。

我尝试的一件事是通过在“索引”窗口中关闭主键（并保持唯一键打开）将主键索引转换为非主索引。这可以正确防止 Year-Week 值为非空的重复记录 - 但它允许 Year-Week 为空的重复记录。

例如，使用上述非主唯一索引，允许以下数据：

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Same activity,
     7  2015    22      31  } different dates.
     7  2015    33      32
     7  2015    40      33  } Same dates,
     7  2015    40      34  } different activities.
     7  2016     2      36
     7                  38  } Different activities,
     7                  39  } undisclosed dates.

随后不允许进行以下任何添加：

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Both records are 
     7  2015    33      32  } duplicates of above.

到目前为止一切顺利（行为符合要求）。但是，随后允许但不应该允许以下两种添加：

Emp_id  Year  Week  Act_id
     7                  38  } Both records are
     7                  39  } duplicates of above.

这是为什么？

有什么好的方法可以解决这个问题，一方面允许 Year 和 Week 等效于 null ，另一方面限制每个Emp_id- Year- Week-Act_id组合是唯一的？

我可以想到另外两个（未尝试过的）解决方案：

  1. 选择一个等效于空的数值，例如零或 -1，并以某种方式向用户解释这一点。
  2. 将 Year-Week 字段类型从 Number 转换为 Text 并简单地使用 ""（空字符串）作为 null。

根据您的经验，在这种情况下有什么好的解决方案？

我知道复合主键中的可为空列有什么问题？和多列主键中的 NULL 值，它解释了某些事情，但不提供解决方案。

ms-access primary-key

5 个回答

  1. 我能给出的最有力的建议是保持主键不可变，除非它对您的用例绝对不可行。您似乎在描述 2 个不同的数据集。

    1. User-Actions在给定 Period期间拍摄（由 Year-Week 的复合键定义）
    2. User-Actions在未知期间拍摄 Period

    我最初的印象是，未知时期必须是不同的数据集。当然，如果您不知道某项操作何时发生，您也还没有足够了解将其从暂存环境移动到User-Action-Period唯一标识符所在的表中。我不太了解 MS Access，无法评论该环境中 null 键控的细节，但我强烈建议您自己定义数据模型，以便您知道是否需要包含“等效于 null "同一张表上的键控记录。

    根据您的描述，似乎是在尝试插入发生的未知的多个实例User-Action时Period出现问题。再一次，如果您没有足够的知识来唯一地识别 的提交时间，则适合在其他地方User-Action暂存您正在记录的指标，直到User-Action您可以适当地识别这与在另一个键上发生User-Action的其他类似情况之间的关系User-Actions-能够时间Period。

    我看到了两条前进的道路：

    如果只允许一个具有“未知”时间键的用户操作

    示例：该操作是“待处理”或“待完成”。用户将始终提交未来/未完成期间的数据。

    解决方案：选择您最喜欢的将时间段键入为“等效于空”的方法，并使键列不可为空。您可以继续将这些数据集存储在同一个表中

    Gotcha：如果用户提交了一个有承诺期限的动作，而同一期限存在未知期限User-Action，您可能会遇到这样一种情况，即在已知期限的情况下提交了数据，但其他数据已经提交了未知期限（恰好是同一个 ) 处于“未知期间”状态并丢失该User-Action-Time记录，用户不会立即知道原因。

    允许具有“未知”时间键的多个用户操作

    示例：用户能够在未知期间使用不同标识符唯一标识相同的操作类型。

    解决方案：您必须定义另一种方法来键入数据（其中操作发生的时间是另一个多键记录的度量）。允许用户将数据提交到这个单独存储的数据集，并User-Action-Time使用适当的逻辑将其合并到您的周期数据集中。这是更多的工作，但如果数据集是真正分开的，就没有办法解决它。

    • 6

  2. 您可以使用超出范围的默认日期。例如在表定义中YEAR INTEGER DEFAULT 1776, WEEK INTEGER DEFAULT 99

    这样你就知道所有的东西都是用某个日期输入的。它将继续不允许您输入重复数据。

    此外，您可以根据这些值创建一个异常报告，让某人知道他们需要将此日期更新为正确的日期。

    • 1

  3. 此解决方案仅适用于 Access 2010+（例如 2013）并且仅当文件保存为 accdb （此解决方案不适用于 MDB，即使在 Access 2010 中）。您可能还必须在已保存的文档上启用宏才能使检查实际...您知道...工作。

    像以前一样设置唯一索引，请记住，如果您有任何空值，它实际上不会是唯一的，直到该解决方案的其余部分付诸实施。用于此的 SQL 代码将是

    CREATE UNIQUE INDEX uidx_engagement
  ON Engagement (Emp_id ASC,  Act_id ASC, Year ASC, Week ASC)

    在表格的数据表视图中，单击上方功能区中的下方Table。Table Tools单击功能区Before Change的Before Events部分。

    第一个动作应该是

    SetLocalVar
Name    vm
Expression     = ValidateMe()

    第二个动作应该是

    If    Not [vm]   Then

    这应该创建一个If..End If块

    在If块的中间，创建另一个语句。我相信实际的错误号并不重要。

    RaiseError
Error Number   1
Error Description   Duplicate Record Entered

    然后保存并关闭Before Change宏调用。

    现在您需要创建 VBA 代码。单击Database Tools功能区。在该Macro部分中，单击Visual Basic

    在打开的左上角面板中，您应该会看到一个文件树。这些对象之一应该是您的数据库的条目。右键单击名称，然后选择Insert-> Module。然后在打开的主窗口中，粘贴以下内容。

    Option Compare Database

Public Function ValidateMe() As Boolean
'idea from http://www.utteraccess.com/forum/lofiversion/index.php/t1965349.html'
    Dim tabl As Object
    Dim my As Object
    Dim tablName As String
    Dim str As String
    Set tabl = Application.CurrentData.AllTables(Application.CurrentObjectName)
    Set my = Application.Screen.ActiveDatasheet.Controls
    tablName = tabl.Name
    str = "Emp_id=" & my!Emp_id & " AND Act_id=" & my!Act_id & " AND " & IIf(IsNull(my!Year), "ISNULL(Year)", "Year=" & my!Year) & " AND " & IIf(IsNull(my!Week), "ISNULL(Week)", "Week=" & my!Week) 'need "'"s if non-numeric data types'
    If DCount("[Emp_id]", tabl.Name, str) > 0 Then ValidateMe = False Else ValidateMe = True
End Function

    保存此模块和一般的 VBA 代码。我将我的模块命名为“Validator”，但不管你怎么称呼它……你可能还必须在Debug菜单下编译模块。

    有关先前尝试解决方案的信息，请参阅此线程的编辑历史记录。呸。

    • 1
  4. Best Answer

    解决方案的第一部分 - 正如我在问题中所说 - 通过在“索引”窗口中关闭主键（并保持唯一键打开）将主键索引转换为非主索引。

    正如@mpag 所建议的，创建此类索引的另一种方法是通过 SQL：

    CREATE UNIQUE INDEX uidx_engagement ON Engagement (Emp_id, Year, Week, Act_id)

    解决方案的第二部分是注意到我已经在用户输入或删除参与数据的表单的代码中使用了参数化 SQL 查询：get_engagement

    SELECT * FROM Engagement WHERE
Emp_id = e AND Act_id = a AND Year = y AND Week = w

    然后对其进行修改，使其可以处理空情况：

    SELECT * FROM Engagement WHERE
Emp_id = e AND Act_id = a
AND IIF (IsNull(y), IsNull(Year), Year = y)
AND IIF (IsNull(w), IsNull(Weak), Weak = w)

    正是@mpag 的验证代码想法让我走上了正确的道路。

    • 1

  5. 你可以做很多事情，但整数和文本的排序不同。

    文本会在 > 6 周中断搜索。
    在文本 10、11、12 中不 > 6。

    我会用 0 表示没有日期

    不是那个大小是什么大不了的，但你会使用 tinyint 一周和 smallint 一年

    一个 varchar 是 smallint 的大小

    • 0

相关问题