SQL Server Management Studio - adding foreign key confusing?

Question

I always find it confusing to add foreign keys to primary table in Management Studio.

Lets say I have a

Table1 
{
    ID int, -- Primary Key
    Table2ID int, -- Refers to Table2's ID 
}

Table2 
{
    ID int, -- Primary Key
    SomeData nvarchar(50)
}

I am adding a foreign key to Table1 by Right Click -> Relationships -> Table and column specification. I am setting "Primary" popups to Table2, ID and "Foreign Key Table" to Table1, Table2ID.

My questions:

1. Shouldn't Table2 be listed for "Foreign Key Table" and Table1 for Primary Key? Is my understanding wrong?

2. When I save I get an alert "The following tables will be saved to your database." and it shows both tables. I really don't get this. I only changed Table1. Why is the second table shown?

Keelboat

Answer 1

• Click the expand symbol next to the table.
• Right click on the "Keys" folder and select "New Foreign Key."
• (Alternatively, you can click the Relationships button on the toolstrip when you have the table open)
• Click the "..." button on the "Tables and Columns Specifications" row to open the editor.
• The drop down on the left will be the table you're adding from, and the static text field will list the table you're adding to.
• Use the dropdowns to specify your constraints, and be sure both sides have the same number of columns.

Coimbra

Question was how to do it in SSMS. Therefore this answer gets my vote. – Alyssa

I take it that if "New Foriegn Key" doesn't show up, then you don't have permission or something? – Khan

Answer 2

Why don't you just use the equivalent T-SQL statements?? Seems much easier and less confusing to me:

ALTER TABLE dbo.Table1
  ADD CONSTRAINT FK_Table1_Table2
    FOREIGN KEY(Table2ID) REFERENCES dbo.Table2(ID)

When I read this, I believe this is immediately clear what two tables are involved, and how they are connected (Table1.Table2ID --(references)--> Table2.ID)

If you want to stay in the SSMS designer world: you could also create a database diagram of your tables involved, and then just drag&drop your Table2ID column from Table1 over to Table2 and drop it onto the ID column there - this would graphically tell SSMS what you want to do, and you just need to review your choices and click OK on the dialog that pops up.

Buford

I know I can use T-SQL but I wanted to know the reasoning behind this design. Also Drag&Drop brings up the same window... – Keelboat

Answer 3

1.Shouldn't Table2 be listed for "Foreign Key Table" and Table1 for Primary Key? Is my understanding wrong?

I believe your understanding is wrong. Table2 is the table whose primary key you are referencing. Therefore it's listed under Primary Key. Table1 is the table that will have the foreign key (the reference to the primary key of another table); therefore it's listed under "Foreign Key Table".

As far as why both tables are saved, even though the foreign key is listed afterward as belonging to Table1: I believe it's because the foreign key constrains both tables. They both have to "know" about the constraint, so they both need to be saved.

Spodumene

Answer 4

Follow this way to create a foreign key in your table.

1. Right-click the column and click Relationship.

2. Click the Tables And Column Specific in the ...

3. Select the primary key table and key on the left side and select the current table key you want to map on the right side.

4. click the down arrow in Tables And Column Specific to confirm foreign key mapping.

5. Click the close button and now a foreign key is created.

Roxanneroxburgh

Best answer since screenshots were included. – Acquittal

Answer 5

ALTER TABLE dbo.Table1
  ADD CONSTRAINT FK_Table1_Table2
    FOREIGN KEY(Table2ID) REFERENCES dbo.Table2(ID)

Wrung

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! – Buford