Início / dba / Perguntas / 58970
Accepted
Snowball
Asked: 2014-02-15 15:58:39 +0800 CST

Impondo restrições "a duas mesas de distância"

  • 772

Tive alguns problemas ao modelar um esquema elétrico em SQL. A estrutura que eu gostaria de capturar é

  part ←────────── pin
   ↑                ↑
part_inst ←───── pin_inst

onde "inst" é a abreviação de "instância".

Por exemplo, eu poderia ter um partamplificador operacional LM358 com pins 1OUT, 1IN-, 1IN+, GND, 2IN+, 2IN-, 2OUT e V CC . Eu poderia então colocar esta parte em um esquema, criando um part_inste 8 pin_insts.

Ignorando os campos de dados, minha tentativa inicial de um esquema foi

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial primary key,
    part_id bigint not null references parts
);
create table part_insts (
    part_inst_id bigserial primary key,
    part_id bigint not null references parts
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null references part_insts,
    pin_id bigint not null references pins
);

O principal problema com esse esquema é que a pin_instpode estar vinculado a part_instwith , part_id=1mas pintem part_id=2.

Eu gostaria de evitar esse problema no nível do banco de dados, e não no nível do aplicativo. Então, modifiquei minhas chaves primárias para impor isso. Marquei as linhas alteradas com --.

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial,                                          --
    part_id bigint not null references parts,
    primary key (pin_id, part_id)                              --
);
create table part_insts (
    part_inst_id bigserial,                                    --
    part_id bigint not null references parts,
    primary key (part_inst_id, part_id)                        --
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null,                              --
    pin_id bigint not null,                                    --
    part_id bigint not null references parts,                  --
    foreign key (part_inst_id, part_id) references part_insts, --
    foreign key (pin_id, part_id) references pins              --
);

Minha reclamação com esse método é que ele polui as chaves primárias: em todos os lugares em que me refiro a um part_inst, preciso acompanhar tanto o part_inst_idquanto o part_id. Existe outra maneira de aplicar a restrição pin_inst.part_inst.part_id = pin_inst.pin.part_idsem ser excessivamente prolixo?

database-design postgresql

1 respostas

  1. Best Answer

    Solução mínima

    Uma solução radical seria remover pin_instcompletamente:

      parte ←────────── pino
    ↑                ↑ 
part_inst ←───── pin_inst

    Não há nada em sua pergunta que sugira que você realmente precise da tabela redundante. Para pins associados a a part_inst, observe os pins do associado part.

    Isso simplificaria o código para:

    CREATE TABLE part (   -- with singular terms as table names
  part_id      bigserial PRIMARY KEY
);
CREATE TABLE pin (
  pin_id       bigserial PRIMARY KEY,
, part_id      bigint NOT NULL REFERENCES part
);
CREATE TABLE part_inst (
  part_inst_id bigserial PRIMARY KEY,
, part_id      bigint NOT NULL REFERENCES part
);

    Mas seu comentário deixou claro que não vamos nos safar disso...

    Alternativa se pin_instfor necessário

    Você não pode referenciar uma tabela “a duas tabelas de distância” com restrições de chave estrangeira . Incluir part_idcomo você fez é a solução mais simples com restrições de chave estrangeira.

    Mas você pode pelo menos se virar sem "poluir" as chaves primárias. Adicione UNIQUErestrições .

    (Atualizado com sintaxe moderna para Postgres 14.)

    parte CREATE TABLE (
  part_id bigint GERADO SEMPRE COMO CHAVE PRIMÁRIA DE IDENTIDADE
);

pino CREATE TABLE (
  pin_id bigint GERADO SEMPRE COMO CHAVE PRIMÁRIA DE IDENTIDADE
, part_id bigint NÃO NULO
, CONSTRAINT pin_part_id_fkey FOREIGN KEY (part_id) REFERÊNCIAS parte
, CONSTRAINT pin_fk_uni UNIQUE (part_id, pin_id)   -- somente para FK; anote o part_id inicial
);

CREATE TABLE part_inst (
  part_inst_id bigint GERADO SEMPRE COMO CHAVE PRIMÁRIA DE IDENTIDADE
, part_id bigint NÃO NULO
, CONSTRAINT part_inst_part_id_fkey FOREIGN KEY (part_id) REFERÊNCIAS parte
, CONSTRAINT part_inst_fk_uni UNIQUE (part_id, part_inst_id)   -- apenas para FK
);

CREATE TABLE pin_inst (
  pin_inst_id bigint GERADO SEMPRE COMO CHAVE PRIMÁRIA DE IDENTIDADE
, part_inst_id bigint NÃO NULO
, pin_id bigint NÃO NULO
, part_id bigint NÃO NULO
, CONSTRAINT pin_fkey        FOREIGN KEY (part_id, pin_id) REFERENCES pin (part_id, pin_id) 
, CONSTRAINT part_inst_fkey FOREIGN KEY (part_id, part_inst_id) REFERÊNCIAS part_inst (part_id, part_inst_id ) 
);

    db<>mexa aqui

    As restrições em negrito UNIQUEsão logicamente redundantes, mas necessárias como destino para as FOREIGN KEYrestrições em negrito.

    Coloquei part_idprimeiro as restrições únicas (e conseqüentemente também as restrições FK). Isso é irrelevante para a integridade referencial, mas é importante para o desempenho. As chaves primárias já implementam índices para as colunas PK. É melhor ter a outra coluna primeiro nos índices multicolunas implementando as UNIQUErestrições. Ver:

    Perguntas relacionadas no SO:

    Alternativa com gatilhos

    Você pode recorrer a funções de gatilhos, que são mais flexíveis, mas um pouco mais complicadas e propensas a erros e um pouco menos rígidas. O benefício: você poderia fazer sem part_inst.part_ide pin.part_id...

    • 15

relate perguntas