How to make strings unique by certain parameters in postgreSQL? [duplicate]

Question

Example: Let's say we have a friends table with columns user_id, friend_id, status. We need to be able to add:

user_id	friend_id	status
3	1	False
3	2	False
3	4	False

But it was impossible to add:

user_id	friend_id	status
1	3	False
2	3	False
4	3	False

In other words, so that IDs 1 and 2, 1 and 3 can be added and their reflections 2 and 1, 3 and 1 cannot be added.

Thank you in advance

I tried to make a composite key of user_id and friend_id consisting of foreign keys referencing to the same table. I also tried. I also tried to make these values unique, but in this case I can't add duplicates; for example 1 and 2, 1 and 3 (not added).

To put it another way, you want to make sure that friend relationships are unique? If 1 is friends with 3, then 3 cannot be friends with 1. And 1 cannot be friends with 1. — Schwern
– Schwern, Commented Feb 19, 2023 at 19:45
Rihgt. If 3 confirms friendship with 1, then there is no need to add row 3 and 1 to the table Thanks for editing! — Rustem Zamaliev
– Rustem Zamaliev, Commented Feb 19, 2023 at 19:52

Schwern · Accepted Answer · 2023-02-19 20:07:36Z

0

Unlike most many-to-many associations, this one is bidirectional. Both members are peers.

You can prevent a user from being friends with themselves with a check: check(user_id <> friend_id).

You can prevent duplicate friend relationships with a unique index on an expression that does not care about the order. For example, make an array and sort it.

create extension intarray;

create unique index on friends(
  (sort(array[user_id, friend_id]))
)

User ID 3 / Friend ID 1 and User ID 1 / Friend ID 3 both result in [1, 3].

To get a complete list of a user's friends you need to search by both user_id and friend_id.

select count(*)
from friends
where user_id = $1 or friend_id = $1

This suggests it might be better to refer to them as friend1 and friend2 making the point that the relationship is bidirectional.

Demonstration.

answered Feb 19, 2023 at 20:07

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make strings unique by certain parameters in postgreSQL? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related