MySQL中使用觸發器和約束限制記錄數

Question

我有一個名為 BFFs 的表，用於儲存使用者 ID 和最好朋友的使用者 ID，我想限制該表為每個不同的使用者提供 3 個最好的朋友。

我的意思是如果表格結構是：

BFFs(userID, userID)

記錄是：

(3286, 1212)
(3286, 4545)
(3286, 7878)

在這種情況下，如果 ID 為 3286 的使用者不應被允許擁有新記錄，例如 (3286, xyzt)。

我寫了這個觸發器，但我不確定：

CREATE TRIGGER BFFControl
BEFORE INSERT ON BFFs
FOR EACH ROW
DECLARE
    numberOfBFFs INTEGER;
    max_BFFs INTEGER := 3;
BEGIN
    SELECT COUNT(*) INTO numberOfBFFs
    FROM BFFs
    WHERE sender =: NEW.sender

    IF :OLD.sender =: NEW.sender THEN
        RETURN;
    ELSE
        IF numberOfBFFs >= max_BFFs THEN
            RAISE_APPLICATION_ERROR(-20000, 'Users are allowed to have at most thre friends.');
        END IF;
    END IF;
END;
/

我應該如何透過斷言或觸發器在關係表上重新豐富它？

謝謝

Answer 1

除了ypercubes 的答案（其中利用DRI 來強制執行您的規則）之外，您還可以使用MIN 進行LEFT JOIN，以從每個用戶ID 的三個表中獲取1、2 或3 中的下一個

Answer 2

沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發

Answer 3

沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發沙發

Answer 4

在恰好包含 3 行的引用表中新增另一列、FriendNumber 和外鍵約束：

CREATE TABLE Three
( friendNumber TINYINT NOT NULL 
, PRIMARY KEY (friendNumber)
) ;

INSERT INTO Three(friendNumber) 
  VALUES
    (1), (2), (3) ;

CREATE TABLE BFFs
( userID       INT NOT NULL 
, friendID     INT NOT NULL
, friendNumber TINYINT NOT NULL
, PRIMARY KEY (userID, friendID)
, UNIQUE (userID, friendNumber)
, FOREIGN KEY userID
    REFERENCES Person(userID)
, FOREIGN KEY friendID
    REFERENCES Person(userID) 

, FOREIGN KEY friendNumber           --- this ensures that a user can have
    REFERENCES Three(friendNumber)   --- max of 3 friends
) ;

然後您可以新增：

INSERT INTO BFFs 
  (userID, friendID, friendNumber)
VALUES
  (3286, 1212, 1) ,
  (3286, 4545, 2) ,
  (3286, 7878, 3) ;

---

或按照@gbn的建議，像這樣（所以該列是自動填入的）：

INSERT INTO BFFs 
    (userID, friendID, friendNumber)
  SELECT 
       3286 AS userID
     , 8989 AS friendID
     , COALESCE(
         ( SELECT MIN(Three.friendNumber)
           FROM Three 
             LEFT JOIN BFFs AS b  
               ON  b.friendNumber = Three.friendNumber
               AND b.userID = 3286
           WHERE b.friendNumber IS NULL
         ), 4
       ) AS friendNumber
  FROM dual

Answer 5

阿薩法守法發撒阿薩法守法發撒阿薩法守法發撒阿薩法守法發撒