CODEWITHSUNDEEP
sql-server
user9969
user9969

Reputation: 16040

Cannot see foreign key constraints on SQL Server 2008

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

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

Upvotes: 2

Views: 5669

Answers (3)

timbuntu2
timbuntu2

Reputation: 111

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?

Upvotes: 0

DorkHead
DorkHead

Reputation: 71

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.

Upvotes: 7

marc_s
marc_s

Reputation: 754230

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.

Upvotes: 3

Related Questions