blog

Home / DeveloperSection / Blogs / Rules in SQL Server

Rules in SQL Server

Sumit Kesarwani3421 29-May-2013

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.


Rules in SQL Server

Output

Rules in SQL Server

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
Rules in SQL Server

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


Rules in SQL Server

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


Updated 18-Sep-2014

Leave Comment

Comments

Liked By