How to Create a Rule in SQL - SERVER DATABASE ?
Today in this Article i want to tell you what is a Rule ? How and when it is applicable to your Sql-server Database ? What is the Syntax to Create Rule or Drop Rule? How to Bind the rule to a Column i will discuss here with Code Snippets ?
What is a rule in SQL-SERVER ?
As in English language the synonym of Rule is nothing but a principles governing as it will not much differ with terms of SQL-SERVER database. A Rule is nothing but enforcing the domain integrity for Columns or User Defined data types. The rule is applied a to the Column or the User defined datatype before an insert or Update statement issued. In Other words , a rule specifies a restriction of values for a Column or User Defined datatype. Rules are used to implement business related restrictions or Limitations Syntax for Creating your First Rule on your SQL -SERVER DATABASE TABLE
Create Rule rulename as Conditional Expression
Rulename : specifies the name of the new rule that must conform to rules for identifiers.
Condition Expression specifies the condition(s) that specifies the rule it can be an expression that is valid in where clause and can have elements such as Arithmetic operators, relational operators, IN , NOT IN , LIKE , between and so on...
The variable specified in the Conditional Expression must be prefixed with @ symbol EXAMPLE OF RULE :
CREATE RULE AS @Entertainment IN ('MOVIES', 'CRICKET', 'FOOTBALL' , 'RUGBY' )
In the Column Entertainment only the above set of Values will be accepted.
You can also Use Not IN OPERATOR to restrict the Column values Under Rule
The Information of a rule can be Obtained by sp_help system stored procedure with text you can use sp_helptext system stored procedure. System stored procedure for Rule
sp_help rule name or sp_helptext rule nameSyntax to Drop a Rule
DROP RULE RULE NAME
you can create a rule with regular expression like for zipcode only allows Numeric below is the Code for that..
Create Rule rulZipcode
as
@zipcode Like '[0-9] [0-9][0-9][0-9][0-9]- [0-9][0-9][0-9][0-9] '
Zipcode pattern should be 83838-5678
Hope you got idea about the rule....
Follow