5

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

4
  • 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
  • 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
-1

Here's my solution, but for SQL Server. I ran into your question via Google, but I'm unfamiliar with PostGres... but maybe this'll help someone.

use master;
GO
ALTER DATABASE GraphDb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [GraphDb];
GO
CREATE DATABASE [GraphDb]
GO
use GraphDb;
GO
CREATE TABLE [GraphEdge](
    SourceId int NOT NULL,
    TargetId int NOT NULL
);
GO
ALTER TABLE [GraphEdge] ADD CONSTRAINT PK_GraphEdge PRIMARY KEY CLUSTERED (SourceId, TargetId);
GO
ALTER TABLE [GraphEdge] ADD CONSTRAINT UQ_GraphEdge_TargetId_SourceId UNIQUE (TargetId, SourceId);
GO
CREATE FUNCTION GraphEdgeUniqueReverseDirection (
    @sourceId int,
    @targetId int
)
RETURNS BIT
AS
BEGIN
    IF EXISTS (SELECT * FROM GraphEdge WHERE SourceId = @targetId AND TargetId = @sourceId)
        return 1;
    return 0
END
GO
ALTER TABLE [GraphEdge] ADD CONSTRAINT CHK_GraphEdge_UniqueReverseDirection CHECK (dbo.GraphEdgeUniqueReverseDirection(SourceId, TargetId)=0);
GO
INSERT INTO GraphEdge (SourceId, TargetId) VALUES (1,2)
GO
INSERT INTO GraphEdge (SourceId, TargetId) VALUES (2,1)
GO

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