Início / dba / Perguntas / 9759
Accepted
Manuel Leduc
Asked: 2011-12-28 01:10:21 +0800 CST

Restrição exclusiva de várias colunas do PostgreSQL e valores NULL

  • 772

Tenho uma tabela assim:

create table my_table (
    id   int8 not null,
    id_A int8 not null,
    id_B int8 not null,
    id_C int8 null,
    constraint pk_my_table primary key (id),
    constraint u_constrainte unique (id_A, id_B, id_C)
);

E eu quero (id_A, id_B, id_C)ser distinto em qualquer situação. Portanto, as duas inserções a seguir devem resultar em um erro:

INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);

Mas não se comporta como esperado pois de acordo com a documentação, dois NULLvalores não são comparados entre si, então ambas as inserções passam sem erro.

Como posso garantir minha restrição exclusiva, mesmo que id_Cpossa ser NULLnesse caso? Na verdade, a verdadeira questão é: posso garantir esse tipo de exclusividade em "sql puro" ou tenho que implementá-lo em um nível superior (java no meu caso)?

postgresql constraint

3 respostas

  1. Best Answer

    Postgres 15

    Isso funciona fora da caixa com NULLS NOT DISTINCT:

    ALTER TABLE my_table
  DROP CONSTRAINT IF EXISTS u_constrainte
, ADD CONSTRAINT u_constrainte UNIQUE NULLS NOT DISTINCT (id_A, id_B, id_C);

    Ver:

    Postgres 14 ou mais antigo (resposta original)

    Você pode fazer isso em SQL puro . Crie um índice exclusivo parcial além do que você tem:

    CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;

    Desta forma, você pode inserir (id_A, id_B, id_C)na sua tabela:

    (1, 2, 1)
(1, 2, 2)
(1, 2, NULL)

    Mas nada disso uma segunda vez.

    Ou use dois índices parciais UNIQUEe nenhum índice completo (ou restrição). A melhor solução depende dos detalhes de suas necessidades. Comparar:

    Embora isso seja elegante e eficiente para uma única coluna anulável no UNIQUEíndice, fica fora de controle rapidamente para mais de uma. Discutindo isso - e como usar o UPSERT com índices parciais:

    Apartes

    Não há uso de identificadores de maiúsculas e minúsculas sem aspas duplas no PostgreSQL.

    Você pode considerar uma serialcoluna como chave primária ou uma IDENTITYcoluna no Postgres 10 ou posterior. Relacionado:

    Então:

    CREATE TABLE my_table (
   my_table_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- for pg 10+
-- my_table_id bigserial PRIMARY KEY  -- for pg 9.6 or older
 , id_a int8 NOT NULL
 , id_b int8 NOT NULL
 , id_c int8
 , CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
);

    Se você não espera mais de 2 bilhões de linhas (> 2147483647) durante o tempo de vida de sua tabela (incluindo linhas perdidas e excluídas), considere integer(4 bytes) em vez de bigint(8 bytes).

    • 155

  2. Eu tive o mesmo problema e encontrei outra maneira de ter NULL exclusivo na tabela.

    CREATE UNIQUE INDEX index_name ON table_name( COALESCE( foreign_key_field, -1) )

    No meu caso, o campo foreign_key_fieldé um inteiro positivo e nunca será -1.

    Então, para responder ao Manual Leduc, outra solução poderia ser

    CREATE UNIQUE INDEX  u_constrainte (COALESCE(id_a, -1), COALESCE(id_b,-1),COALESCE(id_c, -1) )

    Presumo que os ids não sejam -1.

    Qual é a vantagem de criar um índice parcial?
    Caso você não tenha a cláusula NOT NULL, id_a, id_be id_cpode ser NULL juntos apenas uma vez.
    Com um índice parcial, os 3 campos podem ser NULL mais de uma vez.

    • 18

  3. Um Null pode significar que o valor não é conhecido para essa linha no momento, mas será adicionado, quando conhecido, no futuro (exemplo FinishDatepara um running Project) ou que nenhum valor pode ser aplicado para essa linha (exemplo EscapeVelocitypara um buraco negro Star).

    Na minha opinião, geralmente é melhor normalizar as tabelas eliminando todos os Nulls.

    No seu caso, você deseja permitir NULLsem sua coluna, mas deseja que apenas uma NULLseja permitida. Por quê? Que tipo de relação é essa entre as duas tabelas?

    Talvez você possa simplesmente alterar a coluna NOT NULLe armazenar, em vez de NULL, um valor especial (como -1) que é conhecido por nunca aparecer. Isso resolverá o problema de restrição de exclusividade (mas pode ter outros efeitos colaterais possivelmente indesejados. Por exemplo, usar -1para significar "não conhecido / não se aplica" distorcerá qualquer soma ou cálculo médio na coluna. Ou todos esses cálculos terão que levar em conta o valor especial e ignorá-lo.)

    • 9

relate perguntas