PostgreSQL中如何禁止自关联多对多表的双向重复关系？

我有一个关联person表自身的多对多好友关系表，建表语句如下：

CREATE TABLE IF NOT EXISTS friendship(
    person_id INTEGER NOT NULL,
    friend_id INTEGER NOT NULL,
    date_friendship DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY(person_id, friend_id),
    FOREIGN KEY(person_id) REFERENCES person(id),
    FOREIGN KEY(friend_id) REFERENCES person(id),
    UNIQUE(friend_id, person_id)
);

初始插入数据：

INSERT INTO friendship (person_id, friend_id)
VALUES (1, 2),
        (1, 3),
        (2, 3),
        (4, 2);

现在的问题是，我希望插入(2,1)这类记录时被禁止（因为(1,2)已存在，好友关系是双向的），但设置了UNIQUE(friend_id, person_id)后仍然能插入该记录：

INSERT INTO friendship (person_id, friend_id)
VALUES (2, 1);

请问该如何处理这种情况？是否需要更换数据库？

解决方案：不需要更换数据库 #

可以通过以下几种方式实现需求：

1. 插入时强制保证person_id < friend_id #

在插入数据前，确保总是把较小的ID放在person_id字段，较大的放在friend_id字段。比如插入(2,1)时，自动转换成(1,2)再执行插入，利用现有唯一约束阻止重复。

可以通过触发器或存储过程封装插入逻辑：

-- 示例触发器（以PostgreSQL为例）
CREATE OR REPLACE FUNCTION adjust_friendship_order()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.person_id > NEW.friend_id THEN
        -- 交换两个ID的顺序
        NEW.person_id := NEW.friend_id;
        NEW.friend_id := TG_OP = 'INSERT' ? OLD.person_id : NEW.friend_id; -- 修正为正确的交换逻辑
        -- 更简洁的写法：
        SELECT NEW.friend_id, NEW.person_id INTO NEW.person_id, NEW.friend_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_friendship_order
BEFORE INSERT ON friendship
FOR EACH ROW EXECUTE FUNCTION adjust_friendship_order();

也可以在应用层处理，插入前先比较两个ID大小，调整顺序后再执行插入操作。

2. 添加基于表达式的唯一约束 #

多数现代数据库（如PostgreSQL、MySQL 8.0+）支持基于表达式的唯一索引，直接约束(LEAST(person_id, friend_id), GREATEST(person_id, friend_id))的组合唯一性：

-- PostgreSQL/MySQL 8.0+ 适用
CREATE UNIQUE INDEX idx_unique_friendship ON friendship (LEAST(person_id, friend_id), GREATEST(person_id, friend_id));

无论插入(1,2)还是(2,1)，LEAST和GREATEST的结果组合都是(1,2)，会被该唯一索引拦截，避免重复。

3. 检查约束+触发器（兼容更多数据库） #

如果你的数据库不支持表达式索引，可以先添加检查约束保证person_id < friend_id，再配合触发器自动调整插入顺序：

-- 添加检查约束
ALTER TABLE friendship ADD CONSTRAINT chk_friendship_order CHECK (person_id < friend_id);

-- 触发器逻辑同方法1，插入前自动调整ID顺序

既通过检查约束阻止反向插入，又通过触发器自动修正顺序，避免用户手动插入时出错。

内容的提问来源于stack exchange，提问作者Muhammad Ihab