Create FOREIGN KEY in SQL Server via GUI

Saturday, June 25, 2016


Topic in this blog post tutorial is How to create FOREIGN KEY in SQL Server via GUI?... this process in SQL code is really simple with just few lines of SQL Code.. but when you want to create foreign key via GUI some additional steps you need to take to accomplish that.

I hope that this tutorial will be helpful for you. So let's begin, first of all I will demonstraint this process with two tables: Player and Football Team... for example we are making database for our local football team league.

As we know one player can play for only one team, and in other side football team can have more than one player obviously.

So I need to accomplish this via GUI in SQL Server Management Studio:

If we want to make this via SQL code this is how we do it:




So let make this the same but via GUI. First of all I will create new database and name it TheFootballLeague.

Right click on the Database "folder" inside of Object Explorer and select New Database...




After this click OK to confirm this action.

Now when we have our database we will make our two tables Team and Player and hopefully connect them via relationship.

To create new table via GUI we can click open our database and on "folder" table right click on it and select Table...

We will now insert our columns and primary key for this table Team like this.


To make TeamID column as PRIMARY KEY we need to select it and on toolbar select gold/yellow key which represents PRIMARY KEY... after that this column is our primary key for this table, now we can save it and give it a name in my case it will have name "Team".

Now we are going to make another table for Players... we do it the way as previous table, so our new table will look like this:


It is very important to save our table at this moment.

This is how our database tables look like at this moment:
As we can see we don't have any relationship between our two tables, but we need one-to-many relationship between these two tables.


Now when we want to make FOREIGN KEY from our column TeamID to table Team we have two method more-less they are the same but we will go with both of them just to be sure what is esier to us.

First method:

At our design table view we can select our column and than right click and choose Relationship like this:



After we click on relationships we will get Foreign Key Relationship window:

In order to create new foreign key first we need to click on Add button and we will see that new foreign key is created in this case temporary with name "FK_Player_Player"... to change that and to make connection between our foreign and primary key we to click on "three dot" button under the Tables And Columns Specification.



After we click on three dot button in front of us we will see this window:


This is main part of our process... first of all we can name our foreign key at Relationship name part.
After that on left side we need to specify for which table and column/primary key our new foreign key will be referenced. In our case we need to create foreign key in table Player but it will have reference to table Team and column/primary key TeamID.

And in right we have option to specify which column in our table will be foreign key, in this case it will be column TeamID inside of Player table.

We confirm this with OK button and after that we need to save our table... and we are good to go.

Second method:

Second method is very simple all steps are the same but first step is different from previous method.

So to make new foreign key in our Player table we need to right click on "folder" Keys and choose New Foreign Key..., like this:


After that we have same window as in previous method so you just need to follow steps and you will have your new foreign key.



Conclusion:

After all the steps we make... now we have our foreign key in table Player and one-to-many relationship between Team and Player tables.

This is result from this blog post tutorial:




Hope that this blog post  tutorial was helpful for you... if it is please share it to be more helpful for community.

Best regards!

You Might Also Like

5 comments

  1. hello Almir! thank you for the tutorial and for your blog! I'd like to aks if you tried IDE for SQL Server from dbForge? I was said that almost everything works faster.. can you say something? Thanks =)

    ReplyDelete
    Replies
    1. Hi Nath144, Thank you for the comment I am glad that it was helpful for you.

      Unfortunately I did not, so I can not help in that case.

      Best regards!

      Delete
  2. best explanation.thank you soo mush Almir vuk. you solved my big problem

    ReplyDelete
    Replies
    1. I am glad that this was helpful for you... best regards!

      Delete