Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
I am going to go ahead and describe a one-to- many relationship between two of my own tables. So I have two very-very simple tables here. I have a product table, which has a few products in it right now. It's got a ProductID as the unique key, the primary key for this. That's automatically incrementing, ranging from roughly 1000 through 1013 right now, and I also have an OrderItem table. This itself has its own primary key, OrderItemID, that I don't really care about, but it's got a place for a ProductID and this is going to become the foreign key.
I'd like to be able to create multiple order items for each product so that ProductID 1001 can be used several times if I deem it necessary. That means our many-to-one relationship is one product, many order items. So how do I make it? Well I am going to close down these tables. There are a couple of different ways I can do this. One way would be to go to the Database Diagrams area, add a new satabase diagram, grab the two tables that I'm interested in, OrderItem and Product, and I could actually drag and drop between them.
Grabbing hold of the little column to the left, grabbing ProductID and dropping it on here. However, I am not going to step through that way, because we haven't really been doing too much with relationships and I'd rather show you a slightly more manual way to do it, just editing the tables directly. I'm going to go into the OrderItem table into Design view. So I am in the many table here. I am going to right-click on the blank area and come down where I have an option for Relationships and it's going to tell me there are no foreign key relationships right now.
So I'll click Add to make one. Now it doesn't actually know what this relationship is. So it's actually telling me here that the Tables And Columns Specification property needs to be filled in before the new relationship will be accepted. That's this expandable drop down here, where right now it's saying "Well I don't know who the foreign key and the primary key tables are so please tell me" and there is a little ellipses button that you need to click. We open that up. What it's going to say is here's the drop-down for the primary key table.
Who is really in charge, where is the one, and the one is going to be in the Product table. I select that and then click in the column below and I'll select ProductID. Foreign key table here, it says it's OrderItem. That's correct but it's not OrderItemID that I want to link up. Very commonly we are going to be linking up two columns with the same name. They don't have to have the same name, but they do have to have the same kind of data. You couldn't for example link an integer on one table to a date on another table.
You'll notice that by tweaking this, it's actually saying up here as far as it's concerned it's foreign key _OrderItem_Product, just giving us a hint that there is two tables involved, and it's the OrderItem table and the Product table. So I am going to click OK and I'm going to click Close. Now this is still considered an unsaved change. So I am going to try and save it. I'll hit the Save OrderItem and it will say, "The following tables will be saved. Do you want to continue?" Kind of prompting me that there are really two changes here. Yes, I do. Unfortunately, it's giving me an error.
Error was encountered during the save process. The ALTER TABLE statement conflicted with the foreign key constraint. So what's going on here? Well if I open back up this Product table and the OrderItem table, what's going to happen if I spent my time as I could scan through this Product ID and figure out that perhaps a couple of them just aren't right. I have a ProductID down here for 500. Well there is no product with ProductID of 500, and if we are trying to enforce that foreign key, that relationship, the database is saying no, we can't do it.
We can't enforce it for product that doesn't exist. So let me say that perhaps this was intended to be 1005 and I've got another one here for 1012, because we don't have a 1012 product so that wouldn't work either. So you might have to do a little bit of tidying up of your data before you can enforce any new relationships. I am going to go back to the table design where it should still have our change saved. We've got the little asterisk marking that there is a dirty change here. So I am going to try and save it again. Save.
Do I want to do this? Yes I do and well that seemed to work. So let's go ahead and see what impact this would have. If I go into my OrderItem now and actually start to change one of these, perhaps I'll change the one that was 500 and I'll change it back to 500. Well, it's not going to let me do that. This is the benefit of having that relationship, that foreign key, described. We now have the idea of integrity, which often referred to as referential integrity here.
When you describe that relationship it means something. If you describe the ProductID in this table as the foreign key to another table, then you better have a legitimate value here. Now the impact of this does mean that in a lot of cases your applications have to be very careful in what order they do things. If for example, you wanted to add a new product and add a new order item, you better do them in that order, because the product needs to exist first. Once we add a new product, this one being added automatically with 1014, we could then change the value immediately to 1014 in another row that references that.
We just can't at this point change it to 1050. And bearing in mind, if you want to create a many-to-many relationship, that's not created directly. You do two one to many, you do two of these. Follow that process twice with the in-between table. What if I wanted to take a look at this relationship, perhaps just confirm that it worked. And let me hit the Escape key to get out of my changes here, because it won't allow me to save that. I am going to make a new database diagram. I am going to add those two tables and what should happen is that closing that, it should show us the relationship. The key to the infinity symbol, the Product to OrderItem as the one to the many.
We can have the option here to even use the diagram to delete the relationship from the database. Not something that I really want to do but something that I could do. I do get the option to tweak this. I am going to right-click one of the tables. I'll right-click OrderItem and jump into Relationships because the one thing that I did want to show you, bringing up this relationship is this little dropdown here. The INSERT and UPDATE Specification. You've got something called a Delete Rule and an Update Rule and what that means is if I delete something from the Product table and there are references to it in OrderItem table, will it let me do that? Now by default there should be No Action.
No, that's a problem. But you can set it to do what's called cascading deletes. That means if I went into the Product table and I deleted the product with the ID 1005 that it would cascade down into the OrderItems and delete every order item with that foreign key of 1005. Cascading is not something that is typically recommended. You can have it turned on but I'm not going to. Just to go back and prove that I can see that in OrderItem, not only can't I change ProductID to something that doesn't exist -- so I'll hit Escape and change back -- if I pick one of the ProductIDs that I am using, 1001 for example and go over there and try and delete the product row, it's going to say, "Are you sure? You are about to delete one rows." Yes I am going to try that. "Oh! I'm sorry you can't do it." Again, keeping the referential integrity of the database together.
So we won't be allowed to do that. If I wanted to delete that product, I would first have to go and find every order item that referred to it, either change this ID or delete that row, and then and only then would it allow me to go back and delete the row of the product.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.