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.
- Both are
NOT NULL
- Both are not equal to each other
- 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
