Sql-server – Clarify – Need to add Multiple foreign key constraints

sql serversql-server-2008

I am not sure if I am thinking about this process the wrong way.

Just to Surmise if I create a table(TeamList) whose sole purpose is to capture the coaches team list of players and positions for a particular round and date.

I have a Fk from PlayersTeams – A junction table that defines which players play for what teams.

A round table Fk – defines what round the list is for

In the TeamList table I have the two aforementioned columns, plus a date (mm/dd/yyyy) column, a TeamName column, and 17 player position columns.

To ensure that each player in the team list is only from the same team do I need 17 foreign key constraints from PlayersTeams(Fk) for each position to ensure that only a player from the selected team can be selected in a given position?

have I got something minged up?

Edit as below.

USE [NRL_DataMartDesign]
GO

/****** Object:  Table [dbo].[Junct_PlayersTeams]    Script Date: 05/02/2012 14:11:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Junct_PlayersTeams](
    [TeamPlayer] [int] IDENTITY(1,1) NOT NULL,
    [fk_Players] [int] NULL,
    [fk_Teams] [int] NULL,
    [startDate] [datetime] NULL,
    [endDate] [datetime] NULL,
 CONSTRAINT [PK_Junct_PlayersTeams] PRIMARY KEY CLUSTERED 
(
    [TeamPlayer] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Junct_PlayersTeams]  WITH CHECK ADD  CONSTRAINT [FK_Junct_PlayersTeams_Players] FOREIGN KEY([fk_Players])
REFERENCES [dbo].[Players] ([PLayerID])
GO

ALTER TABLE [dbo].[Junct_PlayersTeams] CHECK CONSTRAINT [FK_Junct_PlayersTeams_Players]
GO

ALTER TABLE [dbo].[Junct_PlayersTeams]  WITH CHECK ADD  CONSTRAINT [FK_Junct_PlayersTeams_Team] FOREIGN KEY([fk_Teams])
REFERENCES [dbo].[Team] ([TeamID])
GO

ALTER TABLE [dbo].[Junct_PlayersTeams] CHECK CONSTRAINT [FK_Junct_PlayersTeams_Team]
GO

Team List Table

USE [NRL_DataMartDesign]
GO

/****** Object:  Table [dbo].[TeamList]    Script Date: 05/02/2012 14:13:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TeamList](
    [TeamListID] [int] IDENTITY(1,1) NOT NULL,
    [Round(Fk)] [int] NULL,
    [Team(Fk)] [int] NULL,
    [Coach(Fk)] [int] NULL,
    [Fullback] [int] NULL,
    [LeftWing] [int] NULL,
    [LeftCentre] [int] NULL,
    [RightCentre] [int] NULL,
    [RightWing] [int] NULL,
    [FiveEighth] [int] NULL,
    [HalfBack] [int] NULL,
    [Lock] [int] NULL,
    [SecondRow(L)] [int] NULL,
    [SecondRow(R)] [int] NULL,
    [Prop(L)] [int] NULL,
    [Hooker] [int] NULL,
    [Prop(R)] [int] NULL,
    [Interchange1] [int] NULL,
    [Interchange2] [int] NULL,
    [Interchange3] [int] NULL,
    [Interchange4] [int] NULL,
 CONSTRAINT [PK_TeamList] PRIMARY KEY CLUSTERED 
(
    [TeamListID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Junc_TeamCoach] FOREIGN KEY([Coach(Fk)])
REFERENCES [dbo].[Junc_TeamCoach] ([Junct_TeamCoach])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Junc_TeamCoach]
GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Junct_PlayersTeams] FOREIGN KEY([Team(Fk)])
REFERENCES [dbo].[Junct_PlayersTeams] ([TeamPlayer])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Junct_PlayersTeams]
GO

Best Answer

Instead of one column per position in TeamList, you can use lookup tables for Positions and Players. The TeamList table then defines which player is in which team and in what position for each round. You then don't need to enforce integrity on all players being from the same team because there is one row per player that defines the player:team relationship in TeamList (playerID and teamID).

I've included some DDL below as an example - you may need to add more data columns,etc. but it should be a good starting point.

Positions table:

 CREATE TABLE [dbo].[Positions]
    (
        [PositionID] [int] NOT NULL,
        [position_name] [varchar](20) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [PositionID] ASC
    )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

The players table can be created as (not including team associations, but you could add this here):

CREATE TABLE [dbo].[Players](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [player_name] [varchar](500) NULL,
PRIMARY KEY CLUSTERED 
(
    [playerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

You can then create your TeamList table like so 

 CREATE TABLE [dbo].[TeamList]
    (
    [TeamListID] [int] IDENTITY(1,1) NOT NULL,
    [Round(Fk)] [int] NULL,
    [Team(Fk)] [int] NULL,
    [Coach(Fk)] [int] NULL,
    [PositionID] int null,
    [PlayerID] int not null,
    CONSTRAINT [PK_TeamList] PRIMARY KEY CLUSTERED 
    (
    [TeamListID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Positions] FOREIGN KEY([Position])
    REFERENCES [dbo].[Positions] ([PositionID])
    GO

    ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Positions]
    GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Players] FOREIGN KEY([PlayerID])
REFERENCES [dbo].[Players] ([playerID])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Players]
GO

-- Original FK definitions on round, team, coach, etc. go here
Related Question