6

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

Dynom
  • 169
  • 3
  • 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 08: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

2 Answers2

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)))

Greg Patnude
  • 96
  • 1
  • 4
  • 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 06: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
DharmaTurtle
  • 99
  • 2