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
Creating a Rule
Rules are created using the CREATE RULE statement.
CREATE RULE RuleName AS Condition_Expression
RuleName : Rule name must be valid and unique
name in database.
Condition_Expression : Condition for which the
rule is being created.
CREATE RULE dept AS @dept IN('IT','HR','Testing','Finance')
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
see the effect of rule, add a new row to the table.
In this example, we
insert a row in the table, it easily added because dept value is according to
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.
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.
DROP RULE RuleName
DROP RULE dept