What's wrong with my add foreign key statement?

i'm including two table definitions and an error message i'm getting when trying to create a foreign key relationship on the AthleteUid column. I have no idea how to solve it. I think i understand what the messaged is saying but it seems that I just can't get it resolved, help please and thank you.
---------------1ST TABLE-------------

USE [studytimetracker]
GO

/****** Object:  Table [dbo].[utathleteinfo]    Script Date: 02/10/2011 15:59:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[utathleteinfo](
	[AthleteInfoUid] [int] NOT NULL,
	[AthleteUid] [varchar](8) NOT NULL,
	[AthleteSSN] [varchar](11) NULL,
	[AthleteLastName] [varchar](25) NULL,
	[AthleteFirstName] [varchar](25) NULL,
	[AthleteName] [varchar](50) NULL,
	[SemesterTotal] [int] NULL,
	[WarningCount] [int] NULL,
	[Notes] [varchar](255) NULL,
	[WeekTotal] [varchar](50) NULL,
	[RequiredHours] [int] NULL,
	[BankedMinutes] [int] NULL,
	[BankedTime] [varchar](50) NULL,
	[SportUid] [int] NOT NULL,
	[TotalMinutes] [int] NULL,
	[SignedIn] [bit] NULL,
	[UsedBankedMins] [int] NULL,
	[GPA] [varchar](4) NULL,
	[Major] [varchar](45) NULL,
	[Active] [bit] NULL,
 CONSTRAINT [PK_utathleteinfo] PRIMARY KEY CLUSTERED 
(
	[AthleteInfoUid] 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

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'						' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'utathleteinfo', @level2type=N'COLUMN',@level2name=N'SemesterTotal'
GO

ALTER TABLE [dbo].[utathleteinfo]  WITH CHECK ADD  CONSTRAINT [fk_utathleteinfo_utsportteam] FOREIGN KEY([SportUid])
REFERENCES [dbo].[utsportteam] ([utSportTeamUid])
GO

ALTER TABLE [dbo].[utathleteinfo] CHECK CONSTRAINT [fk_utathleteinfo_utsportteam]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Semes__6FE99F9F]  DEFAULT ((0)) FOR [SemesterTotal]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Warni__70DDC3D8]  DEFAULT ((0)) FOR [WarningCount]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__WeekT__71D1E811]  DEFAULT ('0') FOR [WeekTotal]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Requi__72C60C4A]  DEFAULT ((0)) FOR [RequiredHours]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Banke__73BA3083]  DEFAULT ((0)) FOR [BankedMinutes]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Total__74AE54BC]  DEFAULT ((0)) FOR [TotalMinutes]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__UsedB__75A278F5]  DEFAULT ((0)) FOR [UsedBankedMins]
GO



---------2ND TABLE----------

USE [studytimetracker]
GO

/****** Object:  Table [dbo].[utinout]    Script Date: 02/10/2011 16:12:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[utinout](
	[InOutAUid] [int] IDENTITY(1,1) NOT NULL,
	[AthleteUid] [varchar](8) NOT NULL,
	[StudentName] [varchar](50) NULL,
	[InTime] [datetime2](7) NOT NULL,
	[OutTime] [datetime2](7) NULL,
	[TotalTime] [varchar](8) NULL,
	[AtAProgram] [bit] NULL,
	[SignInOutNotes] [varchar](255) NULL,
	[TotalMinutes] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[InOutAUid] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[utinout] ADD  DEFAULT ((0)) FOR [TotalMinutes]
GO

---------ERROR MESSAGE-----------

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'dbo.utathleteinfo' that match the referencing column list in the foreign key 'fk_utinout_utathleteinfo'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'fk_utinout_utathleteinfo' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.

Open in new window

LVL 1
Michael SterlingWeb Applications DeveloperAsked:
Who is Participating?
 
gdupadhyayConnect With a Mentor Commented:
Suggestion 1:

In current design:
Before insert into Table: utinout, Check AthleteInfoUid in table utathleteinfo

If AthleteInfoUid exist in table: utathleteinfo
      insert into utinout
else
      don't insert and send error message

Note: You have to do all above validation in the stored procedure.

-------------------------------------------------------------------------------------------------------------------------------------------

Suggestion: 2

Remove column AthleteUid from utInOut table.

Add AthleteInfoUid: PK of utathleteinfo table.

Please: See the attached diagram pic and script.


For Insert into utInOut: Read AthleteInfoUid from table:utathleteinfo
      based on SportUid and AthleteUid
      OR AthleteUid

I am sending new script for Suggestion 2.

Now it is up to you, how you want to do.  I like Suggestion 2, but you have to change the design.

Unique key or trigger: Will make it more complex.
USE [studytimetracker]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utathleteinfo]') AND type in (N'U'))
drop table [dbo].[utathleteinfo]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utinout]') AND type in (N'U'))
drop table [dbo].[utinout] 
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utsportteam]') AND type in (N'U'))
drop table [dbo].[utsportteam]
GO

-- Table -1

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[utsportteam](
	[utSportTeamUid]			[int] IDENTITY(1,1) NOT NULL,
	[SportAcr]					[varchar](4) NOT NULL,
	[SportTeamName]				[varchar](45) NOT NULL,
	[CompetitionWeek]			[bit] NULL,
	[DisplayOrder]				[int] NULL,
	PRIMARY KEY CLUSTERED 
(
	[utSportTeamUid] 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

SET ANSI_PADDING OFF
GO


/****** Object:  Table [dbo].[utathleteinfo]    Script Date: 02/10/2011 15:59:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[utathleteinfo](
	[AthleteInfoUid]		[int] NOT NULL,
	[SportUid]				[int] NOT NULL,
	[AthleteUid]			[varchar](8) NOT NULL,
	[AthleteSSN]			[varchar](11) NULL,
	[AthleteLastName]		[varchar](25) NULL,
	[AthleteFirstName]		[varchar](25) NULL,
	[AthleteName]			[varchar](50) NULL,
	[SemesterTotal]			[int] NULL,
	[WarningCount]			[int] NULL,
	[Notes]					[varchar](255) NULL,
	[WeekTotal]				[varchar](50) NULL,
	[RequiredHours]			[int] NULL,
	[BankedMinutes]			[int] NULL,
	[BankedTime]			[varchar](50) NULL,
	[TotalMinutes]			[int] NULL,
	[SignedIn]				[bit] NULL,
	[UsedBankedMins]		[int] NULL,
	[GPA]					[varchar](4) NULL,
	[Major]					[varchar](45) NULL,
	[Active]				[bit] NULL,
 CONSTRAINT [PK_utathleteinfo] PRIMARY KEY CLUSTERED 
(
	[AthleteInfoUid] 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

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'						' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'utathleteinfo', @level2type=N'COLUMN',@level2name=N'SemesterTotal'
GO

ALTER TABLE [dbo].[utathleteinfo]  WITH CHECK ADD  CONSTRAINT [fk_utathleteinfo_utsportteam] FOREIGN KEY([SportUid])
REFERENCES [dbo].[utsportteam] ([utSportTeamUid])
GO

ALTER TABLE [dbo].[utathleteinfo] CHECK CONSTRAINT [fk_utathleteinfo_utsportteam]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Semes__6FE99F9F]  DEFAULT ((0)) FOR [SemesterTotal]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Warni__70DDC3D8]  DEFAULT ((0)) FOR [WarningCount]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__WeekT__71D1E811]  DEFAULT ('0') FOR [WeekTotal]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Requi__72C60C4A]  DEFAULT ((0)) FOR [RequiredHours]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Banke__73BA3083]  DEFAULT ((0)) FOR [BankedMinutes]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Total__74AE54BC]  DEFAULT ((0)) FOR [TotalMinutes]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__UsedB__75A278F5]  DEFAULT ((0)) FOR [UsedBankedMins]
GO


---------2ND TABLE----------

/****** Object:  Table [dbo].[utinout]    Script Date: 02/10/2011 16:12:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[utinout](
	[InOutAUid]				[int] IDENTITY(1,1) NOT NULL,
	[AthleteInfoUid]		[int] NOT NULL,
	[StudentName]			[varchar](50) NULL,
	[InTime]				[datetime2](7) NOT NULL,
	[OutTime]				[datetime2](7) NULL,
	[TotalTime]				[varchar](8) NULL,
	[AtAProgram]			[bit] NULL,
	[SignInOutNotes]		[varchar](255) NULL,
	[TotalMinutes]			[int] NULL,
PRIMARY KEY CLUSTERED 
(
	[InOutAUid] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[utinout] ADD  DEFAULT ((0)) FOR [TotalMinutes]
GO

ALTER TABLE [dbo].[utinout]  WITH CHECK ADD  CONSTRAINT [FK_AthleteInfoUid] FOREIGN KEY([AthleteInfoUid])
REFERENCES [dbo].[utathleteinfo] ([AthleteInfoUid])
GO
ALTER TABLE [dbo].[utinout] CHECK CONSTRAINT [FK_AthleteInfoUid]
GO

Open in new window

Table.JPG
0
 
SharathData EngineerCommented:
I did not find fk_utinout_utathleteinfo in your code. Did you miss any other statements?
0
 
gdupadhyayCommented:
Your all scripts is looking good except:


ALTER TABLE [dbo].[utathleteinfo]  WITH CHECK ADD  CONSTRAINT [fk_utathleteinfo_utsportteam] FOREIGN KEY([SportUid])
REFERENCES [dbo].[utsportteam] ([utSportTeamUid])
GO

Please check table [dbo].[utsportteam] and the primary key.
table [dbo].[utsportteam] should have promary key, which is FK in other table
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@Sharath: see attached code. i forgot to include my FK statements. the first one works fine, but the second one bombs, with the error.

@qdupadhyay: i don't follow, which table do you mean, when you say "other table" in this statement:

"Please check table [dbo].[utsportteam] and the primary key.
table [dbo].[utsportteam] should have promary key, which is FK in other table"
USE [studytimetracker]
GO

ALTER TABLE [dbo].[utathleteinfo]  WITH CHECK ADD  CONSTRAINT [fk_utathleteinfo_utsportteam] FOREIGN KEY([SportUid])
REFERENCES [dbo].[utsportteam] ([utSportTeamUid])
GO

ALTER TABLE [dbo].[utathleteinfo] CHECK CONSTRAINT [fk_utathleteinfo_utsportteam]
GO

--------------------------------------------------------

USE [studytimetracker]
GO

ALTER TABLE [dbo].[utinout] WITH CHECK ADD CONSTRAINT [fk_utinout_utathleteinfo] FOREIGN KEY([AthleteUid])
REFERENCES [dbo].[utathleteinfo] ([AthleteUid])
GO

ALTER TABLE [dbo].[utinout] CHECK CONSTRAINT [fk_utinout_utathleteinfo]
GO

------------ERROR MESSAGE-------------------------
---------ERROR MESSAGE-----------

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'dbo.utathleteinfo' that match the referencing column list in the foreign key 'fk_utinout_utathleteinfo'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'fk_utinout_utathleteinfo' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.

Open in new window

0
 
SharathData EngineerCommented:
Can you post the definition of utsportteam table also?
0
 
gdupadhyayCommented:
In table dbo.utathleteinfo, there is no primary key define (as per your 1st error message)
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@Sharath: See code attached

@qdupadhyay: doesn't this define the primary key:

 CONSTRAINT [PK_utathleteinfo] PRIMARY KEY CLUSTERED
(
      [AthleteInfoUid] 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

USE [studytimetracker]
GO

/****** Object:  Table [dbo].[utsportteam]    Script Date: 02/10/2011 20:04:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[utsportteam](
	[utSportTeamUid] [int] IDENTITY(1,1) NOT NULL,
	[SportAcr] [varchar](4) NOT NULL,
	[SportTeamName] [varchar](45) NOT NULL,
	[CompetitionWeek] [bit] NULL,
	[DisplayOrder] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[utSportTeamUid] 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

SET ANSI_PADDING OFF
GO

Open in new window

0
 
gdupadhyayCommented:

I have tested scripts, its created 3 table without any error. please run scripts in sql server.
USE [studytimetracker]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utathleteinfo]') AND type in (N'U'))
drop table [dbo].[utathleteinfo]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utinout]') AND type in (N'U'))
drop table [dbo].[utinout] 
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utsportteam]') AND type in (N'U'))
drop table [dbo].[utsportteam]
GO

-- Table -1

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[utsportteam](
	[utSportTeamUid] [int] IDENTITY(1,1) NOT NULL,
	[SportAcr] [varchar](4) NOT NULL,
	[SportTeamName] [varchar](45) NOT NULL,
	[CompetitionWeek] [bit] NULL,
	[DisplayOrder] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[utSportTeamUid] 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

SET ANSI_PADDING OFF
GO


/****** Object:  Table [dbo].[utathleteinfo]    Script Date: 02/10/2011 15:59:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[utathleteinfo](
	[AthleteInfoUid] [int] NOT NULL,
	[AthleteUid] [varchar](8) NOT NULL,
	[AthleteSSN] [varchar](11) NULL,
	[AthleteLastName] [varchar](25) NULL,
	[AthleteFirstName] [varchar](25) NULL,
	[AthleteName] [varchar](50) NULL,
	[SemesterTotal] [int] NULL,
	[WarningCount] [int] NULL,
	[Notes] [varchar](255) NULL,
	[WeekTotal] [varchar](50) NULL,
	[RequiredHours] [int] NULL,
	[BankedMinutes] [int] NULL,
	[BankedTime] [varchar](50) NULL,
	[SportUid] [int] NOT NULL,
	[TotalMinutes] [int] NULL,
	[SignedIn] [bit] NULL,
	[UsedBankedMins] [int] NULL,
	[GPA] [varchar](4) NULL,
	[Major] [varchar](45) NULL,
	[Active] [bit] NULL,
 CONSTRAINT [PK_utathleteinfo] PRIMARY KEY CLUSTERED 
(
	[AthleteInfoUid] 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

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'						' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'utathleteinfo', @level2type=N'COLUMN',@level2name=N'SemesterTotal'
GO

ALTER TABLE [dbo].[utathleteinfo]  WITH CHECK ADD  CONSTRAINT [fk_utathleteinfo_utsportteam] FOREIGN KEY([SportUid])
REFERENCES [dbo].[utsportteam] ([utSportTeamUid])
GO

ALTER TABLE [dbo].[utathleteinfo] CHECK CONSTRAINT [fk_utathleteinfo_utsportteam]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Semes__6FE99F9F]  DEFAULT ((0)) FOR [SemesterTotal]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Warni__70DDC3D8]  DEFAULT ((0)) FOR [WarningCount]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__WeekT__71D1E811]  DEFAULT ('0') FOR [WeekTotal]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Requi__72C60C4A]  DEFAULT ((0)) FOR [RequiredHours]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Banke__73BA3083]  DEFAULT ((0)) FOR [BankedMinutes]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Total__74AE54BC]  DEFAULT ((0)) FOR [TotalMinutes]
GO

ALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__UsedB__75A278F5]  DEFAULT ((0)) FOR [UsedBankedMins]
GO


---------2ND TABLE----------

/****** Object:  Table [dbo].[utinout]    Script Date: 02/10/2011 16:12:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[utinout](
	[InOutAUid] [int] IDENTITY(1,1) NOT NULL,
	[AthleteUid] [varchar](8) NOT NULL,
	[StudentName] [varchar](50) NULL,
	[InTime] [datetime2](7) NOT NULL,
	[OutTime] [datetime2](7) NULL,
	[TotalTime] [varchar](8) NULL,
	[AtAProgram] [bit] NULL,
	[SignInOutNotes] [varchar](255) NULL,
	[TotalMinutes] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[InOutAUid] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[utinout] ADD  DEFAULT ((0)) FOR [TotalMinutes]
GO

Open in new window

0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@qdupadhyay: i deleted the tables and recreated them. i ran the following:

ALTER TABLE [dbo].[utinout] WITH CHECK ADD CONSTRAINT [fk_utinout_utathleteinfo] FOREIGN KEY([AthleteUid])
REFERENCES [dbo].[utathleteinfo] ([AthleteUid])
GO

ALTER TABLE [dbo].[utinout] CHECK CONSTRAINT [fk_utinout_utathleteinfo]
GO


 to try an add the foreign key that i need. it still failed with the following error:

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'dbo.utathleteinfo' that match the referencing column list in the foreign key 'fk_utinout_utathleteinfo'.
 

what did i do wrong?
0
 
gdupadhyayCommented:
You can just run my scripts... you will have all.
It is tested and working fine and created table as you expected.
0
 
gdupadhyayCommented:
Scripts will delete all the tables(If exist) and create new with proper PK and FK
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
ok i did copy and run your scripts as is from here. this is my question about the scripts. i'm trying to create a constraint that won't allow you to add a record into the utInOut table if the AthleteUid does not exist int he utAthleteInfo table. where does it say that in the scripts that you gave me?
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
sorry about the bad spelling above, this:

"i'm trying to create a constraint that won't allow you to add a record into the utInOut table if the AthleteUid does not exist int he utAthleteInfo table."

should read like this:

"i'm trying to create a constraint that won't allow you to add a record into the utInOut table if the AthleteUid does not exist in the utAthleteInfo table."
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
for example with no data in either the utAthleteInfo table or in the utInOut table, the following should fail:

insert into utinout (AthleteUid, InTime, SignInOutNotes) VALUES('TST12345', '2011-02-12 09:10:41.0000000', 'TEST')

but it didn't. it allowed the record to be inserted even though 'TST12345' does not exist in the utAthleteInfo table
0
 
gdupadhyayCommented:
In one table, you can create only promary key.
0
 
gdupadhyayCommented:
you need to change your table design...
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
i see. so because i already have a primary key on the utInOut table, (InOutAUid), I can't have another primary key. Ok, I see the problem and yes I do need to change my table design. Any suggestions? My goal is to make sure that records inserted into the utInOut table have a AthleteUid that exists in the utAthleteInfo table. What would be an easier way to achieve this? The utInOut table will / can contain multiple records with the the same AthleteUid, so i can't make i a primary key.
0
 
gdupadhyayCommented:
You can write trigger (insert, update and delete) on any table. But this question is related to PK and FK.
I believe I gave you enough information for this question.
0
 
gdupadhyayCommented:
or you can create unique key
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
ok i like the unique key route. do i just create that on the AthleteUid in the utInOut table? how do i do this?
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
thank you for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.