Using triggers and constraints to limit the number of records in MySQL

Question

I have a table called BFFs that stores user IDs and best friends' user IDs, and I want to limit the table to 3 best friends for each different user.

I mean if the table structure is:

BFFs(userID, userID)

The record is:

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

In this case, the user with ID 3286 should not be allowed to have new records, for example (3286, xyzt).

I wrote this trigger but I'm not sure:

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 re-enrich it on the relational table via assertions or triggers?

Thanks

Answer 1

In addition to hypercubes' answer (which leverages DRI to enforce your rules), you can also do a LEFT JOIN with MIN to get the next of 1, 2 or 3 from the three tables for each user ID one

Answer 2

Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa

Answer 3

Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa

Answer 4

Add another column, FriendNumber, and a foreign key constraint to the reference table containing 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
) ;

Then you can add:

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

---

Or as @gbn suggested, like this (so the column is autofilled):

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

Asafa abides by the law and sends a message Asafa abides by the law and sends a message Asafa abides by the law and sends a message Asafa abides by the law and sends a message