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.
Another kind of constraint you can work with is what's called a unique constraint. Now in fact, we already have one of these on most of our tables. If you have a primary key, you have a unique constraint. That primary key cannot be duplicated. In this case, there can only be one ProductID with 1008 in it. But occasionally, you will want to define another column as being a unique constraint, that the value cannot occur more than once in the entirety of the column in that table.
Now right now none of these columns should be made unique. Certainly not UnitsInStock and certainly not Color. That repeats quite a lot. We might have ProductName made unique, but not the way that we work with it. For example, I have a Road-650 58 in Black and I have a Road-650 58 product in Red and a Road-650 58 product in Green. I can't make ProductName unique. What would be interesting, however, is if I could force uniqueness on a combination of the two.
What that allows me to do is make sure that they aren't accidentally two rows that represent the same product color combination, that the database would not allow me to enter that. Everything we do in our database design should all be about enforcing valid data. So how do we do this? Well, not surprisingly, it's going to be in the Design view of the product. I'm going to right-click that table and jump into Design. Now you won't find any on the unique options in your Column Properties. What you have to do is right-click in the blank designer area and while we had things like Check Constraints there, you don't see a unique constraint.
The area that we are interested in is this one, Indexes/Keys. If I select that, we should find we already have one. Here's my index for the primary key. It's saying this is the ProductID. It is unique, yes, and it is the primary key. We don't want to mess with that. What I want to do is add another one. It's not a primary key. It's what's called a unique key. I am going to click Add and we get this IX_Product, because this dialog box that I'm looking at is how we add new indexes to our table.
How we add new ways of looking things up quicker when we have large amounts of data. Now, we are going to be talking about indexes a little later on. So don't worry too much about all the options here like Create As Clustered and all that stuff. I actually want to change this. I click the Add button and I want to say no, this is not an index. This is a unique key. It changes the Is Unique to Yes. We do have to pick the columns that we are interested in. It's defaulted to ProductID, but ProductID is already unique. I am going to click the little ellipsis button. I am going to say what I actually want is for my column to be ProductName.
And because it's going to create its own little set of product names to make sure that there won't be a duplicate one, we can choose whether that Ascending or Descending. It doesn't matter here. However, I don't want it to be just ProductName. That wouldn't work. In fact, I couldn't enforce that rule on my existing table. It's going to be ProductName and I am going to click beneath it and say Color. That's fine. Color Ascending, ProductName Ascending. I click OK. It's now telling me that the combination is both of them, ProductName and Color. I can ignore the rest of the stuff and just click Close.
This still is considered an unsafe change to my table. So I'm going to save the change now. I am going to go back into editing that and I'm going to add a new product. Let's say in this case it's going to be Road-660 60 in Black and there's 3 UnitsInStock and that works just fine. Now let's say we add Road-650 58 in Green.
Now this one already exists. So when I tab off, I should get a problem. "I'm sorry, there was a Violation of UNIQUE KEY constraint, can't insert duplicate key. The statement has been terminated." Again, if you are a developer, a lot of the times developers think, "Well, surely, I could just take care of that in my application. I'll make a check beforehand." But the great thing about doing it here is we are saving those rules as business rules in the database. We are making sure that we'll never get that duplicate data entered in rather than just trusting an application or a user to make that check for us.
I won't be able to save that. So I can either add it as a new color, Blue, which would allow me to save that row, or I could have deleted that row.
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.