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

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_relationship_type中都找不到项目的item_types。我有两个问题。

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

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

Answer 2

• 这并不完美，但似乎有效

---

CREATE FUNCTION item_check_types( ) RETURNS TRIGGER AS
$func$
BEGIN
IF EXISTS (
        SELECT 1
        FROM item_relationship_types irt
        JOIN items it1 ON it1.itemtype = irt.type1
        JOIN items it2 ON it2.itemtype = irt.type2
        WHERE (it1.id = NEW.itemid1 AND it2.id = NEW.itemid2)
        -- OR (it1.id = NEW.itemid2 AND it2.id = NEW.itemid1)
        ) THEN RETURN NEW;
ELSE
        RAISE EXCEPTION 'type lookup failure';
        RETURN NULL;
END IF;
END;
$func$ LANGUAGE 'plpgsql'
        ;
CREATE CONSTRAINT TRIGGER item_check_types
        AFTER UPDATE OR INSERT
        -- BEFORE UPDATE OR INSERT
        ON item_relationships
        FOR EACH ROW
        EXECUTE PROCEDURE item_check_types()
        ;
INSERT INTO item_types(id)
SELECT generate_series(1,10);
INSERT INTO item_relationship_types (type1, type2) VALUES
(1,3), (2,4), (3,5), (4,6);
INSERT INTO items(id, itemtype)
SELECT gs, gs % 10
FROM generate_series(101,109) gs;
INSERT INTO item_relationships(itemid1, itemid2)
  VALUES (101,103), (102,104); -- Okay
INSERT INTO item_relationships(itemid1, itemid2)
  VALUES (101,104), (102,103); -- should fail