Subtitles of the Movie

When we talk in terms of referential integrity, joins, unions, we're really putting a certain amount of validation on our data. Unions will combine two tables, joins will pull together records from tables. I'd like to talk a bit more about validation, particularly about rules. There are different forms of validation that we should be aware of. OK, first we will have what we are going to discuss in this video are actual rules. I can actually create a rule and then apply it to a table. But keep in mind that defaults are a type of rules because the data shows up automatically. Your index will be a type of rule because it'll show the type of order that we want the data to be actually sorted in and referential integrity as we just discussed. The last aspect are triggers. Triggers will take any of these rules that we can have and have them automated just because the user did a type of form of data entry. Usually you'll see a trigger on an update, a delete or an insert. Now again, in this movie I'd like to talk primarily about working with the rules and we'll actually show examples in SQL server of course. Now, keep in mind that when we talk in validation, you will have referential integrity but you will also hear about requirement integrity. Requirement integrity ensures that a column must have data in every row. Here's where we'll use examples such as not null to ensure that the user has data in it. Now there are, of course, exceptions but for the most part we want to try to keep as much data in the columns as possible. And you will also hear the term validity. Now, when we talk in term of the validity, here we want to make sure that the typing is valid. This is where the triggers or the rules, which we are going to discuss in a few seconds, come into play. Now, in talking in terms of our validity, the example might be an e-mail address. We want to make sure that the address that's typed in is compliant. In other words, we want to make sure that the at sign was not missed or for example the .NET was not missed at the end. This is where validity will come into play. Then we have uniqueness. When we're pulling information from a couple tables, one of the things we have to be careful about is that we do not have exactly the same values in each column. Remember, in our preceding movie we discussed the aspect of union versus union all. This time let's talk a little bit about rules because they are exceptionally powerful and there's just a few things we need to be aware of when we're creating those rules. I'm back into SQL. In front of you, you will notice that I have a rule right on the screen. On the top it's creating a rule and it's called the Accounting Rule. Now the rule is going to, as seen in the list. And then in the list I declared that you can type in a two, a three or a four. I've simply created the rule that shows what I want you to do. Now, this rule is going to apply to the table called Accounting. In Accounting you'll notice I have an entry that is two and it's for receivables. Now remember, my rule stated that I was limited in what numbers I can type in. If I type in a Y and now I'm just going to go ahead and type in Financials, when I type in Financials you'll notice that my error message tells me that the column insert or update conflicts by a rule that has been created. If I say OK, the simple solution is to change the number, tab out and it's taken. We created this by going through the rule itself; create the accounting rule. Notice the accounting rule was created on the database called Accounting. You can see on the side of the screen that I opened Accounting so that you can see the columns, the specific column is the accounting ID. The reason that I wanted to show you that is I want to go to now where we bind the rule. So we create a rule and then we bind the rule. When I'm binding the rule I'm going to execute a stored procedure called Bind Rule. Now, we'll be talking more about stored procedures later in the video. Notice that I'm binding the rule called Accounting Rule. That was what I named the rule on top and I want that rule to be bound to the table called Accounting, the field called Accounting ID. Now if I go back to the rule, there is the Accounting underscore Rule, the name of the rule. Then I'll go to the table, the table is called Accounting. In Accounting I chose to bind the rule to the particular column. Now when I go to bind the rule, you can see that it's running the stored procedure, which is called Bind Rule. It chooses the rule and shows where the rule should be applied. It is very easy to actually create these rules for any situation. Any column in any table can have a rule bound to it. What better way to validate your data.