Wednesday, August 8, 2012

How to create a foreign key constraint with the Visual Studio 2012 database tools

The new SQL tools in Visual Studio 2012 are amazing, but still lack a database diagrams features that SQL Server Management Studio has.  I use database diagrams all the time to easy create foreign keys.  We can obviously do in T-SQL, but who honestly remembers the exact syntax for adding a constraint…not me!

Although it is a bit more work there still is a way to create a foreign key in the Visual Studio designer tools.  It took me a few mins to figure it out, so hopefully this post helps you.

First you need two tables, something like this:

image

I tried many things including dragging ConfSpeaker.ConfiId to Conf.Id, right click and got nothing.  I poked around the interwebs a little and found nothing.  So I poked more around Visual Studio and finally realized that keys can be added by right clicking on the “Foreign Keys” text in the designer.

image

Click on “Add New Foreign Key” modify the name and it will create the correct SQL for you:

Change this:

image

To this:

image

 

 

Here’s the SQL that is generated by default:

image

 

Unfortunately it only puts place holders in the SQL that you have to manually modify.

 

image

 

 

Jon