Início / dba / Perguntas / 345620
Accepted
Thomas Hunter II
Asked: 2025-03-07 12:55:37 +0800 CST

Aplicando uma única linha estrangeira "especial" com várias linhas regulares

  • 772

Tenho uma tabela de banco de dados de documentos e uma tabela de banco de dados de versões de documentos. Há uma relação de um para muitos entre documento e versão de documento. Estou procurando conselhos sobre como modelar exatamente qual versão de documento está "ativa" para um dado documento.

CREATE TABLE documents (
  id serial primary key,
  name varchar(250) not null
  -- active_version integer REFERENCES document_versions(id) not null
);
CREATE TABLE document_versions (
  id serial primary key,
  document integer REFERENCES documents(id) not null,
  name varchar(32) not null
  -- active boolean not null default false
);

Estou procurando uma maneira de marcar qual das entradas da versão do documento está "ativa". Comentadas no esquema estão duas maneiras de fazer isso, mas não acredito que nenhuma delas seja necessariamente ideal.

No primeiro caso, a tabela de documentos tem uma referência de volta às versões do documento. Isso força exatamente uma versão do documento a estar ativa. Suponho que haja um catch-22, então não é possível realmente impor que a active_versioncoluna seja not null. O principal problema com essa abordagem, na minha opinião, é que há uma referência circular entre as duas tabelas.

A segunda maneira de fazer isso é com uma activecoluna booleana na tabela de versões do documento. Suponho que isso esteja ok, mas não tenho certeza de como fazer uma restrição para que apenas uma linha na tabela com um determinado documentvalor de chave estrangeira tenha um valor ativo de true. Também suspeito que isso poderia impor que 0 ou 1 versão ativa do documento exista, mas não impor que exatamente 1 versão ativa do documento exista.

postgresql

2 respostas

  1. Acontece que isso pode ser implementado usando um índice parcial. Neste caso, usamos a coluna booleana active na tabela de versões do documento.

    CREATE UNIQUE INDEX one_active_document_version
  ON document_version (document, active) WHERE active;

    Leia WHERE activecomo WHERE active = truee deve fazer um pouco mais de sentido. Basicamente, é um índice que se aplica somente a um subconjunto dos dados na tabela; neste caso, rows where active é true.

    Isso impõe 0 ou 1 versão do documento, mas não exatamente 1.

    • 3
  2. Best Answer

    Sugiro que você use a chave estrangeira documentse a defina como NOT NULL.

    O problema percebido 22 é provavelmente a dificuldade de inserir linhas em tabelas que referenciam umas às outras. A solução para isso é definir uma das chaves estrangeiras — digamos, a de document_versions— como DEFERRABLE INITIALLY DEFERRED, para que a verificação de integridade seja adiada para o fim da transação. Então você pode fazer:

    BEGIN;

-- won't throw an error, because the check is deferred
INSERT INTO document_versions (document, name)
   VALUES (0, 'some name');

-- uses the last sequence-generated value in this session
INSERT INTO documents (name, active_version)
   VALUES ('some name', currval('document_versions_id_seq'));

UPDATE document_versions
SET document = currval('documents_id_seq')
WHERE id = currval('document_versions_id_seq');

-- now all foreign keys are fulfilled, and no error is thrown
COMMIT;
    • 1

relate perguntas