Perguntas[unique-constraint](dba)

Isso provavelmente é de conhecimento comum para DBAs, mas no meu banco de dados estou lutando com um simples cadastro de clientes.

Os clientes podem se inscrever usando um formulário (usamos typeform ), o que significa que criamos registros de clientes sem um formulário de registro real.

O que fazer se os clientes se registrarem duas vezes? Ou quando clientes diferentes (talvez parceiros) se registram com o mesmo endereço de e-mail?

É sensato impor UNIQUErestrições, por exemplo, no endereço de e-mail, para evitar registros duplicados? Parece a melhor coisa a fazer, mas posso imaginar que isso causará problemas no futuro.

O principal motivo seria o envio de um formulário com um endereço de e-mail já no banco de dados. Se eu obtiver uma duplicata, provavelmente quero substituir os dados do formulário antigo pela resposta mais recente. No entanto, aqui corro o risco de envios "falsos" que substituem dados antigos por dados falsos. Se eu não substituir o registro antigo, posso perder as informações mais recentes.

Talvez eu esteja pensando demais na questão, mas acho difícil fazer uma escolha decisiva.

Se eu não aplicar a restrição de e-mail, há uma chance de ocorrerem registros duplicados quando o mesmo cliente preencher o formulário duas vezes (isso acontece ocasionalmente). Isso é super chato, pois agora a equipe tem que lidar com dois registros e não sabe qual é o correto.

Existe uma boa maneira de lidar com esse problema? Ou é melhor simplesmente escolher um e lidar com as consequências?

Dada uma tabela (postgres),

CREATE TABLE links (
    id bigint NOT NULL,
    url text NOT NULL,
    owner_id bigint NOT NULL
);

Eu gostaria de permitir que várias linhas tenham o mesmo urlse e somente se elas também tiverem o mesmo owner_id.

Ou seja, isso seria permitido:

 id | url | owner_id
----+-----+---------
  7 | foo |        1
  8 | bar |        1
  9 | bar |        1

mas isso não seria:

 id | url | owner_id
----+-----+---------
  7 | foo |        1
  8 | bar |        1
  9 | bar |        2

Existe alguma maneira de impor tal restrição?

Eu tenho uma tabela que requer armazenar meu item em uma posição específica, e o usuário pode "mover" as posições dos itens para "dar espaço" para o novo item.

Aqui está a aparência da mesa

CREATE TABLE   keys (
    key_name   VARCHAR(128) UNIQUE NOT NULL PRIMARY KEY,
    context    VARCHAR(128) NOT NULL,
    position   INTEGER      NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    UNIQUE (context, position)
);

INSERT INTO keys (key_name, context, position)
VALUES
('A.1', 'ctx_A', 0), 
('A.2', 'ctx_A', 1), 
('A.3', 'ctx_A', 2), 
('A.4', 'ctx_A', 3),
('B.1', 'ctx_B', 0), 
('B.2', 'ctx_B', 1), 
('B.3', 'ctx_B', 2), 
('B.4', 'ctx_B', 3);

Eu gostaria de poder inserir uma chave na posição 1 ou mover a chave positionde uma ( UNIQUE), e isso incrementa automaticamente o positioninteiro em 1 para cada valor maior que o novo INSERT.

Aqui está o que eu tentei até agora

UPDATE keys
SET position = position + 1
WHERE context = 'ctx_A' AND position >= 2;

ERRO: valor de chave duplicado viola restrição exclusiva "keys_context_position_key" DETALHE: A chave (context, "position")=(ctx_A, 3) já existe.

Mas não está funcionando.

EDITAR

Estou usando uma imagem do Dockerpostgres:12.7

Eu descobri que usar UNIQUE (context, position) DEFERRABLEme permite executar

UPDATE keys SET position = position + 1 WHERE context = 'ctx_A' AND position > 1;

Mas algo como

BEGIN;
  UPDATE keys SET position=2 WHERE context='ctx_A' AND key_name='A.4';
  UPDATE keys SET position=3 WHERE context='ctx_A' AND key_name='A.3';
COMMIT;

ainda não está funcionando!

Tenho as entidades "Estudante" e "Curso" e estão ligadas a uma relação muitos-para-muitos chamada "Exame". A relação "Exame" tem um atributo "Grade". Para este modelo ER, fiz um esquema relacional:

Student(StudentID, StudentName, DoB)
Course(CourseID, CourseName)
Exam(StudentID, CourseID, Grade)

Alguém pode explicar como você colocaria em um esquema relacional o fato de que um aluno não pode obter várias notas para um único curso? Por exemplo, não podemos ter registros como (Student1, Course1, Grade1)e (Student1, Course1, Grade2).

Tenho uma tabela que salva as informações necessárias para enviar notificações aos usuários. agora as colunas UID nesta tabela são quase únicas porque na maioria das vezes cada usuário se inscreve com um dispositivo. mas é possível ter várias linhas com o mesmo UID, de modo que o índice exclusivo está fora de imagem e, devido à natureza quase exclusiva dos dados, também não acho que o índice usual seja útil, pois levará quase o mesmo tempo para pesquisar o índice como toda a tabela .

Atualmente a solução que me vem à mente é salvar o código primário de cada linha da tabela de notificações em uma coluna da tabela Users em formato separado por vírgulas.

Tenho a seguinte tabela:

