Using triggers and constraints to limit the number of records in MySQL-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

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

P粉807397973 2023-11-14 13:35:02

855

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

P粉807397973

reply all (5)

P粉1139388802023-11-15 12:52:39 5 floor

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

Like+0

Add Reply

徐涛2023-11-15 17:38:31 4 floor

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

Like+0

Add Reply

徐涛2023-11-15 17:42:09 3 floor

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

Like+0

Add Reply

P粉7694133552023-11-15 00:32:43 2 floor

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

Like+0

Add Reply

徐涛2023-11-15 17:36:28 1 floor

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

Like+0

Add Reply