Creating Primary and Foreign Keys in SQL Server 2012

good day everyone this is dr. Soper here and today I'm just going to walk through a quick demonstration of how to create both primary and foreign keys within sequel server for the purposes of this demonstration I've created a new database which as we can see here I have cleverly named demonstration and within this database I have created four tables the names of the tables are customer order order line and product now before I create any primary or foreign keys for these tables I would like to just briefly describe the nature of the relationships that I would like to create essentially what I would like is to create a situation where each customer can place one or more orders and where each order can contain one or more products to implement these relationships will require a four table solution and that is why these four tables have been created to begin let's create a primary key for our customer table perhaps the easiest way to create a primary key is by using the table designer so if I right-click on the name of the table I will have an option which says design and when I select this option the table designer will appear note that this table contains three columns customer ID first name and last name and for the purposes of this demonstration I would like the customer ID attribute to serve as the primary key for this first method what I will do to set the primary key is simply right-click on the column in which I am interested so I right-click on customer ID and you will see an option appears which says set primary key I select this option and you will notice that sequel server has now marked this column as the primary key by placing a little icon next to the name of the column which looks like a key I will then save the table and creating the primary key is complete it is just that easy if I expand my customer table we will see that there is currently nothing listed in the keys folder here one of the unfortunate characteristics of sequel server is that it does not automatically update items in this list when changes are made to the database so if I right click on the name of the table and select refresh from the menu you will notice that after refreshing the list there's now an item listed under keys and it shows that I have a key created which is named PK customer the PK here stands for primary key and this shows me that I do indeed have a primary key created for my customer table next let's see if we can create a primary key for the order table using a slightly different method so again I will go into the table designer by right-clicking on the name of the table and selecting the design option now in this case instead of right-clicking on the name of the column to set the primary key what I will do is I will click on this little key icon which appears up here in the toolbar and when I hover my mouse over this you will see that it reads set primary key so by clicking on this button again we see the now-familiar icon of the key appears and we know that we have thus established a primary key for the order table I save the table and close it and the primary key has been established next I will create the primary key for the product table using our familiar approach so I will select product ID right click set primary key and save the result I now have primary keys selected for customer order and product I've saved order line for last because it's going to have an interesting difference if I look at the design of this table we can see that it is currently composed of three columns the order ID the product ID and the quantity now we can think of an order line as a line on a receipt so when you go to say a grocery store you might buy several items say that you purchase some apples and some cereal and a box of cookies you take those to the checkout lane so you have three different products but they all are going to be a part of the same order and this is the purpose of our order line table it allows us to associate a product that is being purchased with a specific order so in our previous example we may have a product that is Apple's say product number seven associated with order number one our next product was cereal that might be product number eight also associated with order number one and our third item was a box of cookies that might be product ID number nine also associated with order ID number one what I need to create here then is something called a composite primary key in which more than one column in the table will serve as the primary key for this purpose what I want to do is select both columns and I can do this by holding down the control key on my keyboard and selecting both columns you can see that both are selected and then I can use our now familiar technique of right-clicking and choosing set primary key and if we've done this correctly we should see a key icon appear next to both column names indicating that both of these column names together will serve as a composite primary key ok I close the table and save the changes and we've now established primary keys for all of the tables in our database now let's create a relationship to begin we will create a relationship between the customer table and the order table and we said that we want each customer to be able to place many orders to establish this relationship I will need to create a foreign key and because each customer will be able to create many orders as a quick rule of thumb just remember that foreign keys belong at the many end of the relationship so a customer creates many orders I will want to create the foreign key in the order table if I look at the design view for this order table you will see that I've already created a customer ID attribute in this table note that the data type of this customer ID is an integer which matches the data type of the customer ID primary key in my customer table so here there's an integer here as the primary key in the customer table and over here in the order table I have a similarly named customer ID attribute and it has a matching data type okay so to establish the primary key then within my order table I will right click on the keys list item and we will see a new option appears which reads new foreign key I will select this option and the foreign key relationships dialog box will appear now to create the foreign key what I want to do is click on the tables and columns specification option here in my list and you will see a little button with three periods or an ellipsis appears over to the right I click on this button and the tables and columns dialog box appears and this is where I can establish the foreign key first I want to select primarykey table now in our example remember that a customer can place many orders and we're using customer ID in the customer table to link to the customer ID in the order table customer ID in the customer table is serving as the primary key so I will select a customer table here as the primary key table and it's kind of hard to see but this drop-down box will appear below the name of my table and I can select customer ID here to indicate that this is the primary key in the customer table that I want to use in this primary key foreign key relationship so the foreign key table is already selected as order and I will simply select the matching customer ID attribute here so I am linking customer ID in the customer table to customer ID in the order table I click on ok and sequel server has provided for me a default name for this constraint it is called FK which stands for foreign key order and customer showing me that this is the foreign key which links together the order and customer tables it's important to note that each constraint in this case a foreign key constraint within the entire database must have a unique name I click on close and when I save the table my new foreign key link will be established note here that sequel server says it's making changes to two tables that's because I'm linking these two tables together so I click on yes and my foreign key link has been established again remember that sequel server does not automatically update items in the list over on the left and the object Explorer when a change is made but if we ever want to see the latest version we can always right click and select refresh and I can now see that I have two keys created here in the keys list there is the primary key for the order table and our newly created foreign key which links the order table and the customer table together so that's easy it's easy to create primary and foreign keys let me show you one last way of creating foreign keys that I think personally is the easiest and you may agree and that is to create the keys using a database diagram so up here above the tables option you see that I have an option in my object Explorer which says database diagrams and if I right click and select new database diagram the add table dialog box will appear I want to select all four of my tables and I will click on add and then close and sequel server will add my four tables out to this drawing canvas out here now you can see that it visually shows the relationship that has been created between our customer and order tables and note the little key icon here shows that this is the primary key side of the relationship and there's a little symbol here which looks like an infinity symbol in this case it's kind of rotated 90 degrees anti-clockwise that infinity symbol is used to represent this notion of many so a customer can place many orders now let's link our remaining tables together first I want to link the product table to the order line table and essentially what I want to do is create a primary key foreign key relationship that connects the primary key product ID in the product table to the attribute in the order line table which is also named product ID now to do this what I'm going to do is select the attribute hold down my left mouse button and just drag this on top of the product ID attribute in the order line table and when I release the mouse our familiar tables and columns dialog box will appear and you will see that it has populated the correct values for us already so the primary key table is product using product ID we want to link that to product ID in the order line table I can click on OK and click on OK again and you will notice that sequel server has updated our database diagram showing that there is now a relationship between these two tables so that's easy that's the easy way to do it I will create our final relationship by linking order ID in the order table to order ID in the order line table ok ok that relationship is established and the primary key and foreign key relationships for our database are now complete one thing that I would like to note before I end this brief tutorial is that remember in the order line table that we established order ID and Product ID as a composite primary key and we can see the this by noticing that there are two key icons next to the names of those columns so this is a composite primary key but and this is a very important point together these attributes are serving as the primary key for the table but individually each attribute serves as a foreign key link back to its parent table so product ID to Product ID here in the product table is a foreign key relationship just as order ID in the order line table connecting to order ID in the order table is a foreign key relationship so together these two attributes order ID and Product ID serve as a composite primary key but individually each attribute serves as a foreign key link back to its respective parent table this means that these attributes are playing two roles simultaneously they are at the same time part of the primary key and individually a foreign key link back to their parent table well then I'll save my changes and thus ends this brief tutorial on how to create primary and foreign keys in sequel server I hope you learned something interesting until next time have a great day

Channel: Dr. Daniel Soper

Views: 631,902

Keywords: SQL Server, SQL Server 2012, SQL Server Management Studio, SSMS, Primary Keys, Foreign Keys, Microsoft SQL Server (Database Management System)

Id: TpKcAmaaBts

Length: 18min 22sec (1102 seconds)

Published: Tue Jul 02 2013