sql - 多个表的多个外键

Question

我的数据库设计是这样的。缩写形式:

CREATE TABLE LANDSLIDE (
      LNUMBER SERIAL NOT NULL,
      PRIMARY KEY (LNUMBER)
      ...

有4个表像(部分滑坡)

CREATE TABLE SCARP (
      SCPNUM CHAR(7) NOT NULL, 
      LNUMBER SMALLINT NOT NULL, 
      FOREIGN KEY (LNUMBER) REFERENCES LANDSLIDE (LNUMBER) ON DELETE CASCADE,
      ...
                   );

例如scpnum = scp0001

CREATE TABLE ACCUMULATION (
      ACCUNUM CHAR(8) NOT NULL,
      LNUMBER SMALLINT NOT NULL,
      FOREIGN KEY (LNUMBER) REFERENCES LANDSLIDE (LNUMBER) ON DELETE CASCADE,
      ...
                          );

例如accunum = accu0001

CREATE TABLE FRONT (
      FRNUM CHAR(6) NOT NULL,
      LNUMBER SMALLINT NOT NULL, 
      FOREIGN KEY (LNUMBER) REFERENCES LANDSLIDE (LNUMBER) ON DELETE CASCADE,
      ...
                    );

例如frnum = fr0001

CREATE TABLE OTHER (
      OTHERNUM CHAR(9) NOT NULL,
      LNUMBER SMALLINT NOT NULL,
      FOREIGN KEY (LNUMBER) REFERENCES LANDSLIDE (LNUMBER) ON DELETE CASCADE,
      ...
                   );

例如othernum = other0001

还有一个表，叫 Material (不同的石头，不同部位的土)。

CREATE TABLE MATERIAL (
     MATNUMBER VARCHAR(9) NOT NULL,
     ROCK,
     DISTRIBUTION,
     ...
     PRIMARY KEY(MATNUMBER,ROCK,DISTRIBUTION,...),
     CONSTRAINT material_matnumber_scarp_fkey FOREIGN KEY 
     (MATNUMBER) REFERENCES SCARP (SCPNUM) ON DELETE CASCADE,
     CONSTRAINT material_matnumber_accumulation_fkey FOREIGN KEY 
     (MATNUMBER) REFERENCES ACCUMULATION (ACCUNUM) ON DELETE CASCADE,
     CONSTRAINT material_matnumber_front_fkey FOREIGN KEY 
     (MATNUMBER) REFERENCES FRONT (FRNUM) ON DELETE CASCADE,
     CONSTRAINT material_matnumber_other_fkey FOREIGN KEY 
     (MATNUMBER) REFERENCES OTHER (OTHERNUM) ON DELETE CASCADE
                      );

当我向 Material 中插入数据时(在对其他表进行输入之后)，它返回一个错误。错误:在表“material”上插入或更新违反了外键约束“material_matnumber_accumulation_fkey”详细信息:键 (matnumber)=(scp0001) 不存在于表“accumulation”中

表格 Material 应该是这样的:

MATNUMBER | ROCK | DISTRIBUTION | ...
----------+------+--------------+------
scp0001   |   A  |    lateral   | ...
scp0001   |   B  |    lateral   | ...
accu0001  |   B  |    central   | ...
scp0002   |   C  |    NULL      |
accu0002  |   A  |    lateral   |
fr0002    |   A  |    NULL      |
scp0003   |   B  |    cantral   |
accu0003  |   B  |    lateral   |
other0003 |   C  |    NULL      |

每个条目都是独一无二的，因为有编号!

Answer 1

如果我没理解错的话，这是一个常见的问题，可以用父类(super class)型/子类型模式来解决。您还可以在 Material 表中有 4 个可为 null 的 FK 列，但这是我的建议:

CREATE TABLE Landslide  (                  --- no change here
      LNumber SERIAL NOT NULL,
      PRIMARY KEY (LNumber)
      ...

你说:“有 4 个表像(滑坡的一部分)”

因此，我们创建一个额外的 LandslidePart 表(这是我们的“父类(super class)型”表):

CREATE TABLE LandslidePart (
      PartType CHAR(1) NOT NULL, 
      PartNumber INT NOT NULL, 
      LNumber SMALLINT NOT NULL,
      PRIMARY KEY (PartType, PartNumber),
      CHECK (PartType IN ('S', 'A', 'F', 'O')),       --- the 4 subtypes
      FOREIGN KEY (LNumber) REFERENCES Landslide (LNumber) ON DELETE CASCADE,
      ...                                             --- other columns that are
                                                      --- common in all 4 tables
                   );

然后我们有 4 个(“子类型”)表，稍作改动:

CREATE TABLE Scarp (
      PartType CHAR(1) NOT NULL, 
      PartNumber INT NOT NULL, 
      PRIMARY KEY (PartType, PartNumber),
      CHECK (PartType = 'S'), 
      FOREIGN KEY (PartType, PartNumber) 
          REFERENCES LandslidePart (PartType, PartNumber) 
          ON DELETE CASCADE,
      ...                           --- columns that are related to Scarp
                   );

CREATE TABLE Accumulation (
      PartType CHAR(1) NOT NULL, 
      PartNumber INT NOT NULL, 
      PRIMARY KEY (PartType, PartNumber),
      CHECK (PartType = 'A'), 
      FOREIGN KEY (PartType, PartNumber) 
          REFERENCES LandslidePart (PartType, PartNumber) 
          ON DELETE CASCADE,
      ...                           --- columns that are related to Accumulation 
                   );

 --- We define in a similar way the other 2 tables: "Front" and "Other"

现在我们可以定义 Material 表，它引用“父类(super class)型” (LandslidePart) 表，而不是以下四个表中的任何一个:

CREATE TABLE Material (
     PartType CHAR(1) NOT NULL, 
     MatNumber INT NOT NULL,
     Rock,
     Distribution,
     ...
     PRIMARY KEY(PartType, MatNumber, Rock, Distribution, ...),
     CONSTRAINT material_matnumber_landslidepart_fkey 
     FOREIGN KEY (PartType, MatNumber) 
         REFERENCES LandslidePart (PartType, PartNumber) 
         ON DELETE CASCADE,
                      );