3

Problem description

I have a situation where I want to enforce that a relationship can only be defined once and I want to solve it on database (Postgres) level.

The following situation I consider invalid, because the relationship is defined twice.

 user_id | friend_id
---------+-----------
       1 |        35
      35 |         1

I've tried adding CHECK constraints [1] but I can't come up with condition that will render this situation invalid. I'm not sure about other ways of handling this, without starting to think of a lot less transparant features (like procedures).

The current table structure:

     Table "public.friends"
  Column   |  Type   | Modifiers
-----------+---------+-----------
 user_id   | integer | not null
 friend_id | integer | not null

Indexes:
    "friends_pkey" PRIMARY KEY, btree (user_id, friend_id)
Check constraints:
    "no_schizophrenia" CHECK (friend_id <> user_id)
Foreign-key constraints:
    "friends_id_key" FOREIGN KEY (friend_id) REFERENCES users(id)
    "user_id_key" FOREIGN KEY (user_id) REFERENCES users(id)

Desired situation

Data:

 user_id | friend_id
---------+-----------
       1 |        35

Query:

insert into friends (user_id, friend_id) values (35, 1) \g

Result:

ERROR:  new row for relation "friends" violates check constraint "duplicate_relation"
DETAIL:  Failing row contains (35, 1).

[1] - https://www.postgresql.org/docs/current/static/ddl-constraints.html

  • Coming from a SQL Server background (but having done some postgres) I think I would handle this using a trigger. – Keith Miller Jul 20 '16 at 8:45
  • 1
    If you would be willing to order the IDs before inserting, you could replace no_schizophrenia with "CHECK (friend_id < user_id)". – Marco Borchert Jul 20 '16 at 11:05
  • 1
    @KeithMiller that is my fall-back scenario and something I had considered, but my gut tells me there needs to be a cleaner approach to this problem. – Dynom Jul 20 '16 at 13:40
  • @Marco That is an interesting approach, however that still requires logic on the consumer side of things, and I want to keep full control in the database. – Dynom Jul 20 '16 at 13:41
0

Your solution is correct for a one --> many relationship where each user maintains it's own list of friends.

The second record (user #35 has friend #1) is correct for user 35.

If you are absolutely intent on having a constraint to implement a bi-directional constraint -- Assuming your one-many join table is called 'friends', then I think it looks like this:

CHECK ((friend_id <> user_id) AND (user_id NOT IN (SELECT friend_id FROM friends WHERE user_id = friend_id)))

  • 1
    A bit of a necro, but seeing as you've raised it, I'll point to a different suggestion. This requirement now means the table defines a different kind of relationship (no concept of "owner", just "these 2 users are friends"). In that case, the table is really 'user_1_id` and user_2_id with a unique constraint and a check that user_1_id > user_2_id and let the client figure it out (or worst, case a trigger to swap the values). I don't think your solution will work as even if the SELECT is permitted in checks, you cannot see uncommitted work so you won't know for sure. – LoztInSpace Aug 31 '18 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.