如图所示,每个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个额外的表. “总参与”约束仍然需要通过代码来应用.
