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