Como posso permitir que parte de uma chave seja nula e impor exclusividade de registros com nulos?

O conselho mais forte que posso dar é manter a chave primária imutável , a menos que seja absolutamente inviável para o seu caso de uso. Você parece estar descrevendo 2 conjuntos de dados diferentes.

1. User-Actionstomada durante um determinado Period (definido pela chave composta de Ano-Semana)
2. User-Actionstirada durante um desconhecido Period

Minha impressão inicial é que o período desconhecido deve ser um conjunto de dados diferente. Certamente, se você não sabe quando uma ação ocorreu, também não sabe o suficiente para movê-la de seu ambiente de teste para a tabela onde User-Action-Periodestá o identificador exclusivo. Não conheço o MS Access bem o suficiente para comentar sobre as especificidades da chave nula nesse ambiente, mas recomendo definir seu modelo de dados para você mesmo de forma que você saiba se precisa incluir o "equivalente a nulo " registros chaveados na mesma tabela .

Pela sua descrição, parece que o problema ocorre ao tentar inserir várias instâncias de um User-Actiononde o Periodocorrido não é conhecido . Mais uma vez, se você não souber o suficiente para identificar exclusivamente o tempo de confirmação do User-Action, é apropriado testar as métricas que você está registrando sobre o mesmo User-Actionem outro lugar até que você possa identificar adequadamente o relacionamento que isso User-Actiontem com o outro semelhante User-Actionsque ocorreu em outra chave -tempo capaz Period.

Vejo dois caminhos a seguir:

Se apenas uma ação do usuário com chave de tempo "desconhecida" for permitida

Exemplo: A ação está "pendente" ou "a ser concluída". O usuário estará sempre enviando dados para um período futuro/não concluído.

Solução: escolha seu método favorito de codificar o período de tempo como "equivalente a nulo" e torne as colunas-chave não anuláveis. Você pode continuar armazenando esses conjuntos de dados na mesma tabela

Pegadinha: se o usuário enviar uma ação com um período confirmado enquanto existe um período desconhecido para o mesmo User-Action, você pode se deparar com uma situação em que os dados são confirmados em que o período é conhecido, mas outros dados já confirmados por um período desconhecido (que por acaso é o mesmo ) é deixado no estado "período desconhecido" e perdido para esse User-Action-Timeregistro e o usuário não sabe imediatamente o motivo.

Múltiplas ações do usuário com chaves de tempo "desconhecidas" são permitidas

Exemplo: os usuários podem identificar exclusivamente o mesmo tipo de ação com um identificador diferente para um período desconhecido .

Solução: Deve-se definir outra forma de chavear os dados (onde o horário em que a ação ocorre é uma métrica do outro registro multichaveado). Permita que os usuários enviem dados para este conjunto de dados armazenado separadamente e mescle-os em seu User-Action-Timeconjunto de dados de período usando a lógica apropriada. Dá mais trabalho, mas não há como contornar isso se os conjuntos de dados estiverem separados de boa-fé.

Você pode usar uma data padrão de algo fora dos limites. por exemplo, na definição da tabelaYEAR INTEGER DEFAULT 1776, WEEK INTEGER DEFAULT 99

Dessa forma, você sabe que tudo é inserido com alguma data. Ele continuará a não permitir que você insira dados duplicados.

Além disso, você pode criar um relatório de exceção com base nesses valores iniciados para alguém, informando que precisa atualizar essa data para a correta.

Esta solução funcionará apenas no Access 2010+ (por exemplo, 2013) e somente se o arquivo for salvo como um accdb (esta solução não funciona para MDBs, mesmo no Access 2010) . Você também pode ter que habilitar macros no documento salvo para que as verificações realmente... você sabe... funcionem.

Configure o índice exclusivo como antes, tendo em mente que ele não será realmente exclusivo se você tiver valores nulos até que o restante desta solução seja colocado em ação. O código SQL para isso seria

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

Na visualização da folha de dados da tabela, clique em Tableabaixo Table Toolsna faixa de opções superior. Clique Before Changena Before Eventsseção da faixa de opções.

a primeira ação deve ser

SetLocalVar
Name    vm
Expression     = ValidateMe()

A segunda ação deve ser

If    Not [vm]   Then

Isso deve criar um If..End Ifbloco

No meio do Ifbloco, crie outra instrução. Eu acredito que o número real do erro não importa.

RaiseError
Error Number   1
Error Description   Duplicate Record Entered

Em seguida, salve e feche a Before Changechamada de macro.

Agora você precisará criar o código VBA. Clique na Database Toolsfita. Na Macroseção, cliqueVisual Basic

No painel superior esquerdo que se abre, você deve ver uma árvore de arquivos. Um desses objetos deve ser uma entrada para seu banco de dados. Clique com o botão direito do mouse no nome e selecione Insert-> Module. Em seguida, na janela principal que se abre, cole o seguinte.

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

Salve este módulo e o código VBA em geral. Chamei meu módulo de "Validador", mas não importa como você o chama... talvez seja necessário também compilar o módulo no Debugmenu.

Consulte o histórico de edição deste tópico para uma tentativa anterior de solução. Bah.

A primeira parte da solução foi - como eu disse na pergunta - converter o índice de chave primária em um índice não primário desativando Primário (e deixando Único ativado) na janela Índices.

Um meio alternativo para criar esse índice, conforme sugerido por @mpag, é via SQL:

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

A segunda parte da solução foi perceber que eu já estava utilizando uma consulta SQL parametrizada, get_engagement, no código dos meus formulários onde o usuário insere ou apaga os dados do engajamento:

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

E, em seguida, modifique-o para que ele possa lidar com os casos nulos:

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)

Foi a ideia de @mpag para o código de validação que me fez pensar no caminho certo.

Você pode fazer muitas coisas, mas inteiro e texto não são classificados da mesma forma.

O texto interromperia uma pesquisa na semana > 6.
No texto 10, 11, 12 não são > 6.

Eu usaria 0 para nenhuma data

Não que o tamanho seja grande coisa, mas você usaria tinyint para semana e smallint para ano

um varchar é do tamanho de um smallint