One more thing about recreating foreign keys

Question

Post reply

One more thing about recreating foreign keys

MrBaseball34

SSCrazy

Points: 2515
More actions
August 13, 2008 at 9:45 am

#195832

When trying to recreate foreign key FK_ptPhysicalExam_ptDemographics in the
following code, I'm getting this error:
There are no primary or candidate keys in the referenced table 'dbo.ptPhysicalExam' that match the
referencing column list in the foreign key 'FK_ptPhysicalExamDetails_ptPhysicalExam'.
BOTH TABLES ARE EMPTY.
Any ideas?
--**************************************
-- ptPhysicalExam
--**************************************
-- Drop FK_ptPhysicalExamDetails_ptPhysicalExam from ptPhysicalExamDetails
Print 'Drop FK_ptPhysicalExamDetails_ptPhysicalExam from ptPhysicalExamDetails'
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ptPhysicalExamDetails_ptPhysicalExam]')
AND parent_object_id = OBJECT_ID(N'[dbo].[ptPhysicalExamDetails]'))
ALTER TABLE [dbo].[ptPhysicalExamDetails] DROP CONSTRAINT [FK_ptPhysicalExamDetails_ptPhysicalExam]
GO
-- Drop PK_ptPhysicalExam from ptPhysicalExam
Print 'Drop PK_ptPhysicalExam from ptPhysicalExam'
GO
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].ptPhysicalExam')
AND [type]='U'))
AND (EXISTS (SELECT * FROM sys.objects
WHERE [object_id]=OBJECT_ID(N'[dbo].PK_ptPhysicalExam')
AND [parent_object_id]=OBJECT_ID(N'[dbo].ptPhysicalExam')))
ALTER TABLE [dbo].ptPhysicalExam DROP CONSTRAINT PK_ptPhysicalExam
GO
-- Altering ptPhysicalExam
Print 'Altering ptPhysicalExam'
ALTER TABLE ptPhysicalExam ALTER COLUMN MRN varchar(25) NOT NULL
GO
-- Updating ptPhysicalExam
Print 'Updating ptPhysicalExam'
UPDATE ptPhysicalExam SET MRN = RTRIM(MRN)
GO
-- Create Index PK_ptPhysicalExam on ptPhysicalExam
Print 'Create Index PK_ptPhysicalExam on ptPhysicalExam'
GO
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[ptPhysicalExam]') AND [type]='U'))
AND NOT (EXISTS (SELECT * FROM sys.indexes WHERE [name]=N'PK_ptPhysicalExam'
AND [object_id]=OBJECT_ID(N'[dbo].[ptPhysicalExam]')))
CREATE INDEX [PK_ptPhysicalExam]
ON [dbo].[ptPhysicalExam] ([PEID] ASC)
GO
-- Create Foreign Key FK_ptPhysicalExamDetails_ptPhysicalExam on ptPhysicalExam
Print 'Create Foreign Key FK_ptPhysicalExamDetails_ptPhysicalExam on ptPhysicalExam'
GO
IF OBJECT_ID(N'[dbo].[ptPhysicalExam]') IS NOT NULL
AND OBJECT_ID(N'[dbo].[ptPhysicalExam]') IS NOT NULL
AND NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id]=OBJECT_ID(N'[dbo].[FK_ptPhysicalExam_ptDemographics]')
AND [parent_object_id]=OBJECT_ID(N'[dbo].[ptPhysicalExam]'))
BEGIN
ALTER TABLE [dbo].[ptPhysicalExamDetails] WITH CHECK ADD CONSTRAINT [FK_ptPhysicalExamDetails_ptPhysicalExam] FOREIGN KEY([PEID])
REFERENCES [dbo].[ptPhysicalExam] ([PEID])
END
GO
--
-- Foreign Key FK_ptPhysicalExam_ptDemographics dropped earlier in script
--
-- Create Foreign Key FK_ptPhysicalExam_ptDemographics on ptPhysicalExam
Print 'Create Foreign Key FK_ptPhysicalExam_ptDemographics on ptPhysicalExam'
GO
IF OBJECT_ID(N'[dbo].[ptPhysicalExam]') IS NOT NULL
AND OBJECT_ID(N'[dbo].[ptDemographics]') IS NOT NULL
AND NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id]=OBJECT_ID(N'[dbo].[FK_ptPhysicalExam_ptDemographics]')
AND [parent_object_id]=OBJECT_ID(N'[dbo].[ptPhysicalExam]'))
BEGIN
ALTER TABLE [dbo].[ptPhysicalExam]
ADD CONSTRAINT [FK_ptPhysicalExam_ptDemographics]
FOREIGN KEY ([MRN]) REFERENCES [dbo].[ptDemographics] ([MRN])
END
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 1

It might help if I use the correct names for everything. (No, still get the errors)

--**************************************

-- ptPhysicalExam

--**************************************

-- Drop FK_ptPhysicalExamDetails_ptPhysicalExam from ptPhysicalExamDetails

Print 'Drop FK_ptPhysicalExamDetails_ptPhysicalExam from ptPhysicalExamDetails'

GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ptPhysicalExamDetails_ptPhysicalExam]')

