June 25, 2003 at 3:52 am
I'm kinda new to this so forgive me if my inquiry seems a little foolish!
I have a database that tracks soccer games and how individual players rate in each game. The three tables of note here are:
tblPlayers
PlayerID - PK
TeamID
Name
tblGames
GameID - PK
TeamID
tblRatings
GameID - PK
PlayerID - PK
Rating
I want to include a constraint on tblRatings that will not allow a rating entry for a PlayerID/GameID unless there is an entry for his TeamID against that GameID in tblGames. I hope I've explained that correctly and any help would be much appreciated... Perhaps my db design is incorrect??
Andy
June 25, 2003 at 5:19 pm
Andy,
One suggestion I might make is that the GAMES table might need to include 2 team IDs, because each unique game must be between 2 teams. Maybe TeamID1 and TeamID2, or Home_TeamID and Away_TeamID.
Anyway, the suggestion below is based on your current design of only one teamID in a game. Before creating the Ratings table, it creates a function that does the check that you want. That function is then referenced in a CHECK constraint in the Ratings table.
Note, you'll need to amend the function slightly if you go with the suggestion of 2 teams per Game row.
-- ====================================================================
-- Create the PLAYERS table
-- ====================================================================
create table tblPlayers (
PlayerID int primary key,
TeamID int,
Name varchar(30))
-- ====================================================================
-- Create the GAMES table
-- ====================================================================
create table tblGames (
GameID int primary key,
TeamID int)
go
-- ====================================================================
-- Function to check that a Play's team participated in a specific game
-- ====================================================================
CREATE FUNCTION HasPlayersTeamParticipated
(@GameID int,
@PlayerID int)
RETURNS int
AS
BEGIN
DECLARE @result INT
IF EXISTS (SELECT 1 FROM tblGames g
INNER JOIN tblPlayers p ON p.TeamID = g.TeamID
WHERE g.GameID = @GameID
AND p.PlayerID = @PlayerID)
SET @result = 1
ELSE
SET @result = 0
RETURN @result
END
GO
-- ====================================================================
-- Create the RATINGS table
-- ====================================================================
create table tblRatings (
GameID int,
PlayerID int,
Rating int ,
constraint pkRating primary key (GameID, PlayerID),
constraint ckPlayersTeamParticipation check (dbo.HasPlayersTeamParticipated(GameID, PlayerID)=1)
)
GO
-- ====================================================================
-- Insert test data
-- ====================================================================
insert tblPlayers values (1, 100001, 'Smith') -- Player 1 belongs to Team 100001
insert tblPlayers values (2, 100002, 'Jones') -- Player 2 belongs to Team 100002
insert tblGames values (1001,100001) -- Team 100001 played in game 1001
insert tblGames values (1002,100002) -- Team 100002 played in game 1002
insert tblRatings values (1001,1,6) -- Smithy rated a 6 in game 1001
insert tblRatings values (1002,2,1) -- Jonesy had a shocker in game 1002
insert tblRatings values (1002,1,8) -- Oops. Smithy's team didn't play in this game
Cheers,
- Mark
June 25, 2003 at 5:40 pm
Thanks mccork, that is exactly what I was looking for... I was unaware that you could use Functions in constraints...
Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply