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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy