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:
(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 '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?
source to share
Add another column
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
source to share