AND parent_object_id = OBJECT_ID(N'[dbo].[ptPhysicalExamDetails]'))

ALTER TABLE [dbo].[ptPhysicalExamDetails] DROP CONSTRAINT [FK_ptPhysicalExamDetails_ptPhysicalExam]

GO

-- Drop PK_ptPhysicalExam from ptPhysicalExam

Print 'Drop PK_ptPhysicalExam from ptPhysicalExam'

GO

IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].ptPhysicalExam')

AND [type]='U'))

AND (EXISTS (SELECT * FROM sys.objects

WHERE [object_id]=OBJECT_ID(N'[dbo].PK_ptPhysicalExam')

AND [parent_object_id]=OBJECT_ID(N'[dbo].ptPhysicalExam')))

ALTER TABLE [dbo].ptPhysicalExam DROP CONSTRAINT PK_ptPhysicalExam

GO

-- Altering ptPhysicalExam

Print 'Altering ptPhysicalExam'

ALTER TABLE ptPhysicalExam ALTER COLUMN MRN varchar(25) NOT NULL

GO

-- Updating ptPhysicalExam

Print 'Updating ptPhysicalExam'

UPDATE ptPhysicalExam SET MRN = RTRIM(MRN)

GO

-- Create Index PK_ptPhysicalExam on ptPhysicalExam

Print 'Create Index PK_ptPhysicalExam on ptPhysicalExam'

GO

IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[ptPhysicalExam]') AND [type]='U'))

AND NOT (EXISTS (SELECT * FROM sys.indexes WHERE [name]=N'PK_ptPhysicalExam'

AND [object_id]=OBJECT_ID(N'[dbo].[ptPhysicalExam]')))

CREATE INDEX [PK_ptPhysicalExam]

ON [dbo].[ptPhysicalExam] ([PEID] ASC)

GO

-- Create Foreign Key FK_ptPhysicalExamDetails_ptPhysicalExam on ptPhysicalExam

Print 'Create Foreign Key FK_ptPhysicalExamDetails_ptPhysicalExam on ptPhysicalExam'

GO

IF OBJECT_ID(N'[dbo].[ptPhysicalExamDetails]') IS NOT NULL

AND OBJECT_ID(N'[dbo].[ptPhysicalExam]') IS NOT NULL

AND NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id]=OBJECT_ID(N'[dbo].[FK_ptPhysicalExamDetails_ptPhysicalExam]')

AND [parent_object_id]=OBJECT_ID(N'[dbo].[ptPhysicalExam]'))

BEGIN

ALTER TABLE [dbo].[ptPhysicalExamDetails] WITH CHECK ADD CONSTRAINT [FK_ptPhysicalExamDetails_ptPhysicalExam] FOREIGN KEY([PEID])

REFERENCES [dbo].[ptPhysicalExam] ([PEID])

END

GO

--

-- Foreign Key FK_ptPhysicalExam_ptDemographics dropped earlier in script

--

-- Create Foreign Key FK_ptPhysicalExam_ptDemographics on ptPhysicalExam

Print 'Create Foreign Key FK_ptPhysicalExam_ptDemographics on ptPhysicalExam'

GO

IF OBJECT_ID(N'[dbo].[ptPhysicalExam]') IS NOT NULL

AND OBJECT_ID(N'[dbo].[ptDemographics]') IS NOT NULL

AND NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id]=OBJECT_ID(N'[dbo].[FK_ptPhysicalExam_ptDemographics]')

AND [parent_object_id]=OBJECT_ID(N'[dbo].[ptPhysicalExam]'))

BEGIN

ALTER TABLE [dbo].[ptPhysicalExam]

ADD CONSTRAINT [FK_ptPhysicalExam_ptDemographics]

FOREIGN KEY ([MRN]) REFERENCES [dbo].[ptDemographics] ([MRN])

END

GO

hodgy SSCertifiable Points: 5906 More actions · Answer 2

your constraint PK_ptPhysicalExam on [dbo].[ptPhysicalExam] is just a non-clustered index. It is not set as a unique index or a primary key. Therefore, the foreign key cannot reference it.

Tom

Life: it twists and turns like a twisty turny thing

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 3

Then how is it that the FK can be created in the designer?

hodgy SSCertifiable Points: 5906 More actions · Answer 4

In your script you are dropping the constraint PK_ptPhysicalExam. Perhaps the constraint that is being dropped is a true primary key and this is what the designer is using when it creates the foreign key.

Life: it twists and turns like a twisty turny thing

xgenx Newbie Points: 3 More actions · Answer 5

Is it Sys.foreign_keys in 2008? In 2000 it is just sysforeignkeys. I think the column names might be wrong too. If I am just being ignorant just ignore me.

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 6

Creating a unique index is NOT the same as creating a Primary key.

Change from doing this

CREATE INDEX [PK_ptPhysicalExam]

ON [dbo].[ptPhysicalExam] ([PEID] ASC)

to

ALTER TABLE dbo.ptPhysicalExam

ADD CONSTRAINT PK_ptPhysicalExam PRIMARY KEY CLUSTERED (PEID ASC)

Gary Johnson
Sr Database Engineer