Início / dba / Perguntas / 17853
Accepted
ypercubeᵀᴹ
Asked: 2012-05-15 15:02:25 +0800 CST

Existem DBMS que permitem uma chave estrangeira que faz referência a uma exibição (e não apenas tabelas base)?

  • 772

Inspirado por uma questão de modelagem Django: Modelagem de banco de dados com múltiplas relações muitos-para-muitos em Django . O db-design é algo como:

CREATE TABLE Book
( BookID INT NOT NULL
, BookTitle VARCHAR(200) NOT NULL
, PRIMARY KEY (BookID)
) ;

CREATE TABLE Tag
( TagID INT NOT NULL
, TagName VARCHAR(50) NOT NULL
, PRIMARY KEY (TagID)
) ;

CREATE TABLE BookTag
( BookID INT NOT NULL
, TagID INT NOT NULL
, PRIMARY KEY (BookID, TagID)
, FOREIGN KEY (BookID)  REFERENCES Book (BookID)
, FOREIGN KEY (TagID)   REFERENCES Tag (TagID)
) ;

CREATE TABLE Aspect
( AspectID INT NOT NULL
, AspectName VARCHAR(50) NOT NULL
, PRIMARY KEY (AspectID)
) ;

CREATE TABLE TagAspect
( TagID INT NOT NULL
, AspectID INT NOT NULL
, PRIMARY KEY (TagID, AspectID) 
, FOREIGN KEY (TagID)   REFERENCES Tag (TagID)
, FOREIGN KEY (AspectID)  REFERENCES Aspect (AspectID)
) ;

diagrama db

e a questão é como definir a BookAspectRatingtabela e reforçar a integridade referencial, de modo que não se possa adicionar uma classificação para uma (Book, Aspect)combinação inválida.

CHECKAFAIK, restrições complexas (ou ASSERTIONS) que envolvem subconsultas e mais de uma tabela, que possivelmente poderiam resolver isso, não estão disponíveis em nenhum SGBD.

Outra ideia é usar (pseudocódigo) uma visão:

CREATE VIEW BookAspect_view
  AS
SELECT DISTINCT
    bt.BookId
  , ta.AspectId
FROM 
    BookTag AS bt
  JOIN 
    Tag AS t  ON t.TagID = bt.TagID
  JOIN 
    TagAspect AS ta  ON ta.TagID = bt.TagID 
WITH PRIMARY KEY (BookId, AspectId) ;

e uma tabela que possui uma Chave Estrangeira para a View acima:

CREATE TABLE BookAspectRating
( BookID INT NOT NULL
, AspectID INT NOT NULL
, PersonID INT NOT NULL
, Rating INT NOT NULL
, PRIMARY KEY (BookID, AspectID, PersonID)
, FOREIGN KEY (PersonID)   REFERENCES Person (PersonID)
, FOREIGN KEY (BookID, AspectID) 
    REFERENCES BookAspect_view (BookID, AspectID)
) ;

Três perguntas:

  • Existem DBMS que permitem um (possivelmente materializado) VIEWcom um PRIMARY KEY?

  • Existem SGBDs que permitem um FOREIGN KEYque REFERENCESum VIEW(e não apenas um base TABLE)?

  • Esse problema de integridade poderia ser resolvido de outra forma - com recursos de DBMS disponíveis?

Esclarecimento:

Já que provavelmente não existe uma solução 100% satisfatória - e a questão do Django nem é minha! - Estou mais interessado em uma estratégia geral de possível ataque ao problema, não em uma solução detalhada. Portanto, uma resposta como "no DBMS-X isso pode ser feito com triggers na tabela A" é perfeitamente aceitável.

database-design foreign-key

5 respostas

  1. No Oracle, uma maneira de impor esse tipo de restrição de maneira declarativa seria criar uma visão materializada que é configurada para atualizar rapidamente na confirmação cuja consulta identifica todas as linhas inválidas (ou seja, BookAspectRatinglinhas que não correspondem a BookAspect_view). Você pode então criar uma restrição trivial nessa visão materializada que seria violada se houvesse alguma linha na visão materializada. Isso tem o benefício de minimizar a quantidade de dados que você precisa duplicar na visualização materializada. Isso pode causar problemas, no entanto, uma vez que a restrição só é aplicada no ponto em que você está confirmando a transação-- muitos aplicativos não são escritos para esperar que uma operação de confirmação possa falhar-- e porque a violação da restrição pode ser um pouco difícil para associar a uma linha ou tabela específica.

    • 10
  2. Best Answer

    Essa regra de negócios pode ser aplicada no modelo usando apenas restrições. A tabela a seguir deve resolver seu problema. Use-o em vez de sua exibição:

        CREATE TABLE BookAspectCommonTagLink
    (  BookID INT NOT NULL
    , AspectID INT NOT NULL
    , TagID INT NOT NULL
--TagID is deliberately left out of PK
    , PRIMARY KEY (BookID, AspectID)
    , FOREIGN KEY (BookID, TagID) 
        REFERENCES BookTag (BookID, TagID)
    , FOREIGN KEY (AspectID, TagID) 
        REFERENCES AspectTag (AspectID, TagID)
    ) ;
    • 9

  3. Acho que você descobrirá que, em muitos casos, regras de negócios complexas não podem ser impostas apenas por meio do modelo. Este é um daqueles casos em que, pelo menos no SQL Server, acho que um gatilho (de preferência em vez de um gatilho) atende melhor ao seu propósito.

    • 8

  4. SIRA_PRISE permite isso.

    Embora o FK não seja mais chamado de "FK", mas apenas "restrição de banco de dados", e a "visualização" na verdade nem precise ser definida como uma exibição, você pode apenas incluir a expressão de definição de exibição dentro da declaração do restrição de banco de dados.

    Sua restrição seria algo como

    SEMIMINUS(BOOKASPECT , JOIN(BOOKTAG , TAGASPECT))

    e pronto.

    No entanto, na maioria dos DBMSs SQL, você teria que fazer o trabalho de análise em sua restrição, determinar como ela pode ser violada e implementar todos os gatilhos necessários.

    • 4

  5. No PostgreSQL, não consigo imaginar uma solução sem envolver gatilhos, mas certamente pode ser resolvido dessa forma (seja mantendo uma visão materializada de algum tipo ou um gatilho anterior em BookAspectRating). Não há chaves estrangeiras referenciando uma exibição ( ERROR: referenced relation "v_munkalap" is not a table), muito menos uma chave primária.

    • 3

relate perguntas