Cannot see foreign key constraints on SQL Server 2008

Bit rusty on the sql side as I have not touched in a while

开发者_Go百科I have added a foreign key constraint using the Diagram and clearly says that there is a fk relationship.

However when scripting the table or viewing the keys in SQL Server explorer I cannot see the grey key.

Am I missing something? Not sure I am doing it correctly.

How do you create one programatically between 2 tables? Should it appear in the Explorer under keys?

Thanks a lot for any input

The foreign keys in the designer are in memory until they are saved. Look for a open design tab that hasn't been saved. Save it and refresh the database. The foreign keys should show up.

To create the FK relationship, use this syntax:

ALTER TABLE dbo.ChildTable
  ADD CONSTRAINT FK_ChildTable_ParentTable
  FOREIGN KEY (FK column in child table)
  REFERENCES dbo.ParentTable(column in parent table)

and possibly define what happens on UPDATE or DELETE of the primary key in the parent table:

  ON DELETE ...... (CASCADE, NO ACTION, SET NULL)
  ON UPDATE ...... (CASCADE, NO ACTION, SET NULL)

You should be able to query the data dictionary views to check if that FK constraint is really in there:

SELECT
    fk.NAME,
    OBJECT_NAME(parent_object_id) 'Child table',
    OBJECT_NAME(referenced_object_id) 'Parent table',
    fk.*
FROM sys.foreign_keys fk

This will list all existing foreign keys in your current database.

Remember to save what you changed and refresh the Object Explorer. For my case, I can see the grey key icon for my foreign key in both Columns and Keys folders. However, I can't see it is my Constraints folder. Apparently, it is considered normal?