mytable
---------
id Serial PK
action Integer
model VARCHAR
model_id integer
active BOOL

Mas devido à minha lógica não pode haver valores duplicados com os mesmos valores para model,model_id,actionquando active for true.

Portanto, coloco uma restrição exclusiva:

ALTER TABLE mytable ADD CONSTRAINT constraint_name UNIQUE (model,model_id,action,active);

Mas isso não me impede de ter a restrição exclusiva apenas quando active=true. Além disso, minha tabela é pré-preenchida com valores duplicados, portanto, quero definir active=false em dduplicate e manter apenas um registro com value=truevocê sabe alguma boa idéia de como posso fazer isso além de apenas escrever uma instrução de atualização antes de aplicar a restrição ?

Meu trabalho

• Uma chave primária é única e contém apenas uma para uma tabela.

• Existem muitas chaves exclusivas que podem conter em uma tabela.

• No SQL Server, a chave primária não é anulável. Mas, uma chave exclusiva pode conter apenas um valor nulo.

Minha pergunta:

Praticamente no SQL Server, a chave primária não é anulável. Mas quando se trata de teoria, por que não podemos ter exatamente apenas um valor Null na chave primária quando permite um valor Null na chave exclusiva?

Obrigado por responder minha pergunta

Desculpe por fazer uma pergunta geral, depois de ler a resposta, tive uma ideia clara sobre valores nulos no banco de dados.

Além disso, confundi com linguagens de programação como em C++, Jave, onde o valor nulo pode ser comparável. Depois de fazer algum trabalho, entendo que Null não é comparável em SQL.

Em linguagens de programação como em C++, Jave...

Null==Null 

retorna VERDADEIRO

Mas, em SQL

Null==Null 

retorna FALSE, porque o valor Null é desconhecido e pode ser qualquer valor.

Portanto, atualmente temos o seguinte:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: DELETED (INTEGER)
CHECK: DELETED = 0 OR DELETED = 1

Agora, há o requisito de permitir infinitos registros excluídos, mas permitir apenas um único registro não excluído para cada data + empresa.

Eu sugeri mudar o esquema para:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: ACTIVE (Nullable INTEGER)
CHECK: ACTIVE = 1 OR ACTIVE IS NULL
UNIQUE: START, COMPANYID, ACTIVE

Enquanto meu colega de trabalho é da opinião de que isso está "indo longe demais com restrições" e que devemos confiar apenas na verificação de exclusividade no aplicativo.

Existe uma prática recomendada geralmente aceita aqui?

Alguém disse que é preferível criar suas consultas para evitar exceções de chave duplicadas, mas não estou convencido de que seja mais eficiente do que apenas definir IGNORE_DUP_KEY = ONo índice.

Meu objetivo é garantir que uma linha ou conjunto de linhas exista para um ou mais usuários antes de tentar atualizar essas linhas. Eu faço isso para que, quando tento atualizar a linha com uma instrução de atualização como a abaixo, e nenhuma linha seja afetada, é porque a [Count]parte do predicado não foi satisfeita, ao contrário da linha que não existe (ou seja, a [ID]parte do predicado não sendo satisfeita):

UPDATE [Inventory]
SET [Count] = [Count] + 1
WHERE [ID] = 3 
AND ([Count] + 1) <= @MaxInventory

Eu poderia executar EXISTS(SELECT 1 From [Inventory] WHERE [ID] = 3para verificar essa única linha e inserir a linha apenas se ela não existir. Isso simplesmente evita inserções desnecessárias. A inserção, se necessário, ainda teria que lidar com transações simultâneas, portanto, exceções de chave duplicada ainda podem ocorrer.

Estou curioso para saber se é mais eficiente apenas ativar IGNORE_DUP_KEYneste cenário, em vez de permitir que o erro seja lançado e capturado. Especificamente, estou curioso para saber se é tão rápido ou possivelmente ainda mais rápido do que executar uma verificação existente, apenas para tentar inserir o registro e deixá-lo ignorar chaves duplicadas.

Isso se torna ainda mais importante quando estou verificando e inicializando vários registros ao mesmo tempo. Por exemplo, se eu precisar garantir que os registros de milhares de usuários existam em uma única instrução de atualização, a lógica seria muito mais simples se eu apenas executasse essa instrução de inserção antecipadamente, permitindo que ela ignorasse chaves duplicadas. Evitar duplicatas seria mais complexo, porque eu teria que primeiro consultar a tabela para a qual os registros não existem e, em seguida, tentar adicionar apenas esses registros (novamente, ignorando as chaves duplicadas). Apenas inserir pode ser mais rápido, mesmo que todos os registros existam.

Eu poderia encontrá-lo no meio do caminho e verificar se algum dos registros está faltando, como com uma junção à esquerda ou uma COUNTcomparação, mas por que se preocupar se a inserção ignorando chaves duplicadas é apenas mais rápida?

É uma boa ideia usar IGNORE_DUP_KEYe apenas tentar inserções em vez de se preocupar em verificar a existência de linhas antes do tempo? Se não, por quê?

Em um sistema de produção no SQL Server todos os ids (principalmente PKs) em todas as tabelas são gerados automaticamente e sou informado que eles são únicos globalmente. Quero dizer, não há 2 ids iguais no banco de dados, mesmo que as tabelas sejam diferentes. Eu quero saber como isso pode ser feito? Se houver várias maneiras, liste todas. Obrigado.