主页 / dba / 问题 / 17853
Accepted
ypercubeᵀᴹ
Asked: 2012-05-15 15:02:25 +0800 CST

是否有允许引用视图（而不仅仅是基表）的外键的 DBMS？

  • 772

受到 Django 建模问题的启发：Database Modeling with multiple many-to-many Relations in Django。db-design 类似于：

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)
) ;

数据库图

问题是如何定义BookAspectRating表并强制执行参照完整性，因此不能(Book, Aspect)为无效的组合添加评级。

AFAIK，涉及子查询和多个表的复杂CHECK约束（或）可能解决此问题，但在任何 DBMS 中均不可用。ASSERTIONS

另一个想法是使用（伪代码）视图：

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) ;

以及具有上述视图的外键的表：

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)
) ;

三个问题：

  • 是否有允许（可能物化）VIEW带有 a 的DBMS PRIMARY KEY？

  • 是否有允许 a FOREIGN KEYthat REFERENCESa VIEW（而不仅仅是 base TABLE）的 DBMS？

  • 是否可以通过其他方式解决这个完整性问题 - 使用可用的 DBMS 功能？

澄清：

因为可能没有 100% 令人满意的解决方案 - Django 问题甚至不是我的！- 我对可能攻击问题的一般策略更感兴趣，而不是详细的解决方案。因此，像“在 DBMS-X 中，这可以通过表 A 上的触发器来完成”这样的答案是完全可以接受的。

database-design foreign-key

5 个回答

  1. 在 Oracle 中，以声明方式强制执行此类约束的一种方法是创建一个物化视图，该视图设置为在提交时快速刷新，其查询标识所有无效行（即BookAspectRating在 中没有匹配的行BookAspect_view）。然后，您可以在该物化视图上创建一个简单的约束，如果物化视图中有任何行，就会违反该约束。这样做的好处是可以最大限度地减少您必须在物化视图中复制的数据量。但是，它可能会导致问题，因为约束仅在您提交事务时才被强制执行——许多应用程序并不是为了期望提交操作可能失败而编写的——并且因为违反约束可能有些困难与特定行或特定表关联。

    • 10
  2. Best Answer

    可以仅使用约束在模型中强制执行此业务规则。下表应该可以解决您的问题。使用它代替您的视图：

        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. 我想您会发现，在很多情况下，复杂的业务规则不能仅通过模型来执行。这是其中一种情况，至少在 SQL Server 中，我认为触发器（最好是触发器而不是触发器）更好地服务于您的目的。

    • 8

  4. SIRA_PRISE允许这样做。

    虽然 FK 不再叫“FK”，而只是“数据库约束”，而“视图”实际上甚至不必定义为视图，您只需在声明中包含视图定义表达式即可数据库约束。

    你的约束看起来像

    SEMIMINUS(BOOKASPECT , JOIN(BOOKTAG , TAGASPECT))

    你就完成了。

    然而，在大多数 SQL DBMS 中，您必须对约束进行分析工作，确定如何违反约束并实现所有需要的触发器。

    • 4

  5. 在 PostgreSQL 中，我无法想象一个不涉及触发器的解决方案，但它当然可以通过这种方式解决（无论是维护某种物化视图还是在触发器上之前BookAspectRating）。没有引用视图 ( ERROR: referenced relation "v_munkalap" is not a table) 的外键，更不用说主键了。

    • 3

相关问题