CONSTRAINT Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CONSTRAINT Help

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-12 : 15:06:05

I have the following tables.
Team (PK TeamKey )
Player (PK PlayerKey, FK TeamKey)
Game (PK GameKey, FK Home_TeamKey, FK Away_TeamKey)
Matchup (PK MatchupKey, FK Player1_PlayerKey, FK Player2_PlayerKey, FK Player1_GameKey, FK Player2_GameKey)

I want to add a CONSTRAINT to Matchup.Player1_GameKey and Matchup.Player2_GameKey that will check if the correct GameKey is being used based on the Player's Team. Matchup.Player1_PlayerKey JOINs to Player.PlayerKey, Player.TeamKey joins to Game.Home_TeamKey and Game.Away_TeamKey.

Any help would be greatly appreciated. Thanks!

Serrano88
Starting Member

4 Posts

Posted - 2011-05-12 : 15:56:10

Something like the query below might do the trick. The example is for the Player1_GameKey column, but it could easily be altered for player2.

ALTER TABLE Matchup
ADD CONSTRAINT CK_GameKey_Player1GameKey
CHECK (Player1_GameKey =
( Select GameKey
From Game, Player, Matchup
Where (Game.Home_TeamKey = Player.TeamKey OR Game.Away_TeamKey = Player.TeamKey)
And Player.PlayerKey = Matchup.Player1_PlayerKey
)
)

If I haven't misunderstood your data design, then the constraint should join the player key from the matchup table to the player table, then the game table will determine the gamekey by joining to the team key on the player table.

Hope this works. If not TechRepublic has a pretty good article about contstraints that might be helpful:

http://www.techrepublic.com/article/how-do-i-enforce-domain-integrity-for-sql-server-data-using-check-constraints/6164469

__________________________________________________
For SQL Contractor and Consulting Support:
http://www.thedatasolutioncenter.com

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-12 : 16:32:35

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

I would start by normalizing the schema. Look for natural keys that have actual names instead of blindly adding a column that improperly ends in “_key” to every table.

Teams and players are entities; they get a table each.

CREATE TABLE Teams
(team_name CHAR(10) NOT NULL PRIMARY KEY);

CREATE TABLE Players
(player_name CHAR(10) NOT NULL PRIMARY KEY);

Relationships have their own tables. Teams have players. Games have teams.

CREATE TABLE Team_Assignments
(player_name CHAR(10) NOT NULL PRIMARY KEY,
REFERENCES Players(player_name),
team_name CHAR(10) NOT NULL
REFERENCES Teams(team_name));

CREATE TABLE Games
(game_date DATE NOT NULL,
home_team_name CHAR(10) NOT NULL
REFERENCES Teams (team_name),
away_team_name CHAR(10) NOT NULL
REFERENCES Teams (team_name),
CHECK (hone_team_name <> away_team_name),
PRIMARY KEY (game_date, home_team_name, away_team_name));

I have no idea what a “match up” is. Or why you need it.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-12 : 16:56:32

Thanks, let me to this.
CONSTRAINT CHK_Matchup_PlayerKeyGameKey1 CHECK ( dbo.fn_ValidatePlayerKeyGameKey(Player1_PlayerKey,Player1_GameKey) >= 1 )

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-12 : 17:16:11

Damn jcelko.
I posted in "New to SQL Server Programming" if you weren't aware and I kept my question brief without giving irrelevant information.

But if you would like that additional information...

All my tables are named in the singular form.
All tables have as their first field a Key field named TableNameKey which is set as an identity column, and is set as the Primary Key for the table.
To keep my structure consistent I use INT for my Keys since I intend to have 100,000,000 records.
Here is my Team table in case you were wondering.

CREATE TABLE dbo.Team (
    TeamKey INT IDENTITY(1,1) NOT NULL,
    URLKey INT NOT NULL DEFAULT(1),
    TeamSport NVARCHAR(3) NOT NULL DEFAULT('NFL'),
    TeamCity NVARCHAR(100) NOT NULL DEFAULT(''),
    TeamFullName NVARCHAR(50) NOT NULL DEFAULT(''),
    TeamDisplayName NVARCHAR(50) NOT NULL DEFAULT(''),
    ActiveIndicator BIT NOT NULL DEFAULT(1),
    LastUpdateDateTime DATETIME NOT NULL DEFAULT(GETDATE()),
    LastUpdatedBy_SystemUserKey INT NOT NULL DEFAULT(1),
    CONSTRAINT PK_Team PRIMARY KEY CLUSTERED ( TeamKey ASC )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY],
    CONSTRAINT FK_Team_URLKey FOREIGN KEY ( URLKey ) REFERENCES dbo.URL(URLKey),
    CONSTRAINT FK_Team_LastUpdatedBy_SystemUserKey FOREIGN KEY ( LastUpdatedBy_SystemUserKey ) REFERENCES dbo.SystemUser(SystemUserKey),
    CONSTRAINT CHK_Team_TeamSport CHECK ( TeamSport IN ( 'MLB', 'NBA', 'NFL', 'NHL' ) )
) ON [PRIMARY]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources