1

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?

1
  • If I were you I would ignore the designer, which is riddled with problems. Just script out your tables using CREATE etc Apr 14, 2023 at 0:33

2 Answers 2

4

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:

enter image description here

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

enter image description here

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.

1
  • 1
    Thanks. I generated a full script from the entire database using tasks->generate scripts and there's no FK named that MSSQL is complaining about. It also doesn't show up in the object browser. I am beginning to suspect there's a major issue with the database integrity, but I cannot figure out what. I also can't drop the 'existing' FK as I get an error that it doesn't exist. I may have to recreate both tables from scratch and move data in them.
    – Daniel
    Apr 13, 2023 at 21:39
-3

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

2
  • 1
    "This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead...This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature." - aka deprecated.
    – J.D.
    Apr 13, 2023 at 3:21
  • 1
    regarding the statement, "foreign key is not an object": dbfiddle.uk/sH9D8B6F
    – Hannah Vernon
    Apr 13, 2023 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.