User Defined Rules (like check constraint) in SQL Server 2005:

You may be aware with CHECK Constraint in SQL Server 2005 which is used to check data integrity for columns. User Defined Rule is the same concept of checking data integrity and consistency. The only difference is you have to use CHECK constraint with every column you want to check for and User Defined Rule, you have to create it once and can use it with any column of any table. One time creation and life time usageJ.

You can create User Defined Rule by following T-SQL command:

CREATE RULE AgeRule AS @Age<=100 AND @Age>0

Now, let us create one table with which we will bind the above rule. If you have any table already created with Age field, you can bind this rule with it.

CREATE TABLE EmpDetail

(

EmpName VARCHAR(15),

Age INT

)

After having the table, we should bind the AgeRule with EmpDetail’s Age column with following T-SQL command.

EXEC sp_BindRule 'AgeRule', 'EmpDetail.Age'

Now, try to insert records which break the rules, it won’t accept those record(s).

Try using following INSERT statement and you will greet with error.

INSERT INTO EmpDetail VALUES('Ritesh',101)

Error:

“Msg 513, Level 16, State 0, Line 1

A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'AdventureWorks', table 'dbo.EmpDetail', column 'Age'.

The statement has been terminated.
”

If you modify your insert statement likes

INSERT INTO EmpDetail VALUES('Ritesh',29)

You will not face any error rather you will get cool message “(1 row(s) affected)”

NOTE: User Defined Rule is for backward compatibility in Microsoft SQL Server 2005 and may not be available in future so beware using it. You can use CHECK constraint for the same purpose.

Reference: Ritesh Shah