Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have seen several posts related to this on various forms. I have yet to find a satisfactory answer so I am hoping I can find some answers here.
I am trying to join two tables in my database using SQL Server Management Studio 2008.
One table looks like this:
Key	League_ID	nvarchar(7)
Key	Team_ID	        nvarchar(7)
Key	Season		nvarchar(4)
Key	Session		nvarchar(1)
Key	Player_ID	int
	Player		nvarchar(64)
	W		int
	L		int
The other looks like this:
Key	Team_ID	        nvarchar(7)
	Team Name	nvarchar(64)
Key	League_ID	nvarchar(7)
It is a one (Teams) to many (Pit_Output) relationship.
From the design view of the Pit_Output table, I click on the relationship tool and add a relationship using the Team_ID and League_ID columns with Teams as the primary key table and Pit_Output as the foreign key table
 
My first question is – Am I doing this correctly. The second question is, why do I get the following dialog message: The column in table 'Teams' do not match an existing primary key or UNIQUE constraint.
Posted 29-Dec-12 5:06am
MrSME286
Edited 29-Dec-12 7:35am
(no name)499K
v2
Comments
RajAcharya at 31-Dec-12 5:37am
   
In your "Team" table column "Team_Id" is primary key? If no, then first set the primary key for "Team_ID" column in Team table and then try again.
MrSME at 1-Jan-13 10:05am
   
Thanks. When I made your suggested change, it worked perfectly. However, I thought that my primary key should be a composite key since the Teams table could have records where the Team_ID is the same but the League_ID's are different. Should my primary key include all of the columns that insure uniqueness?
RajAcharya at 2-Jan-13 0:17am
   
Welcome. It's depend on the requirement whether you use single column primary key or composite primary key. For insure unique value in your column you can use unique key constraint for your column in which you want to store unique value.
MrSME at 2-Jan-13 6:24am
   
I think that this will work for me. Should I conclude that I cannot create a foreign key this way if I am using a composite primary key?
RajAcharya at 3-Jan-13 2:00am
   
You can create foreign key using composite primary key. Only thing is that you need to use same column combination which you use to define composite primary key.
MrSME at 3-Jan-13 20:50pm
   
This is what I started with. Team_ID and League_Id were the primary key for the Teams table and the foreign key used those 2 fields. I thought that would work but it caused the error dialog I described.
RajAcharya at 4-Jan-13 2:20am
   
Can you please paste here you sample code using which you are trying to create both table.
MrSME at 4-Jan-13 19:31pm
   
<pre lang="sql">
<pre lang="text">
'PIT_OUTPUT
</pre>
 
<pre lang="c++">CREATE TABLE [dbo].[Pit_Output](
[League_ID] [nvarchar](7) NOT NULL,
[Team_ID] [nvarchar](7) NOT NULL,
[Season] [nvarchar](4) NOT NULL,
[Session] [nvarchar](1) NOT NULL,
[Player_ID] [int] NOT NULL,
[Player] [nvarchar](64) NULL,
[W] [int] NOT NULL,
[L] [int] NULL,
[ERA] [float] NULL,
[G] [int] NULL,
[GS] [int] NULL,
[CG] [int] NULL,
[Sho] [int] NULL,
[Sv] [int] NULL,
[BS] [int] NULL,
[IP] [float] NULL,
[TBF] [int] NULL,
[H] [int] NULL,
[R] [int] NULL,
[ER] [int] NULL,
[HR] [int] NULL,
[BB] [int] NULL,
[IBB] [int] NULL,
[HBP] [int] NULL,
[WP] [int] NULL,
[BK] [int] NULL,
[SO] [int] NULL,
[SF] [int] NULL,
[SH] [int] NULL,
[upsize_ts] [timestamp] NULL,
CONSTRAINT [PK_Pit_Output] PRIMARY KEY CLUSTERED
(
[League_ID] ASC,
[Team_ID] ASC,
[Season] ASC,
[Session] ASC,
[Player_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
</pre>
 

'TEAMS
 
<pre lang="c++">CREATE TABLE [dbo].[Teams](
[Team ID] [nvarchar](7) NOT NULL,
[Team City] [nvarchar](64) NULL,
[Team Name] [nvarchar](64) NULL,
[League ID] [nvarchar](7) NOT NULL,
[Franchise ID] [nvarchar](7) NULL,
[Short] [nvarchar](8) NULL,
CONSTRAINT [PK_Teams_1] PRIMARY KEY CLUSTERED
(
[Team ID] ASC,
[League ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]</pre>
</pre>
RajAcharya at 4-Jan-13 23:35pm
   
Please try following. Hope it will work for you.
CREATE TABLE [dbo].[Pit_Output](
[League_ID] [nvarchar](7) NOT NULL,
[Team_ID] [nvarchar](7) NOT NULL,
[Season] [nvarchar](4) NOT NULL,
[Session] [nvarchar](1) NOT NULL,
[Player_ID] [int] NOT NULL,
[Player] [nvarchar](64) NULL,
[W] [int] NOT NULL, [L] [int] NULL,
[ERA] [float] NULL, [G] [int] NULL,
[GS] [int] NULL,
[CG] [int] NULL,
[Sho] [int] NULL,
[Sv] [int] NULL,
[BS] [int] NULL,
[IP] [float] NULL,
[TBF] [int] NULL,
[H] [int] NULL,
[R] [int] NULL,
[ER] [int] NULL,
[HR] [int] NULL,
[BB] [int] NULL,
[IBB] [int] NULL,
[HBP] [int] NULL,
[WP] [int] NULL,
[BK] [int] NULL,
[SO] [int] NULL,
[SF] [int] NULL,
[SH] [int] NULL,
[upsize_ts] [timestamp] NULL,
CONSTRAINT PK_Pit_Output PRIMARY KEY(League_ID, Team_ID)
)
------------------------------------------------------------------------
CREATE TABLE [dbo].[Teams](
[Team_ID] [nvarchar](7) NOT NULL,
[Team City] [nvarchar](64) NULL,
[Team Name] [nvarchar](64) NULL,
[League_ID] [nvarchar](7) NOT NULL,
[Franchise ID] [nvarchar](7) NULL,
[Short] [nvarchar](8) NULL,
CONSTRAINT [PK_Teams_1] foreign key(League_ID, Team_ID) references Pit_Output(League_ID, Team_ID)
)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 George Jonsson 175
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 29 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100