On establishing Primary Key / Foreign Key in SQL Server 2008

When you copy over tables to SQL Server you not necessarily copy all the 'Keys' although you have all the data. Now you want to recreate the relationships. How do you do it? Of course one could use scripts to do it. Here is how you can do it in the SQL Server 2008 Management Studio.

There are two tables Suppliers and Products.
To start with both tables have no Primary Keys
Products has SupplierID as one of its column which happens to be also in the Suppliers table.

Step 1:

You should get both the tables into the Database Diagram Pane.

Expand the database node in the SQL Server 200 Management Studio. Right click the Database Diagram folder and choose to create a New Database Diagram. The Database Diagram Pane opens up and a Add Table Window listing all the tables in the database opens. You can pick and choose tables to add and they appear in the diagram pane.
------------------------------------------------------------------------------------------------------------------------------------

Step 2:

Set up Primary Keys for both tables
Right click Products Table in the Database Diagram of SQL Server 2008
From the drop-down menu choose Set Primary Key

















Similarly set the Primary Key for the Suppliers table.
-------------------------------------------------------------------------------------------------------------------------------------

Step 3:

Establish the Primary Key / Foreign Key relationship

Click on SupplierID on its left and hold-down the mouse and click on Supplier ID in Products table shown schematically in









-----------------------------------------------------------------------
In the Tables and Columns window that pops-up verify that SupplierID in Suppliers table has a related item SupplierID in the Products table.

----------------------------------------------------------------------------
Click OK. The Foreign Key Relationship window pops-up as shown















--------------------------------------------------------------------------------------------------
Expand INSERT And UPDATE specific item.
Click on an empty space in Delete Rule and Update Rule text boxes in the expanded items and set them for Cascade.

Click OK and try to save your work from the File menu. You will get a error message if you already have data in your tables.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Step 4:

Enable saving of changes

In the Main menu click Options. In the window that pops-up deselect
"Prevent saving changes that require table recreation"
Click OK
Now save the Database diagram and it will be saved with a name that you provide

Read about Referential Integrity in: http://en.wikipedia.org/wiki/Referential_integrity