Solved

What's wrong with my add foreign key statement?

Posted on 2011-02-10
21
446 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:mikesExpertExchange
  • 10
  • 9
  • 2
21 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34866785
I did not find fk_utinout_utathleteinfo in your code. Did you miss any other statements?
0
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 34866847
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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 34866884
@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
 
LVL 40

Expert Comment

by:Sharath
ID: 34866898
Can you post the definition of utsportteam table also?
0
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 34867079
In table dbo.utathleteinfo, there is no primary key define (as per your 1st error message)
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 34867844
@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
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 34868355

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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 34879482
@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
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 34879533
You can just run my scripts... you will have all.
It is tested and working fine and created table as you expected.
0
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 34879539
Scripts will delete all the tables(If exist) and create new with proper PK and FK
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 34879572
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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 34879579
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
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 34879621
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
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 34879665
In one table, you can create only promary key.
0
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 34879687
you need to change your table design...
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 34879711
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
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 34879728
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
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 34879730
or you can create unique key
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 34879775
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
 
LVL 9

Accepted Solution

by:
gdupadhyay earned 500 total points
ID: 34881193
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
 
LVL 1

Author Closing Comment

by:mikesExpertExchange
ID: 34993432
thank you for your help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Select Query help 3 31
Change the SSRS datasource server or connection string 10 16
Calculating Business Hours 19 63
Sql query 34 19
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now