Trying to create key

  • 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?

     

  • 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

  • You need a PK to create a FK.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

  • 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