Solved

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

Posted on 2011-02-12
13
1,220 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
  • 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
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.

 
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 Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 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