sql - DB2:多个表之间的外键约束

Question

在 DB2 中，假设我有 3 个表:T1、T2、T3:

CREATE TABLE T1 (
  T1_ID INTEGER NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE T2 (
  T2_T1 INTEGER NOT NULL, -- parent entity ID from T1
  T2_ID INTEGER NOT NULL,
  PRIMARY KEY (T2_T1, T2_ID),
);

CREATE TABLE T3 (
  T3_T1 INTEGER NOT NULL, -- parent entity ID from T1
  T3_T2 INTEGER NOT NULL, -- parent entity ID from T2
  T3_ID INTEGER NOT NULL,
  PRIMARY KEY (T3_T1, T3_T2, T3_ID)
);

我想定义 FK 关系，如果 T3 中有连接记录，它会禁止删除 T2 中的记录，但同时当我从 T1 中删除记录时，它应该自动删除 T2 中的记录，并且分别为T3。

是否可以只定义 FK 约束来满足这些要求？或者如果不编写代码首先从 T3 中删除适当的记录记录，然后从 T2 中删除，然后才从 T1 中删除，就无法实现？

更新:我尝试运行的实际 DB2 10.5 代码:

CREATE TABLE t1 (
  t1_id INTEGER NOT NULL,
  PRIMARY KEY(t1_id)
)

CREATE TABLE t2 (
  t2_t1 INTEGER NOT NULL, 
  t2_id INTEGER NOT NULL, 
  PRIMARY KEY (t2_t1, t2_id), 
  CONSTRAINT t2_fk_t1 
    FOREIGN KEY (t2_t1) 
    REFERENCES t1 (t1_id) 
    ON DELETE CASCADE
)

CREATE TABLE t3 (
  t3_t1 INTEGER NOT NULL, 
  t3_t2 INTEGER NOT NULL, 
  t3_id INTEGER NOT NULL, 
  PRIMARY KEY (t3_t1, t3_t2, t3_id), 
  CONSTRAINT t3_fk_t1 
    FOREIGN KEY (t3_t1) 
    REFERENCES t1 (t1_id) 
    ON DELETE CASCADE, 
  CONSTRAINT t3_fk_t2 
    FOREIGN KEY (t3_t1, t3_t2) 
    REFERENCES t2 (t2_t1, t2_id) 
    ON DELETE RESTRICT
)

最后一个 CREATE 语句失败并出现以下错误:

SQL20255N FOREIGN KEY "T3_FK_T2" is not valid because it would cause a descendent table "S.T3" to be delete-connected to its ancestor table "S.T1" through multiple relationships with conflicting delete rules. The conflict is between the delete rules of constraints "S.T3.T3_FK_T1" and "S.T3.T3_FK_T2" on the descendent table. Reason code = "3". SQLSTATE=42915

Answer 1

这应该有效。尽管看起来有点奇怪，但并不禁止重叠约束。

---

CREATE TABLE t1 (
  t1_id INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE t2 (
  t2_t1 INTEGER NOT NULL REFERENCES t1(t1_id) ON DELETE CASCADE
  , t2_id INTEGER NOT NULL
  , PRIMARY KEY (t2_t1, t2_id)
);

CREATE TABLE t3 (
  t3_t1 INTEGER NOT NULL REFERENCES t1(t1_id) ON DELETE CASCADE
  , t3_t2 INTEGER NOT NULL
        , FOREIGN KEY (t3_t1,t3_t2) REFERENCES t2(t2_t1,t2_id) ON DELETE RESTRICT
  , t3_id INTEGER NOT NULL
        , PRIMARY KEY (t3_t1, t3_t2, t3_id)
);
INSERT INTO t1 (t1_id) VALUES (11), (12),(13), (14), (15);
INSERT INTO t2 (t2_t1,t2_id) VALUES (11, 21), (12,22),(13,23), (14,24), (15,25);
INSERT INTO t3 (t3_t1,t3_t2,t3_id) VALUES (11, 21,31), (12,22,32),(13,23,33), (14,24,34), (15,25,35);


DELETE FROM t1 WHERE t1_id = 13; -- should succeed
DELETE FROM t2 WHERE t2_id = 24; -- should fail

SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;