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(TeamID) SELECT 1 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(TeamID, NumberInTeam),
UNIQUE(PlayerID, TeamID, NumberInTeam),
FOREIGN KEY(TeamMateID, TeamID, TeamMatesNumberInTeam)
REFERENCES dbo.Players(PlayerID, TeamID, NumberInTeam)
);
You can only insert one or more whole teams:
INSERT INTO dbo.Players(PlayerID, TeamID, NumberInTeam, TeamMateID, TeamMatesNumberInTeam)
SELECT 1,1,1,2,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.