防止多对多自引用关系(Postgres)中的循环

Question

假设您想要一个thing的层次结构，其中thing可以有多个thing父级和子级：

CREATE TABLE thing (
id SERIAL PRIMARY KEY
);
CREATE TABLE thing_association (
parent_id INTEGER NOT NULL,
child_id INTEGER NOT NULL,
PRIMARY KEY (parent_id, child_id),
CHECK (parent_id != child_id),
FOREIGN KEY (parent_id) REFERENCES thing(id),
FOREIGN KEY (child_id) REFERENCES thing(id)
);

CHECK约束可防止thing与自身发生关系，PRIMARY KEY约束可防止重复关系，但可以防止循环吗？

更确切地说，如果thing_association表中存在(x, y)行，是否可以阻止插入(y, x)行？

更进一步，如果thing_association中存在(x, y)和(y, z)行，是否可以防止插入(z, x)行？

Answer 1

我希望在没有触发器的情况下实现这一点，但我不确定这是否可能。我能够用BEFORE INSERT触发器实现这一点：

CREATE TABLE thing (
id SERIAL PRIMARY KEY
);
CREATE TABLE thing_association (
parent_id INTEGER NOT NULL,
child_id INTEGER NOT NULL,
PRIMARY KEY (parent_id, child_id),
CHECK (parent_id != child_id),
FOREIGN KEY (parent_id) REFERENCES thing(id),
FOREIGN KEY (child_id) REFERENCES thing(id)
);
/* maps every thing to all of it's parents */
CREATE VIEW thing_hierarchy AS
WITH RECURSIVE children AS (
SELECT
child_id,
parent_id
FROM thing_association
UNION SELECT
children.child_id,
parents.parent_id
FROM thing_association AS parents
INNER JOIN children
ON children.parent_id = parents.child_id
) SELECT * FROM children;
CREATE FUNCTION check_thing_association_loop() RETURNS TRIGGER AS $$
BEGIN
IF ((NEW.parent_id, NEW.child_id) in (SELECT child_id, parent_id FROM thing_hierarchy)) THEN
RAISE EXCEPTION 'Cannont create a hierarchy loop';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER thing_association_insert_check
BEFORE INSERT ON thing_association
FOR EACH ROW EXECUTE FUNCTION check_thing_association_loop();

您可以将视图合并到触发器函数中，但视图本身很有用，而且它可以保持简洁。