Mysql – Is it possible to enforce a Unique Constraint on a Dataset comprised of joins

constraintMySQLrelational-theory

I am playing around with an idea to describe friendships in a system, and I am wondering about constraints. I currently have a database that looks like this:

Users:

+---------+-------------------------+
| User ID | (joined, password, etc) |
+---------+-------------------------+
| ALICE   | (normally numeric, but alpha for examples sake)
+---------+
| BOB     |
+---------+
| CARL    |
+---------+

Friendships:

+---------------+----------------------------------+
| Friendship ID | (date started, awesomeness, etc) |
+---------------+----------------------------------+
| 1             |
+---------------+
| 2             |
+---------------+
| 3             |
+---------------+
| 4             |
+---------------+

Friendship Members:

+---------+---------------+
| User ID | Friendship ID |
+---------+---------------+
| ALICE   | 1             |
+---------+---------------+
| BOB     | 1             |
+---------+---------------+
| ALICE   | 2             |
+---------+---------------+
| CARL    | 2             |
+---------+---------------+
| BOB     | 3             |
+---------+---------------+
| ALICE   | 3             |
+---------+---------------+

What I am looking for is a constraint that could perform a lookup on the Friendship Members, join them through their common Friendship ID, and ensure that the resultant dataset is unique (i.e. No two friendships with the same Members).

At this stage, it is assumed that a Friendship will only have 2 members. I have searched for a way to try to enforce that as a Constraint, but have had little success, so that is secondary at this point.

I was thinking that if this cannot be enforced natively by the database, I can always assemble a statement to find and remove non unique entities, but I was hoping to find a way to keep it entirely database side.

SOLUTION

As it was quickly answered, here is the implemented solution for others who are interested:

ON INSERT TRIGGER:

BEGIN
    SET @memberCount = (SELECT COUNT(*)
           FROM `FriendshipMembers`
          WHERE `FriendshipID` = NEW.`FriendshipID`);

    CASE
        WHEN (@memberCount = 1)  THEN
            IF ((SELECT 1
                   FROM `FriendshipMembers`
                  WHERE `FriendshipID` = NEW.`FriendshipID`
                    AND `UserID`       = NEW.`UserID`) = 1) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'User already a part of the Friendship';
            END IF;

            SET @secondUserID = (SELECT `UserID`
                                   FROM `FriendshipMembers`
                                  WHERE `FriendshipID` = NEW.`FriendshipID`);
            IF ((SELECT COUNT(*)
                   FROM `FriendshipMembers` FM1
             INNER JOIN `FriendshipMembers` FM2
                     ON FM1.`FriendshipID` = FM2.`FriendshipID`
                    AND FM1.`UserID`      != FM2.`UserID`
                  WHERE FM1.`UserID`       = NEW.`UserID`
                    AND FM2.`UserID`       = @secondUserID) > 0) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This Friendship already exists';
            END IF;

        WHEN (@memberCount > 1) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Too many members';

        ELSE BEGIN END;
    END CASE;
END;

ON UPDATE TRIGGER:

BEGIN
    SET @memberCount = (SELECT COUNT(*)
           FROM `FriendshipMembers`
          WHERE `FriendshipID` = NEW.`FriendshipID`);

    CASE
        WHEN (@memberCount = 2)  THEN
            IF ((SELECT 1
                   FROM `FriendshipMembers`
                  WHERE `FriendshipID` = NEW.`FriendshipID`
                    AND `UserID`       = NEW.`UserID`) = 1) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'User already a part of the Friendship';
            END IF;

            SET @secondUserID = (SELECT `UserID`
                                   FROM `FriendshipMembers`
                                  WHERE `FriendshipID` = NEW.`FriendshipID`
                                    AND `UserID`      != OLD.`UserID`);
            IF ((SELECT COUNT(*)
                   FROM `FriendshipMembers` FM1
             INNER JOIN `FriendshipMembers` FM2
                     ON FM1.`FriendshipID` = FM2.`FriendshipID`
                    AND FM1.`UserID`      != FM2.`UserID`
                  WHERE FM1.`UserID`       = NEW.`UserID`
                    AND FM2.`UserID`       = @secondUserID) > 0) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This Friendship already exists';
            END IF;

        WHEN (@memberCount > 2) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Too many members';

        ELSE BEGIN END;
    END CASE;
END;

Best Answer

create before insert/ before update trigger/s on Friendship Members table

table Friendships has nothing to do with the relation so you probably want so leave it aside for now.

the trigger should check

  1. id X can not have relation with id X
  2. if id X is in relation with id Y than do not create relation between id Y and id X

triggers

Related Question