I have an Microsoft SQL Server 2019 Enterprise Edition database where I had a relationship between two tables:
Properties being the primary key and
Photos being the foreign key table.
However, the foreign key constraint named
FK_Photos_Properties has somehow disappeared: it doesn't show up in the SSMS table designer and
SELECT * FROM sysobjects WHERE name = 'FK_Photos_Properties';
returns no results.
When I try to recreate the relationship using the SSMS designer, I get an error message:
Unable to create relationship 'FK_Photos_Properties'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Photos_Properties". The conflict occurred in database "MyDB", table "dbo.Properties", column 'ID'.
So it seems the foreign key still exists but where? What is going on here?
DBCC CHECKDB returns no errors. I also tried scripting the entire database structure to look for
FK_Photos_Properties, but it's not in the generated script at all. The foreign key doesn't show up anywhere that I can see: neither in the table designer's Relationships... window, nor in the script generated from the database schema.
What can I do to get the foreign key constraint back?