SQL Server 2008 won't let me add foreign keys

Question

I've been using SQL Server for years, so I'm not a noob. I've recently been having problems where I can't add a foreign key to certain tables in my database using SQL Management Studio and SQL Server 2008 Express. The table I'm trying to reference is there and it has a primary key (with the primary key constraint created), but it doesn't show up in the list of tables that I can choose from when I'm trying to add the FK.

If I try and add the FK through plain old T-SQL, the query succeeds, but if I view the new FK using the UI, the primary key table dropdown is empty. The FK is there and it actually does work if I try to insert some data that would violate the constraint.

Anyone know why this would be happening?

Answer 1

This sounds like a tool issue (SSMS), not an engine issue

Thoughts:

• close/reopen SSMS (caching?)
• patched to same version as server install?
• different schema etc?

Edit, after comment and it's SSMS caching:

You can also right-click on the table node and refresh so SSMS updates the cache. This problem goes back to SQL Enterprise Manager and SQL 2000. No known fix after 10 years...