THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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

 

CREATE TABLE dbo.Teams(TeamID INT NOT NULL PRIMARY KEY);
GO
INSERT INTO dbo.Teams(TeamIDSELECT UNION ALL SELECT 2;
GO
  

CREATE TABLE dbo.Players(PlayerID INT NOT NULL PRIMARY KEY,
  
TeamID INT NOT NULL FOREIGN KEY REFERENCES dbo.Teams(TeamID),
  
NumberInTeam INT NOT NULL CHECK(NumberInTeam IN (1,2)),
  
TeamMateID INT NOT NULL,
  
TeamMatesNumberInTeam INT NOT NULL,
-- if NumberInTeam=1 then TeamMatesNumberInTeam must be 2
-- and vise versa
  
CHECK(NumberInTeam+TeamMatesNumberInTeam 3), 
  
UNIQUE(TeamIDNumberInTeam),
  
UNIQUE(PlayerIDTeamIDNumberInTeam),
  
FOREIGN KEY(TeamMateIDTeamIDTeamMatesNumberInTeam)
    
REFERENCES dbo.Players(PlayerIDTeamIDNumberInTeam)
);

You can only insert one or more whole teams:

INSERT INTO dbo.Players(PlayerIDTeamIDNumberInTeamTeamMateIDTeamMatesNumberInTeam)
SELECT 1,1,1,2,UNION ALL
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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

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 sqlblog.com, 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 simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement