Foreign key constraint not showing in sysobjects but cannot be created due to conflict with itself

Question

I have an Microsoft SQL Server 2019 Enterprise Edition database where I had a relationship between two tables: Properties and Photos with 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?

Answer 1

SSMS won't script foreign key constraints, default constraints, indexes, permissions, etc, by default unless you specify that in the Options menu. This is how I configure my SSMS to generate better scripts:

Having said that, you should see foreign keys listed under the Keys folder, in the Object Explorer tree:

You may need to click on the Keys folder then click "refresh" before the new objects are shown.

You can also use the system views built around sys.objects, including sys.foreign_keys to see constraints. See the example I wrote here.

This query will identify the table and constraint names for all the foreign key constraints in your database:

SELECT 
      [parent_object] = QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
    , [constraint_name] = QUOTENAME(ok.[name])
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
    INNER JOIN sys.objects ok ON o.[object_id] = ok.[parent_object_id]
    INNER JOIN sys.schemas sk ON ok.[schema_id] = sk.[schema_id]
WHERE ok.[type_desc] = N'FOREIGN_KEY_CONSTRAINT'
ORDER BY 
      s.[name]
    , o.[name]
    , ok.[name];

Sample output looks like:

parent_object	constraint_name
[dbo].[ContactUs]	[FK_ContactUs_UserID]
[dbo].[ContactUs]	[FK_ContactUser_UserEmailAddressID]
[dbo].[UserEmailAddresses]	[FK_UserEmailAddress_UserID]

My query above uses sys.objects and sys.schemas exclusively to show that constraints are listed in sys.objects, and are denoted as foreign keys in the type_desc column. You can also use the sys.foreign_keys system view, as shown in my fiddle, which adds several columns allowing you to identify which table and columns are being referenced by the foreign key. The Learn page for the sys.foreign_keys system view is here

Be aware, that while sysobjects does return results, it has in fact been deprecated by Microsoft, and replaced with a System Compability View. Get in the habit of using the Catalog Views instead.

Answer 2

Foreign key is not an object, but a "constraint". You will find it in the sysconstraints table.