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
Leave Comment