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 name


Syntax 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....


Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 73 articles authored by srirama

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: