February 4, 2021 at 3:24 pm
Hi
I had to drop a constraint to run an update. The drop was fine but when I run the create statement:
ALTER TABLE [dbo].[PEC_Claim_Supp] WITH CHECK ADD CONSTRAINT [FK_claim_PEC_Claim_Supp] FOREIGN KEY([claim_id])
REFERENCES [dbo].[claim] ([claim_id])
GO
ALTER TABLE [dbo].[PEC_Claim_Supp] CHECK CONSTRAINT [FK_claim_PEC_Claim_Supp]
GO
I get the error:
Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'dbo.claim' that match the referencing column list in the foreign key 'FK_claim_PEC_Claim_Supp'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint or index. See previous errors.
Msg 4917, Level 16, State 0, Line 5
Constraint 'FK_claim_PEC_Claim_Supp' does not exist.
Msg 4916, Level 16, State 0, Line 5
Could not enable or disable the constraint. See previous errors.
What am I doing wrong?
February 4, 2021 at 6:28 pm
this worked
ALTER TABLE dbo.claim
ADD CONSTRAINT PK_CLAIM_ID PRIMARY KEY CLUSTERED (claim_id);
GO
ALTER TABLE [dbo].[PEC_Claim_Supp]
ADD CONSTRAINT [FK_claim_PEC_Claim_Supp] FOREIGN KEY([claim_id])
REFERENCES [dbo].[claim] ([claim_id])
GO
February 4, 2021 at 7:24 pm
You need a PK to create a FK.
February 4, 2021 at 8:06 pm
I think SQL Server loosened up on that. I believe you just need a unique index to create a FK against.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 4, 2021 at 9:33 pm
Had to go check out that unique comment. Wow, you can do it. You learn something new every day.
Here is the link.
--JD
February 4, 2021 at 11:53 pm
Wow, I learned something, too. Glad to see a unique index is enough, as it should be.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply