数据库设计 – 是否有DBMS允许引用视图的外键(而不仅仅是基表)?

灵感来自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)) ;CRE

灵感来自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约束(或ASSERTIONS),可能解决此问题,在任何DBMS中都不可用.

另一个想法是使用(伪代码)视图:

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,PersonID INT NOT NULL,Rating INT NOT NULL,AspectID,PersonID),FOREIGN KEY (PersonID)   REFERENCES Person (PersonID),FOREIGN KEY (BookID,AspectID) 
    REFERENCES BookAspect_view (BookID,AspectID)
) ;

三个问题:

>是否有DBMS允许(可能具体化的)具有PRIMARY KEY的VIEW?
>是否有DBMS允许引用视图的FOREIGN KEY(而不仅仅是一个基表)?
>使用可用的DBMS功能,是否可以解决此完整性问题?

澄清:

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

解决方法

可以仅使用约束在模型中强制执行此业务规则.下表应该可以解决您的问题.使用它而不是你的观点:

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

作者: dawei

【声明】:永州站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

为您推荐

联系我们

联系我们

0577-28828765

在线咨询: QQ交谈

邮箱: xwei067@foxmail.com

工作时间:周一至周五,9:00-17:30,节假日休息

返回顶部