sql - 两个递归多对多关系之间的约束

Question

我有下表用于创建任意数量的不同类型的项目。

CREATE TABLE item_types (
    id SERIAL,
    PRIMARY KEY (id)
    -- Other columns omitted
);

CREATE TABLE items (
    id SERIAL,
    itemtype integer NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (itemtype) REFERENCES item_types (id)
    -- Other columns omitted
);

items 表有一个称为item_relationship 的递归多对多关系。

CREATE TABLE item_relationships (
    itemid1 integer,
    itemid2 integer,
    PRIMARY KEY (itemid1, itemid2),
    FOREIGN KEY (itemid1) REFERENCES items (id),
    FOREIGN KEY (itemid2) REFERENCES items (id) 
);

item_types 表有一个称为item_relationship_types 的递归多对多关系。

CREATE TABLE item_relationship_types (
    type1 integer,
    type2 integer,
    PRIMARY KEY (type1, type2),
    FOREIGN KEY (type1) REFERENCES item_types (id),
    FOREIGN KEY (type2) REFERENCES item_types (id)  
);

现在，我想做的是以某种方式限制您不能意外创建无效的 item_relationship，即项目的 item_types在任何 item_relationship_type 中都找不到。我有两个问题。

1. 这样的约束有意义吗？我认为插入错误的关系是业务逻辑中很容易发生的错误，因此在数据库中进行约束感觉很重要。

2. 实际实现约束的明智方法是什么？

Answer 1

一种可能的方法是使用代理主键扩展 item_relationship_types 表:

CREATE TABLE item_relationship_types (
    id integer SERIAL,
    type1 integer,
    type2 integer,
    PRIMARY KEY (id),
    UNIQUE (type1, type2),
    FOREIGN KEY (type1) REFERENCES item_types (id),
    FOREIGN KEY (type2) REFERENCES item_types (id)  
);

然后将指向该代理键的外键添加到 item_relationships 表中:

CREATE TABLE item_relationships (
    itemid1 integer,
    itemid2 integer,
    type_rel_id integer not null,
    PRIMARY KEY (itemid1, itemid2),
    FOREIGN KEY (itemid1) REFERENCES items (id),
    FOREIGN KEY (itemid2) REFERENCES items (id),
    FOREIGN KEY (type_rel_id) REFERENCES item_relationship_types (id)  
);

您还需要创建一个触发器，以防止在 item_relationships 表中输入 type_rel_id 值，该值指向类型与 item_relationship_types 中的这两个项目无关的条目 表。