SQL构造双向图 [英] SQL structuring a bi-driectional graph

问题描述

我想要一个具有以下列的表:id，user_id1和user_id2.

I want to have a table with columns: id, user_id1, and user_id2.

基本上，这将表示一个朋友图，其链接表示user1是user2的朋友，反之亦然.

basically this will resprsent a friend graph with the link representing that user1 is friends with user2 AND vice versa.

我的设置得到一个用户，然后是他们的朋友列表.鉴于我不想在表中有多余的条目，应该如何处理呢?

my setup gets a single user and then a list of their friends. given that I don't want to have extra entries in my table how shoudl I handle this?

我想做类似的事情:insert into friendship (user_id1, user_id2) values (<id1>, <id2>) where ...

但是我不确定如何执行SQL中的条件逻辑

but I'm not sure how to do conditional logic like that in SQL

推荐答案

您可以通过CHECK约束强制user_id1始终小于user_id2:

You could force user_id1 to always be less than user_id2 with a CHECK constraint:

CHECK (user_id1 < user_id2)

大概不允许人们成为自己的朋友.然后在插入之前确保ID顺序正确.提取朋友列表时，您仍然需要检查两列:

Presumably people aren't allowed to be their own friends. And then make sure the IDs are in the right order before you INSERT. When extracting a list of friends, you'd still have to check both columns though:

select user_id2 from friendship where user_id1 = X
union all
select user_id1 from friendship where user_id2 = X

X当然是您感兴趣的人.要查看两个人是否是朋友，只需按正确的顺序排列其ID，然后选择SELECT.

where X is, of course, the person you're interested in. And to see if two people are friends, just arrange their IDs in the right order and SELECT away.

这篇关于SQL构造双向图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！