Foreign keys in mysql

Question

I have a doubt about the way of relating some tables. I have these tables:

• User table: username (primary key)

• Team table: team_name(primary key), username (foreign key references User(username))

With this relationship, I get that an user can have more than one team.

• Group table: group_name (primary key)

I want that a group can have many teams, but these teams have to be of different users, so two teams of a user cannot be in the same group.

I have thought to do a relationship with the three tables of this way:

• Group_teams table: (group_name, username, team_name). This table would have a composite primary key (group_name and username), in this way I would be sure that an user cannot has more than one team in a same group.

In addition, I think that I should create a composite foreign key references User(username) and Team (team_name) to be able to control that the team of a user exists. Finally, I should create another foreign key references Group (group_name) to control that a group exists.

I'm not sure that it would be of this way because I have errors when I try to do it. Could yo help me and tell me your opinions?

Answer 1

If a user can be on (at most) only one team, then you have a 0/1 - many relationship.

The easiest approach is to have TeamId in the Users table. This would be a foreign key reference to Teams.

There is no need for a third table to represent this relationship.

Answer 2

There's no need to reproduce the username field in the Group_teams table, just have group_name & team_name.

Create a trigger on the Group_teams table to fire before a new row is inserted, checking for more other teams with the same username.

Have a look at the question "How do you check constraints from another table when entering a row into a table?", specifically this answer from Jim V describing such a setup.