Início / dba / Perguntas / 333977
Accepted
ceving
Asked: 2023-12-12 19:03:24 +0800 CST

Como fazer uma chave estrangeira verificando valores NULL?

  • 772

Tenho uma tabela especificando tipos para distinguir entre escalares e listas. Quando existe "escalar" o valor é um escalar e quando é nulo é uma lista.

drop table type1 cascade;
create table type1 (
  id     integer primary key,
  scalar "char",
  name   text,
  unique (id, scalar)
);
insert into type1 values
  (1, 'x', 'single'),
  (2, null, 'multi');

Agora tenho uma tabela com valores, que faz referência ao tipo e adiciona uma restrição exclusiva à tupla (tipo, escalar). Isso funciona porque NULL é distinto. Se "escalar" for NULL é possível ter mais de uma linha por "tipo", pois todos os "escalares" são distintos.

drop table value1 cascade;
create table value1 (
  id     integer primary key,
  type   integer,
  scalar "char",
  value  text,
  foreign key (type, scalar) references type1 (id, scalar),
  unique (type, scalar)
);

É possível inserir um único valor:

insert into value1 values
  (11, 1, 'x', 'just one');

Mas não é possível adicionar outro:

insert into value1 values
  (12, 1, 'x', 'another one'); -- ERROR

Mas se o escalar for NULL, é possível ter mais de um:

insert into value1 values
  (21, 2, null, 'first'),
  (22, 2, null, 'second');

Meu problema: é possível fazer referência a tipos que não existem.

Isso funciona, embora não devesse:

insert into value1 values
  (12, 1, null, 'another');

É permitido usar (1, NULL) como tipo, embora na tabela de tipos estejam apenas (1, 'x') e (2, NULL).

Tentei usar match fulla chave estrangeira, mas parece fazer algo diferente. Impede também o uso pretendido de vários valores nulos, porque rejeita vários valores nulos.

Quero permitir vários NULLs se value1.scalarfor type1.scalarNULL. Mas quero rejeitar qualquer NULL se value1.scalarnão type1.scalarfor NULL. Como posso expressar isso?

postgresql

1 respostas

  1. Best Answer

    Você tem dois problemas separados:

    • A uniquerestrição value1é impor uma única linha por conjunto de (type, scalar), mas você deseja que os não escalares permitam múltiplos. Restrições exclusivas não permitem filtros. No entanto,
      você pode usar um índice exclusivo filtrado .....where (scalar is not null)
    • ... a outra questão é que ambos MATCH SIMPLEe MATCH FULLnão fazem o que querem. Você precisa MATCH PARTIAL, mas isso ainda não foi implementado.

    Em vez disso, use um valor de sinalizador como \x0(o caractere NUL) para sinalizar um não escalar e tornar a scalarcoluna não anulável em type1.

    create table type1 (
  id     integer primary key,
  scalar "char" not null,
  name   text,
  unique (id, scalar)
);

create table value1 (
  id     integer primary key,
  type   integer,
  scalar "char",
  value  text,
  foreign key (type, scalar) references type1 (id, scalar) match full  -- or match simple (the default) if you prefer
);

create unique index ux on value1 (type, scalar) where (scalar <> '\x0');

    banco de dados<> violino

    • 2

relate perguntas