Limit number of records using trigger and constraints in MySQL -

i have table called bffs, stores userid, , best friend's userid , restrict table have 3 number of best friends each different user.

i mean if table structre is:

bffs(userid, userid) 

and records are:

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

and in case, if user id 3286 should not allowed have new record such (3286, xyzt).

i wrote trigger i'm not sure:

create trigger bffcontrol before insert on bffs each row declare     numberofbffs integer;     max_bffs integer := 3; begin     select count(*) numberofbffs     bffs     sender =: new.sender      if :old.sender =: new.sender         return;     else         if numberofbffs >= max_bffs             raise_application_error(-20000, 'users allowed have @ thre friends.');         end if;     end if; end; / 

how should restrich on relational tables through assertions or triggers ?

thanks

add column, friendnumber , foreign key constraint reference table 3 rows:

create table 3 ( friendnumber tinyint not null  , primary key (friendnumber) ) ;  insert 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           --- ensures user can have     references three(friendnumber)   --- max of 3 friends ) ; 

then can add:

insert bffs    (userid, friendid, friendnumber) values   (3286, 1212, 1) ,   (3286, 4545, 2) ,   (3286, 7878, 3) ; 

---

or suggested @gbn, (so column auto-filled):

insert bffs      (userid, friendid, friendnumber)   select         3286 userid      , 8989 friendid      , coalesce(          ( select min(three.friendnumber)            3               left join bffs b                  on  b.friendnumber = three.friendnumber                , b.userid = 3286            b.friendnumber null          ), 4        ) friendnumber   dual