How to enforce a 1 to n relationship

Question

I'm working on an online muiltiplayer board game & have a SQL server question.

Lets assume the game allows two players. When the game is created, the creator is added as the first user.

At that point, two users could try to join the game simultaneously. One of these users should be blocked.

My database schema is as follows:

tbGame - contains a list of all games. PrimaryKey is GameId
tbPlayers - contains a list of all registered users. PrimaryKey is PlayerId
tbPlayersInGame - contains a list of all players in each game. Foreign key 
                  relations to tbGame and tbPlayers.

I feel like I need two things:

1. A way to lock tbPlayersInGame based on the GameId. This would be used while adding a player to a game. From what I've read it sounds like a key-range lock (on GameId) would be suitable.
2. A way to enforce a 1-to-2 relationship so that attempts to add a 3rd player would fail.

Answer 1

A couple suggestions:

1) When you try to write to the tbPlayersInGame table first do a SELECT to make sure the game isn't full, then INSERT INTO the table. Wrap this SELECT and the INSERT INTO in a transaction with the transaction isolation level set to serializable.

2) Don't have a separate tbPlayersInGame table, instead have 2 fields in tbGame: Player1Id, Player2Id