在MySQL中限制使用触发器和约束的记录数 [英] Limit number of records using trigger and constraints in MySQL

问题描述

我有一个名为BFF的表，它存储userID和最好的朋友的userID，我想限制该表为每个不同的用户准备3个最好的朋友。

I have a table called BFFs, that stores userID, and best friend's userID and I would like to restrict that table to have exactly 3 number of best friends for each different user.

我的意思是如果表结构是：

I mean if the table structre is:

BFFs(userID, userID)

，记录为：

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

在这种情况下，如果不允许ID为3286的用户拥有一个新记录，例如（3286，xyzt）。

And in that case, if user with ID 3286 should not be allowed to have a new record such as (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;
/

我应该如何通过断言或触发器限制关系表？

How should I restrich this on relational tables through assertions or triggers ?

感谢

推荐答案

添加另一列 FriendNumber 和外键约束到具有正好3行的引用表：

Add another column, FriendNumber and a Foreign Key constraint to a reference table with exactly 3 rows:

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建议的（因此列自动填充）：

---

or as suggested by @gbn, to something like this (so the column is auto-filled):

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

这篇关于在MySQL中限制使用触发器和约束的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！