Quick Start Guide to SQL Server 7 -- Part 3, Page 6
Rules are wonderful little widgets that allow you to dictate what sort of information can go into a particular field. They're what Microsoft Access calls a Validation Rule.
However in Access, you put the rules behind each individual field. In SQL Server, you create a rule, then tell the system which columns that rule applies to.
Here's an example of a rule: @value = 'P'
This tells SQL Server the field @value must equal 'P'. If the rule is evaluated as True (eg, the field @value is equal to 'P'), the field value is accepted. If the rule is evaluated as False (eg, the field @value is not 'P'), the field value is rejected.
Here's another example: @value >= #7000 AND @value < #25000
This allows any money amount greater than or equal to #7,000 and less than #25,000.
And one more example: @value IN ('1389', '0736', '0877')
This allows a field value of 1389, 0736 or 0877 anything else gets thrown out the window.
Note that existing table data isn't checked by added rules, only updates and new additions.
To create a Rule:
- View your Database in Enterprise Manager (as before)
- Right-click the yellow 'Rules' icon, and select 'New Rule'
- Type in a Name for your Rule
- Specify the Rule text
- Click OK to save
To bind a Rule:
- Create your Rule (as before)
- Open your Rule by double-clicking on it in Enterprise Manager
- To bind a rule to a field, click 'Bind Columns', select your table, add the individual fields, then click OK
- To bind a rule to a particular user defined type (as per the last section), click 'Bind UDTs', then check the 'Bind' box for all those to be affected
- Checking the 'Future Only' box means existing columns using the UDT won't inherit the new rule
- Click OK to save
To edit a Rule:
- Open your Rule, remove all dependencies of the Rule, then close it by clicking OK
- In this instance, dependencies are items that rely on this rule to work, such as a bound column
- To view dependencies, right-click on your rule, select 'All Tasks', 'Display Dependencies'
- To remove dependencies, open your Rule, then click on both the 'Bind Columns' and 'Bind UDTs' buttons, removing all columns that rely on the rule
- Open your Rule again and edit as required
- Click OK
- Open your Rule yet again and rebind the columns as before, closing and saving when finished
Check us out in a fortnight for the fourth and final part in our print-and-collect series. We'll be covering; transactions, triggers and stored procedures, as well as even more Visual Basic ADO code.