One more thing about recreating foreign keys
-
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
-
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
-
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
-
Then how is it that the FK can be created in the designer?
-
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
-
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.
-
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply