7

I have two tables: a user table and a friendship table. Say the friendship table looks like this:

friendship
------------
user_one_id
user_two_id
other_fields

I need to enforce the uniqueness of the combination of values of (user_one_id, user_two_id) and disregard the ordering, so:

user_one_id | user_two_id
------------+------------
          1 |          2
          2 |          1  -- the DBMS should throw a unique constraint error when trying to insert a row like this one.

Another important point is that user_one_id represents the initiator of the friendship and user_two_id represents the recipient, so I cannot just make the "smaller" of the two ids user_one_id.

The question

Is there a way to do this using constraints or should I implement this some other way?

8

In light of your comment,

For my use case, user_one_id represents the initiator of the friendship, and user_two_id represents the recipient of the friendship. So I can't just use the lowest value as user_one_id.

Well, you can still do it. Your use case just excludes a row-constraint to ensure that. What you want is to use a table-constraint, something like this.

CREATE TABLE friendship (
  user_one_id int NOT NULL,
  user_two_id int NOT NULL,
  CHECK (user_one_id != user_two_id ),
  PRIMARY KEY (user_one_id, user_two_id)
);
-- you can do least first if you want. doesn't matter.
CREATE UNIQUE INDEX ON friendship (
  greatest(user_one_id, user_two_id),
  least(user_one_id, user_two_id)
);

We have a lot going on here.. We make sure.

  1. Both are NOT NULL
  2. Both are not equal to each other
  3. Both are UNIQUE (user_one_id, user_two_id)

That leaves one remaining problem of commutative uniqueness we solve that with a custom unique table-constraint implemented with an index.

Proof in the pudding

INSERT INTO friendship VALUES ( 1,2 );
INSERT 0 1
test=# INSERT INTO friendship VALUES ( 2,1 );
ERROR:  duplicate key value violates unique constraint friendship_greatest_least_idx"
DETAIL:  Key ((GREATEST(user_one_id, user_two_id)), (LEAST(user_one_id, user_two_id)))=(2, 1) already exists.

As an important friendly note your names are all kinds of silly. The relationship is fine. In production, please give them better names..

friendship
----------
request_initiator
request_target
other_fields
| improve this answer | |
  • Correct solution, there is a small detail, though, that could be improved: your solution creates two indexes in the background, where one would suffice. Create the table without primary key, then an index over (GREATEST(),LEAST()) and do ALTER TABLE ADD PRIMARY KEY USING INDEX... – Twinkles Feb 9 '17 at 7:34
  • 2
    Although, now that I think about it, most real world databases will probably benefit from the second index anyway. – Twinkles Feb 9 '17 at 7:44
  • @Twinkles This didn't working, raise an error: ERROR: index "friendship_greatest_least_idx" contains expressions LINE 1: alter table friendship add primary key using index friendshi... ^ DETAIL: Cannot create a primary key or unique constraint using such an index – jsvisa Feb 13 '17 at 5:57
0

the solution is very good. But we should first ensure that there is no overlap numbers in the two columns. ie., a number will be unique to a column.

I would suggest the following simpleton solution:

create two sequences:

1.

user_one_id_seq: with even numbers

user_two_id_seq with odd numbers

2.

user_one_id_seq: numbers between say 1 to 10000

user_two_id_seq numbers between say 10001 to 20000

| improve this answer | |
0

The design should be normalized by creating an intersection table named user_friendship:

create table user_friendship ( user id int NOT NULL, friendship id int NOT NULL, PRIMARY KEY (user_id, friendship_id), FOREIGN KEY friendship_id REFERENCES friendship(friendship_id), FOREIGN KEY user_id REFERENCES user(user_id)
);

| improve this answer | |
-1

If you set the logic to set that the value of user_one_id is lower than the value of user_two_id, you won't have any issue.

| improve this answer | |
  • 2
    For my use case, user_one_id represents the initiator of the friendship, and user_two_id represents the recipient of the friendship. So I can't just use the lowest value as user_one_id. – maniciam Feb 9 '17 at 4:51
  • 1
    @maniciam see my answer. – Evan Carroll Feb 9 '17 at 5:29

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.