SQL Server How to resolve a Microsoft.Data.SqlClient.SqlException with foreign key constraint?

p4rjhz4m  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(125)

I'm working on a C# project with Microsoft SQL Server as the database. I've written an ALTER TABLE statement to make some changes to a table in my database. However, when I try to execute this statement, I encounter the following error:
Microsoft.Data.SqlClient.SqlException: 'The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SampleTable_ReferenceTable_ReferenceColumn". The conflict occurred in database "MyDatabase", table "dbo.ReferenceTable"

It appears that the error is caused by an existing foreign key constraint in my table. I've double-checked the ALTER TABLE statement, and it seems correct. How can I resolve this issue and successfully complete the table modification operation?

Here's the ALTER TABLE statement I'm trying to execute:

ALTER TABLE dbo.SampleTable
    ADD CONSTRAINT FK_SampleTable_ReferenceTable_ReferenceColumn
        FOREIGN KEY (ReferenceColumn) REFERENCES dbo.ReferenceTable (Id);

Any suggestions or guidance on how to address this situation would be greatly appreciated. Thank you in advance!

t40tm48m

t40tm48m1#

You should issue the command ALTER TABLE twice: the first time to remove the CONSTRAINT (if exists), the second to ADD it again:

ALTER TABLE dbo.SampleTable
    DROP CONSTRAINT IF EXISTS FK_SampleTable_ReferenceTable_ReferenceColumn;

ALTER TABLE dbo.SampleTable
    ADD CONSTRAINT FK_SampleTable_ReferenceTable_ReferenceColumn
        FOREIGN KEY (ReferenceColumn) REFERENCES dbo.ReferenceTable (Id);

相关问题