Limit the number of records using trigger and limits in MySQL
I have a table named BFF that stores the user id and best friend id, and I would like to constrain this table to have exactly 3 best friends for every other user.
I mean if the table is structre:
BFFs(userID, userID)
and records:
(3286, 1212) (3286, 4545) (3286, 7878)
And in this case if the user with ID 3286 is not allowed to have a new entry such as (3286, xyzt).
I wrote this trigger but 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 do I constrain this in relational tables through assertions or triggers?
thank
0
source to share
2 answers
Add another column FriendNumber
and foreign key constraint to the reference table with exactly three 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, something like this (so the column is autocomplete):
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
+2
source to share