多个表的多个外键

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,
      ...
                   );

例如，其他编号= 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“上的insert或update违反了外键约束"material_matnumber_accumulation_fkey”详细信息:表“scp0001”中不存在键(Matnumber)=(Matnumber)

表格材料应如下所示：

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      |

由于matnumber，每个条目都是唯一的！

Answer 1

如果我理解正确的话，这是一个可以使用超类型/子类型模式解决的常见问题。在Material表中也可以有4个可以为空的FK列，但这里是我的建议：

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

你说：“有4张表就像(山体滑坡的一部分)”

因此，我们创建了一个额外的LandslidePart表(这是我们的“超类型”表)：

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表，引用的是“超类型” (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,
                      );

Answer 2

检查您的外键长度和数据类型。

在MATERIAL中对MATNUMBER VARCHAR(9) NOT NULL的所有以下引用

SCPNUM CHAR(7) NOT NULL, *should be* SCPNUM VARCHAR(9) NOT NULL,
ACCUNUM CHAR(8) NOT NULL *should be* ACCUNUM VARCHAR(9) NOT NULL
FRNUM CHAR(6) NOT NULL, *should be* FRNUM VarCHAR(9) NOT NULL,