如何允許鍵的一部分為空並強制使用空值的記錄的唯一性？

我有一個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 值，它解釋了某些事情，但不提供解決方案。

我能給出的最有力的建議是保持主鍵不可變，除非它對您的案例絕對不可行。您似乎在描述 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使用適當的邏輯將其合併到您的周期數據集中。這是更多的工作，但如果數據集是真正分開的，就沒有辦法解決它。

您可以使用超出範圍的預設日期。例如在表定義中YEAR INTEGER DEFAULT 1776, WEEK INTEGER DEFAULT 99

這樣你就知道所有的東西都是用某個日期輸入的。它將繼續不允許您輸入重複數據。

此外，您可以根據這些值創建一個異常報告，讓某人知道他們需要將此日期更新為正確的日期。

引用自：https://dba.stackexchange.com/questions/141241