THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Saving the whole team of players or nothing

Suppose that you need to store teams of players, and you must enforce the following business rule: each team must consist of exactly two players. I will demonstrate how you can use constraints to implement this rule. I haven't used this approach in production yet, but I wanted to share an interesting idea. Of course we typically use triggers or stored procedures to implement such rules, but you can do it with constraints too, and it is worth mentioning at least as a brainteaser. Detailed comparison of pros and contras of different approaches is beyond the scope of this post.


Setting up the tables



NumberInTeam INT NOT NULL CHECK(NumberInTeam IN (1,2)),
TeamMatesNumberInTeam INT NOT NULL,
-- if NumberInTeam=1 then TeamMatesNumberInTeam must be 2
-- and vise versa
CHECK(NumberInTeam+TeamMatesNumberInTeam 3), 
FOREIGN KEY(TeamMateIDTeamIDTeamMatesNumberInTeam)
REFERENCES dbo.Players(PlayerIDTeamIDNumberInTeam)

You can only insert one or more whole teams:

INSERT INTO dbo.Players(PlayerIDTeamIDNumberInTeamTeamMateIDTeamMatesNumberInTeam)
SELECT 2,1,2,1,1;

 You cannot insert a single player.


Nor you can DELETE a single player, or UPDATE a player transferring to another team. Once the whole team is inserted, it can only be deleted as a whole team, or both players can at once transfer to other team(s). You try out and see for yourself.


More complex cases.


You can easily extend this approach to have teams of different sizes. For example, you can add columns TeamSizeFrom and TeamSizeTo to your Teams table, and make sure that all teams have the required sizes (different for different teams), all only with constraints. If you are running 2008, MERGE significantly simplifies modifications. Prior to 2008 in many cases all you can do is delete the whole team and reinsert the changed one.

P.S. I am leaving for a vacation soon, so I will not be responding to questions for a few weeks or so.



Published Wednesday, July 15, 2009 2:28 PM by Alexander Kuznetsov


No Comments
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on and Currently he works as an agile developer.

This Blog


Privacy Statement