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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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
All Courses

From novice to tech pro — start learning today.