Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-12
13
Medium Priority
?
1,229 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
[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:Michael Sterling
ID: 34880007
@AriMc: do you have syntax you can show me?
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

660 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