- [Narrator] Because we're using primary key columns in our data tables to uniquely identify individual records, we can use this information in other tables to make relationships between them. By formalizing the connections between data tables, we can take advantage of the interconnected nature of the information that's being stored. Let's suppose that we want a new table to collect information about orders and we want to make sure that we know the customer that's placing the order. We can use the CustomerID number in the orders table so that we have an easy way to refer back to the individual that made the purchase. To see this in action, we'll go over to the MyDatabase database and we'll come down to the Tables folder within it.
I'll right click on it, point to new, and choose table. We're going to create a new table here for our orders. The first column will be the OrderID. I'll press tab to move over to the data type, and this is going to store an integer data type. So I'll type int. And we don't want to allow null values, so I'll uncheck the allow nulls box. This is going to be the primary key for the table, so I'll press the key icon in our toolbar. And then I'll come down to the column properties and we'll find the section for the identity specification. We'll go ahead and open that up, and I'll change Is Identity to yes.
I'll go ahead and double click on it to automatically change it to yes. And we'll leave the identity increment to one. And the identity see is one as well. The next column will be used to refer back to the Customers table. I'll come down to the next column, and we'll type in the name CustomerID. This is also going to be an integer data type, so I'll type in int. And I don't want to allow nulls here. Finally, I want to know what product they ordered. So I'll go ahead and go to the third column and I'll type in ProductName. This will store a varchar(50) data type.
And I also want to require this information in the table, so again, I'll uncheck the allow nulls box. Let's go ahead and press the save icon. I'll change this name to Orders. And press ok. Now in order to formalize the link between our Orders table and the Customers table, we'll create a relationship between the data that's being stored in the Customers table, in the CustomerID field and the data that's being stored in the Orders table in the CustomerID field. To do this, we need to press the relationships button. And unfortunately on my screen size, it's actually the screen.
I want to press the downward pointing arrow on the right hand side of my toolbar, and we'll find the relationships button here. It has the green plus icon on it. That'll bring up a new window called Foreign Key Relationships. I'll come down to the bottom, and we'll add in a new relationship. Then in the general group, we'll find this tables and columns specification section, and I'll go ahead and twirl that down. I'll come over to the far right, and I'll press the ellipses button to create the relationship. The primary key table is going to be our Customers table. So I'll go ahead and select it from the dropdown menu. And the column is going to be the CustomerID.
That's going to link to the Orders table and the CustomerID column in that table. I'll go ahead and select that from the dropdown list as well. Once those selections have been made, go ahead and press the OK button and that'll return us back into the Foreign Key Relationships window. And we can see that those details have been filled in. So the foreign key base table is going to be Orders. The foreign key column is going to be CustomerID. The primary, or the unique key based table is Customers. And the primary unique key columns is CustomerID. I'll press the close button, and then I'll finish saving my relationship, and then we can go ahead and close out of the Orders table.
That's going to prompt me to save my changes to the Orders table. Go ahead and say Yes. And it's also going to prompt me to save the Customers table. Since the relationship that we just made is joining both tables together, it has to modify both tables. So say Yes to this as well. Now let's go into the Tables folder. Right click and choose Refresh. And we can see that we have two new tables here. We have the original Customers table as well as the new Orders table. So let's go ahead and see how this works when I try and enter in an order. For reference, first I'm going to right click on the Customers table and choose select top 1000 rows.
That'll show me the single customer that I entered in previously for Suzanne Jones. And we can see that her CustomerID is number 1000. So let's go ahead and place an order for Suzanne. I'll right click on the Orders table and choose edit top 200 rows. Remember that the OrderID is going to get filled in automatically by SQLServer, so I'll come over here to the CustomerID and we'll type in Suzanne's CustomerID of 1000. Then the product that she ordered is going to be the Barcelona chair. I'll press enter, and that record gets saved into the database.
So now let's see what happens if we try and enter in an order for a customer that doesn't yet exist in the Customers table. I'll come down to the next record, and we'll try and enter in a CustomerID number of 1001. I'll press tab to move over to the ProductName, and this time the order is for a Wassily chair. But this time when I press the enter key, I get this error message. Now this text is a little bit dense, but you might be able to pick out a reference to a conflict in the foreign key constraint. Because we don't yet have a customer with the ID number of 1001, SQLServer won't allow this data entry.
The relationship that we establish between the two tables protects the database from getting populated with erroneous or incomplete data. If this is a valid order for a new customer, I'll first have to establish the new customer's record before I can save the order details. Let's go ahead and say OK to this. And that'll back us out to our table. I'll press the escape key to move out of that a couple of times. And then we can go ahead and close these two windows. So that's how table relationships help protect the database and insure that you can trust the data that's being entered is complete.
▶️ DTube
▶️ IPFS