数据库设计 – 在SQL中实现与多个参与约束的多对多关系

我应该如安在SQL中实现以下实体相关图中描写的场景？

如图所示,每个A实体范例的呈现必需与至少一个B对应物相干(由双毗连线暗示),反之亦然.我知道我应该建设以下三个表：

CREATE TABLE A
    (
        a INT NOT NULL,CONSTRAINT A_PK PRIMARY KEY (a)
    );

    CREATE TABLE B
    (
        b INT NOT NULL,CONSTRAINT B_PK PRIMARY KEY (b)
    );

    CREATE TABLE R
    (
        a INT NOT NULL,b INT NOT NULL,CONSTRAINT R_PK      PRIMARY KEY (a,b),CONSTRAINT R_to_A_FK FOREIGN KEY (a)
            REFERENCES A (a),CONSTRAINT R_to_B_FK FOREIGN KEY (b)
            REFERENCES B (b)
    );

可是,总参加束缚的实验环境怎样(即,逼迫执行A??或B的每个实例与另一个实例之间至少涉及一个相关)？

办理要领

-- lets create first the 2 tables,A and B:
CREATE TABLE a 
( aid INT NOT NULL,bid INT NOT NULL,CONSTRAINT a_pk PRIMARY KEY (aid) 
 );

CREATE TABLE b 
( bid INT NOT NULL,aid INT NOT NULL,CONSTRAINT b_pk PRIMARY KEY (bid) 
 );

-- then table R:
CREATE TABLE r 
( aid INT NOT NULL,CONSTRAINT r_pk PRIMARY KEY (aid,bid),CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

这里是“正常”计划,个中每个A可以与零,一个或多个B相干,而且每个B可以与零,一个或多个A相干.

“总参加”限定必要以相反的次序举办束缚(别离来自A和B,引用R).在相反偏向(从X到Y以及从Y到X)具有FOREIGN KEY束缚正在形成一个圆(“鸡和蛋”题目),这就是为什么我们必要个中一个至少是可延长的.在这种环境下,我们有两个圆圈(A – > R – > A和B – > R – > B,因此我们必要两个可耽误的束缚：

-- then we add the 2 constraints that enforce the "total participation":
ALTER TABLE a
  ADD CONSTRAINT r_a_fk FOREIGN KEY (aid,bid) REFERENCES r 
    DEFERRABLE INITIALLY DEFERRED ;

ALTER TABLE b
  ADD CONSTRAINT r_b_fk FOREIGN KEY (aid,bid) REFERENCES r 
    DEFERRABLE INITIALLY DEFERRED ;

然后我们可以测试我们可以插入数据.请留意,不必要INITIALLY DEFERRED.我们可以将束缚界说为DEFERRABLE INITIALLY IMMEDIATE可是我们必需在事宜时代行使SET CONSTRAINTS语句来推迟它们.但在每种环境下,我们都必要在单个事宜中插入表中：

-- insert data 
BEGIN TRANSACTION ;
    INSERT INTO a (aid,bid)
    VALUES
      (1,1),(2,5),(3,7),(4,1) ;

    INSERT INTO b (aid,(1,2),3),4),6),7) ;

    INSERT INTO r (aid,7) ; 
 END ;

测试时刻为SQLfiddle.

假如DBMS没有DEFERRABLE束缚,则一种办理要领是将A(bid)和B(帮助)列界说为NULL.然后INSERT进程/语句必需起首插入到A和B中(别离在出价和帮助中安排空值),然后插入到R中,然后将上面的空值更新为来自R的相干非空值.

行使这种要领,DBMS不会仅通过DDL逼迫执行这些要求,但必需响应地思量和调解每个INSERT(以及UPDATE和DELETE和MERGE)进程,而且必需限定用户仅行使它们,而且不能直接写入表格.

很多最佳实践并未思量在FOREIGN KEY束缚中行使圆圈,而且出于充实的来由,伟大性是个中之一.譬喻,行使第二种要领(具有可为空的列),如故必需行使特另外代码来更新和删除行,详细取决于DBMS.譬喻,在SQL Server中,您不能只行使ON DELETE CASCADE,由于当存在FK圈时,不应承级联更新和删除.

请阅读此相干题目的谜底：
How to have a one-to-many relationship with a privileged child?

另一种第三种要领(拜见我在上述题目中的谜底)是完全去除圆形FK.因此,保持代码的第一部门(表A,B,R和外键仅从R到A和B)险些完备(现实上简化它),我们为A添加另一个表来存储“必需有一个”来自B的相干项目.因此,A(出价)栏移至A_one(出价)对付从B到A的反向相关也是云云：

CREATE TABLE a 
( aid INT NOT NULL,CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

CREATE TABLE a_one 
( aid INT NOT NULL,CONSTRAINT a_one_pk PRIMARY KEY (aid),CONSTRAINT r_a_fk FOREIGN KEY (aid,bid) REFERENCES r
 );

CREATE TABLE b_one
( bid INT NOT NULL,CONSTRAINT b_one_pk PRIMARY KEY (bid),CONSTRAINT r_b_fk FOREIGN KEY (aid,bid) REFERENCES r
 );

第一种要领和第二种要领的区别在于没有轮回FK,因此级联更新和删除事变正常. “全员参加”的执行不只仅是DDL,如第二种要领,必需通过恰当的措施(INSERT / UPDATE / DELETE / MERGE)来完成.与第二种要领的一个渺小不同是全部列都可以界说为不举动空.

另一个第4种要领(拜见上述题目中的@Aaron Bertrand’s answer)是行使过滤/部门独一索引(假如它们在您的DBMS中可用)(对付这种环境,您必要个中两个,在R表中).这与第3种要领很是相似,只是您不必要2个特另外表. “总参加”束缚如故必要通过代码来应用.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!