Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-12
13
Medium Priority
?
1,230 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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
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.

877 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