Início / dba / Perguntas / 132776
Accepted
Nishant
Asked: 2016-03-21 00:53:03 +0800 CST

Chave estrangeira com restrições adicionais?

  • 772

Existe uma tabela chamada Item(id, name, cost)e Orders(id, bill_id, item_id, units), que é criada para rastrear pedidos feitos, onde mesmo bill_id significa que pertence a um único pedido.

Como impor uma restrição adicional no banco de dados que diz que o item deve estar "disponível" (naquele momento) se precisar ser adicionado como item_id na tabela Order ? Um item é determinado manualmente como "Disponível" e não pode ser derivado de outros campos no banco de dados neste cenário.

Um design de esquema (eu prefiro) é adicionar uma coluna Tipo que teria os campos "Disponível" e "Indisponível". Mas como posso verificar se a restrição de chave estrangeira item_id não deve ser apenas uma chave primária na Itemtabela, seu tipo também deve ser "disponível"?

Esta resposta Stack Overflow usando restrições de verificação parece próxima, mas é a única maneira? Eu sinto que isso é uma coisa trivial para RDBMS, ou isso não é um dado normalizado?

O outro desenho do esquema (eu não prefiro) é ter uma Tabela chamada "Menu" que poderia ter apenas os Itens Disponíveis. O problema é que essa tabela é muito dinâmica por natureza e muda dependendo da disponibilidade de itens. E estou apenas criando uma tabela de subconjunto de itens, dependendo de seu estado, o que não parece ser uma boa ideia.

É fácil fazer isso programaticamente; no entanto, como faço para conseguir isso no RDBMS? Eu gosto da ideia do banco de dados ser inteligente o suficiente para lidar com isso.

database-design postgresql

1 respostas

  1. Best Answer

    A resposta original está errada! Veja Editar 1 para a versão corrigida.

    Resposta original

    Uma solução incrível exigiria uma chave estrangeira para uma coluna em uma exibição ou uma tabela herdada, mas infelizmente o PostgreSQL (suponho que seja o seu RDBMS por causa da tag) não possui isso (ainda).

    Acho que bastaria uma simples mudança na forma como você organiza os dados: crie uma tabela como ItemsAvailableQuantity , conectando um Item com sua disponibilidade que serão referências nos pedidos. Quando um item não está mais disponível, DELETEdele.

    CREATE TABLE Item (
    id   serial  NOT NULL
  , name text    NOT NULL
  , cost numeric

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
    );

CREATE TABLE ItemAvailableQuantity (
    id       serial  NOT NULL
  , item_id  integer NOT NULL
  , quantity integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES Item (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
  , CONSTRAINT postive_quantity -- This constraint is the same as
        CHECK (quantity > 0)    -- checking something like `available = TRUE`.
    );

CREATE TABLE ItemOrder (       -- Changed the name from `Order` because
    id      serial  NOT NULL   -- PostgreSQL refuses that name, somehow
  , bill_id integer NOT NULL
  , item_id integer NOT NULL
  , units   integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES ItemAvailableQuantity (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
-- Uncomment when `Bill` table is ready
--  , FOREIGN KEY (bill_id)
--        REFERENCES Bill (id)
--        ON UPDATE CASCADE
--        ON DELETE CASCADE
  , CONSTRAINT positive_units
        CHECK (units > 0)
    );

    Perceber! A restrição Positive_units pode causar problemas quando seu software reduz as unidades e atinge 0. Faça algo parecido CHECK >= 0, se necessário, ou adicione um gatilho que automaticamente DELETE-s linhas quando as unidades atingirem 0 (ou menos) em cada INSERTou UPDATE. Isso preservaria a tabela ItemAvailableQuantity para ter apenas itens realmente disponíveis, que é o que queremos para ser referenciado na tabela ItemOrder .

    Isso deve resolver seu problema. Não é uma resposta exata para sua pergunta. Isso envolveria um gatilho ou a CHECKchamada de uma função como no link que você forneceu.

    Para visualizar facilmente a quantidade dos itens então, basta criar uma view que una ItemAvailableQuantity e Item . Se você realmente quiser, torne-o INSERTcapaz com um gatilho (veja o aviso da caixa amarela) .

    Editar 1

    Na verdade , Order (também conhecido como ItemOrder ) deve fazer referência ao Item em vez de ItemAvailableQuantity para evitar qualquer problema quando o Item não estiver disponível no momento, conforme declarado no comentário.

    Isso sugere que devemos remover toda a tabela ItemAvailableQuantity e adicionar apenas uma coluna available_quantity em Item .

    CREATE TABLE Item (
    id                 serial NOT NULL
  , name               text   NOT NULL
  , cost               numeric
  , available_quantity integer NOT NULL

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
  , CONSTRAINT non_negative_quantity
        CHECK (quantity >= 0)
    );

    Então, para ter certeza de inserir apenas itens disponíveis em pedidos, poderíamos simplesmente executar

    INSERT INTO ItemOrder (bill_id, item_id, units) VALUES
    (SELECT id FROM Bill WHERE condition = something -- customize at will
    , SELECT id FROM Item WHERE available_quantity >= wanted_quantity
        AND other_condition = something
    , wanted_quantity)
    ;

    onde wanted_quantityé um parâmetro passado pelo seu software para a consulta.

    Ainda assim, resolve o problema, mas não é uma resposta direta à pergunta.

    • 3

relate perguntas