Home > DeveloperSection > Blogs > Rules in SQL Server

Rules in SQL Server


Database Database 
Ratings:
0 Comment(s)
 1472  View(s)
Rate this:

Rules in SQL Server

In this blog, I’m explaining the rules in sql server and how to create it.

Rules enforce domain integrity by providing sophisticated checking of the valid values. Rules are used to ensure that values either match a pattern, match a list of values or fall within range of values. Rules are also standalone objects that require special permission to create. They are stored in the table – sysobjects and syscomments.

Creating a Rule

Rules are created using the CREATE RULE statement.

Syntax:

CREATE RULE RuleName AS Condition_Expression

where

·         RuleName : Rule name must be valid and unique name in database.

·         Condition_Expression : Condition for which the rule is being created.

Example

CREATE RULE dept AS @dept IN('IT','HR','Testing','Finance')

Binding Rules

Rules are independent objects, which have to be bound to a data type or a column. Rules are binded using the sp_bindrule system stored procedure.

Syntax:

sp_bindrule RuleName, Object_Name

Example

sp_bindrule dept,'EMP.DEPT'

To see the effect of rule, add a new row to the table.


Output

In this example, we insert a row in the table, it easily added because dept value is according to the rule.

Try another insert query

in this query, the dept value is not according to the rule, so u will get an error like this:


Unbinding the Rule

To unbind a rule use the sp_unbindrule system stored procedure.

Syntax:

Sp_unbindrule  Object_Name

Example

sp_unbindrule 'EMP.DEPT'

Dropping a Rule

Rules can be using DROP RULE statement. Rules must be unbound with any column before dropping, if they are bounded with any column, they cannot be dropped.

Syntax:

DROP RULE RuleName

Example

DROP RULE dept


Don't want to miss updates? Please click the below button!

Follow MindStick