Trying to create key

Question

Post reply

Trying to create key

krypto69

SSChampion

Points: 13606
More actions
February 4, 2021 at 3:24 pm

#3841009
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?

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

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

krypto69 SSChampion Points: 13606 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 738327 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 738327

February 4, 2021 at 7:24 pm

#3841124

You need a PK to create a FK.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 3

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".

James Dorame Ten Centuries Points: 1034 More actions · Answer 4

Had to go check out that unique comment. Wow, you can do it. You learn something new every day.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-ver15

Here is the link.

--JD

Steve Jones - SSC Editor SSC Guru Points: 738327 More actions · Answer 5

Wow, I learned something, too. Glad to see a unique index is enough, as it should be.

Trying to create key

Cookies on SQLServerCentral