Solved

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

Posted on 2011-02-12
13
1,225 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:mikesExpertExchange
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:mikesExpertExchange
ID: 34880007
@AriMc: do you have syntax you can show me?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:mikesExpertExchange
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:mikesExpertExchange
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:mikesExpertExchange
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:mikesExpertExchange
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:mikesExpertExchange
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 500 total points
ID: 34882384
Try adding UNIQUE specifier in the first table:

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

Author Comment

by:mikesExpertExchange
ID: 34883090
will do...
0
 
LVL 1

Author Closing Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

752 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