MySQL – Limiting Number of Foreign Key References for a Single Table Index

foreign keyMySQL

I'm having difficulty finding an answer because I'm having difficulty expressing the question. So I apologize if their is a duplicate (or similar) question on here.

Is there a way to limit the number of references to a specific foreign key in MySQL?

Explained via Metaphor:

Imagine it as a study group with a 'leader'. You have a 'leaders' table, and you have a 'members' table.

Is there a way to prevent each 'leader' from having more than 'x number' of 'members' assigned to them via foreign key?

Or is this something I would have to do when creating the 'members' in the first place? ie. some way to check how many references already exist and change the foreign key if the ID is > 'x number'

Thank you for any help or direction you can give!

Best Answer

I am not sure if this is a very clean solution, but it might work.

Suppose that you have a Leader table and a Member table. Create a third table, LeaderMember. This should have three columns, LeaderID, MemberID, and MemberNumber. LeaderID and MemberID are both foreign keys to the respective tables.

CREATE TYPE for the MemberNumber column, restricting the integer values that it can take, for example from 1 to 8.

Now impose two constraints on the table. First a UNIQUE constraint on MemberID, which ensures that a member can only have one leader. Then a UNIQUE constraint on the combination of columns (LeaderID, MemberNumber). This will mean that each leader can only have as many members as there are distinct values possible for MemberNumber.

I can see a couple of drawbacks with this - first of all inserting a new Member is now a little tricky, as you have to find a value of MemberNumber which has not been used. Bear in mind you have to make a check at this point anyway - to see if the Leader in question already has enough members. Secondly the type for the MemberNumber might suggest that there is some difference between the members which have the different values - that the numbers are meaningful.

However, I think it is a reasonably clean and normalized design.

Related Question