[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How do I create the foreign key or unique key for this situation?

Posted on 2011-02-12
13
Medium Priority
?
1,234 Views
Last Modified: 2012-05-11
I have two tables, table A has 1 field that is a primary key and another field that i want to simply make a non null unique key. How do i do this? (step one)

Same scenario for table B as above, without the 2nd field being unique, but with the addition of the fact that I want to create a constraint that will not allow a record to be entered into table B where the 2nd field does not match field 2 of the first table.

I'm using SQL Express 2008 RS
0
Comment
Question by:Michael Sterling
  • 8
  • 4
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34879973
you can create a unique constraint on the second field on tablea, and create the foreign key on tableb field to that second field of tableA ...
can you please clarify what the exact problem is you have?
0
 
LVL 9

Expert Comment

by:AriMc
ID: 34879996
create table a (prikeyfld int unique, secfld int unique, primary key (prikeyfld))

create table b (secfld int, foreign key (secfld) references a(secfld))
0
 
LVL 1

Author Comment

by:Michael Sterling
ID: 34880007
@AriMc: do you have syntax you can show me?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 1

Author Comment

by:Michael Sterling
ID: 34880009
@AriMc: do you have syntax you can show me?
0
 
LVL 9

Expert Comment

by:AriMc
ID: 34880024
What do you mean by syntax? The SQL-statements in my original reply are valid statements for MS SQL-Server or any SQL92-compliant database engine.

0
 
LVL 1

Author Comment

by:Michael Sterling
ID: 34880080
@AriMc: sorry...

these are my tables and the errors i'm getting trying to create the constraints...

--------------1ST TABLE-------------USE [studytimetracker]GO/****** Object:  Table [dbo].[utathleteinfo]    Script Date: 02/10/2011 15:59:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOEXEC 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'GOALTER TABLE [dbo].[utathleteinfo]  WITH CHECK ADD  CONSTRAINT [fk_utathleteinfo_utsportteam] FOREIGN KEY([SportUid])REFERENCES [dbo].[utsportteam] ([utSportTeamUid])GOALTER TABLE [dbo].[utathleteinfo] CHECK CONSTRAINT [fk_utathleteinfo_utsportteam]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Semes__6FE99F9F]  DEFAULT ((0)) FOR [SemesterTotal]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Warni__70DDC3D8]  DEFAULT ((0)) FOR [WarningCount]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__WeekT__71D1E811]  DEFAULT ('0') FOR [WeekTotal]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathl
ete__Requi__72C60C4A]  DEFAULT ((0)) FOR [RequiredHours]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Banke__73BA3083]  DEFAULT ((0)) FOR [BankedMinutes]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Total__74AE54BC]  DEFAULT ((0)) FOR [TotalMinutes]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[utinout] ADD  DEFAULT ((0)) FOR [TotalMinutes]GO---------ERROR MESSAGE-----------Msg 1776, Level 16, State 0, Line 2There 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 2Could not create constraint. See previous errors.Msg 4917, Level 16, State 0, Line 2Constraint 'fk_utinout_utathleteinfo' does not exist.Msg 4916, Level 16, State 0, Line 2Could not enable or disable the constraint. See previous errors.
0
 
LVL 1

Author Comment

by:Michael Sterling
ID: 34880081
@AriMc: sorry...

these are my tables and the errors i'm getting trying to create the constraints...

--------------1ST TABLE-------------USE [studytimetracker]GO/****** Object:  Table [dbo].[utathleteinfo]    Script Date: 02/10/2011 15:59:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOEXEC 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'GOALTER TABLE [dbo].[utathleteinfo]  WITH CHECK ADD  CONSTRAINT [fk_utathleteinfo_utsportteam] FOREIGN KEY([SportUid])REFERENCES [dbo].[utsportteam] ([utSportTeamUid])GOALTER TABLE [dbo].[utathleteinfo] CHECK CONSTRAINT [fk_utathleteinfo_utsportteam]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Semes__6FE99F9F]  DEFAULT ((0)) FOR [SemesterTotal]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Warni__70DDC3D8]  DEFAULT ((0)) FOR [WarningCount]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__WeekT__71D1E811]  DEFAULT ('0') FOR [WeekTotal]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathl
ete__Requi__72C60C4A]  DEFAULT ((0)) FOR [RequiredHours]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Banke__73BA3083]  DEFAULT ((0)) FOR [BankedMinutes]GOALTER TABLE [dbo].[utathleteinfo] ADD  CONSTRAINT [DF__utathlete__Total__74AE54BC]  DEFAULT ((0)) FOR [TotalMinutes]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[utinout] ADD  DEFAULT ((0)) FOR [TotalMinutes]GO---------ERROR MESSAGE-----------Msg 1776, Level 16, State 0, Line 2There 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 2Could not create constraint. See previous errors.Msg 4917, Level 16, State 0, Line 2Constraint 'fk_utinout_utathleteinfo' does not exist.Msg 4916, Level 16, State 0, Line 2Could not enable or disable the constraint. See previous errors.
0
 
LVL 9

Expert Comment

by:AriMc
ID: 34880120
Sorry but that script isn't exatly what I'd call readable. It's also not compliant to any SQL-standard, just to one version of MS SQL-Server.

I'd recommend using standard SQL to create tables. That way you can get help more easily and you're not dependant on one database engine.

0
 
LVL 1

Author Comment

by:Michael Sterling
ID: 34880134
k...i sent from my phone i'll continue when i'm in front of a computer
0
 
LVL 1

Author Comment

by:Michael Sterling
ID: 34880866
sorry about the jibbirish above, see the code attached below. is this not standard SQL? I've also attatched the script i try to run to add the foreign keys, the first one runs just fine but the second one throws the error that is also in the attached code. Any help would be appreciated
---------------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


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

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 9

Accepted Solution

by:
AriMc earned 2000 total points
ID: 34882384
Try adding UNIQUE specifier in the first table:

      [AthleteUid] [varchar](8) NOT NULL UNIQUE,
0
 
LVL 1

Author Comment

by:Michael Sterling
ID: 34883090
will do...
0
 
LVL 1

Author Closing Comment

by:Michael Sterling
ID: 34911649
that was it! thanks for your patience, advice and tenacity.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

591 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