Limit number of records using trigger and constraints in MySQL

Question

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)

and records are:

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

And in that case, if user with ID 3286 should not be allowed to have a new record such as (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 restrich this on relational tables through assertions or triggers ?

Thanks

ypercubeᵀᴹ · Accepted Answer · 2012-03-08T19:30:15.783

2

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
) ;

Then you can add:

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

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

edited Mar 08 '12 at 19:30

answered Mar 08 '12 at 17:29

ypercubeᵀᴹ

113,259
19
174
235

can you see the edit of my question, what are the differences between your answer and my trigger ? – CanCeylan Mar 08 '12 at 17:44
Triggers are hard to debug. I usually avoid them when a constraint solution exists. – ypercubeᵀᴹ Mar 08 '12 at 17:49
While they are not pure evil, read on this: [Are database triggers evil?](http://stackoverflow.com/questions/460316/are-database-triggers-evil) – ypercubeᵀᴹ Mar 08 '12 at 17:51
And MySQL had some issues with triggers-cascading FKs combination, not sure if that is resolved. – ypercubeᵀᴹ Mar 08 '12 at 17:52
So in this case, if I want to add friend, should I first add into the table Three, then into BFFs ? – CanCeylan Mar 08 '12 at 17:57
No, no, table Three stays locked and out of touch of everyone. You add rows as in the example above. (editing) – ypercubeᵀᴹ Mar 08 '12 at 17:59
So I need to keep track of number of friends that each user have before I make the adding proccess, because I'm writing the schema but people use it so they do not know the background, they sometimes add friends one by one, so in any of that case should I make the sql statement programmatically ? – CanCeylan Mar 08 '12 at 18:03
Yes, that's the problem with this approach. You can't have the `friendNumber` auto-filled by the database. Well, you can with MyISAM but then you can't have FKs which is a funny situation. – ypercubeᵀᴹ Mar 08 '12 at 18:05
You can leave it unanswered in case someone knows some better approach (answered questions get less attention). Or make another question of how this can be solved. – ypercubeᵀᴹ Mar 08 '12 at 18:10

score 1 · Answer 2 · answered Mar 08 '12 at 19:08

1

In addition to ypercubes' answer (whcih leverages DRI to enforce your rule), you can also LEFT JOIN with MIN to get the next of 1, 2 or 3 from the Three table per userID

answered Mar 08 '12 at 19:08

gbn

422,506
82
585
676

Limit number of records using trigger and constraints in MySQL

2 Answers2

Linked

Related