是否可以對由連接組成的數據集強制執行唯一約束

我正在玩一個在系統中描述友誼的想法，我想知道約束。我目前有一個如下所示的數據庫：

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

我正在尋找的是一個約束，它可以在 上執行查找Friendship Members，通過它們的 common 加入它們Friendship ID，並確保結果數據集是唯一的（即沒有兩個相同成員的友誼）。

在這個階段，假設一個友誼只有 2 個成員。我已經尋找一種方法來嘗試將其作為約束來強制執行，但收效甚微，因此在這一點上是次要的。

我在想，如果這不能由數據庫本身強制執行，我總是可以組裝一個語句來查找和刪除非唯一實體，但我希望找到一種方法將其完全保留在數據庫端。

解決方案

正如它很快得到回答，這裡是其他感興趣的人實施的解決方案：

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;

在友誼成員表上插入/更新觸發器之前創建

表友誼與關係無關，因此您可能想要暫時將其放在一邊。

觸發器應該檢查

1. id X 不能與 id X 有關係
2. 如果 id X 與 id Y 有關，則不要在 id Y 和 id X 之間創建關係

觸發器

引用自：https://dba.stackexchange.com/questions/75788