2

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;
  • Will there always be 2 members in each relationships? – ypercubeᵀᴹ Sep 5 '14 at 11:46
  • Yes, the friendship only exists between 2 people, I am going to be using a separate table (based on this, but without that limit) to handle Friendship Groups – topherg Sep 5 '14 at 12:18
1

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

| improve this answer | |
  • Sweet, that also works for enforcing the Constraint of the number of Friendship Members, cheers – topherg Sep 5 '14 at 11:27
  • nice triggering, mate! just a note: on update trigger will work every time you update something in the source table. Normally what you need is an event triggered for a single column, which I usually implement using: if (old.value <> new.value) (you might want to use <=> instead with NOT ) than ... – ninjabber Sep 5 '14 at 13:16
  • ah, good point. I should modify that to perform two separate tests, one if the User value changes (which it already does), and one if the Friendship value changes – topherg Sep 5 '14 at 13:22
  • 1
    exactly, mate! ;) – ninjabber Sep 5 '14 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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