RULE in SQL Server

In this article I would like to explain about “Rules” which are part of Constraints family. Constraints are one of the key factors in designing a table. Constraints are mainly used to implement business rules. Let't take one real world example. When you are opening a bank account, there is a bank rule that says, you need to maintain a minimum balance of 1000 in your account. This is a business rule.

There are several places where we can implement this rule.
The way I see is, implementing this in Back-end gives more flexibility. For now I will explain about how to implement this business rule in back-end database.

To implement this kind of rule either we can use RULE or CHECK Constraint. This article will implement this business rule using RULE constraint.

There are total six types of constraints in SQL Server to implement different types of business rules and they are:
1) Check Constraints.
2) Rules
3) Default Constraints
4) Unique Constraints
5) Primary Key Constraints.
6) Foreign Key Constraints.

Other Relevant articles related to Constraints:
.. List all the Primary Keys in SQL Server Database
.. List all the Foreign Keys in a SQL Server
.. Foreign Key and Primary Key in SQL Server

In my previous articles we did talk about all the different constrains except Rule.

RULE Constraint:
RULE is an object with in a database. First you create a rule and then bind this rule to any no.of columns with in database. So this is more towards to the Object Oriented programming, where you are trying to re-use the code/logic.

Create and Bind:

Let’s Create the RULE for our business rule:

“Need to maintain a minimum balance of 1000 in your Check-in account.”

Let’s create an account table.

CREATE TABLE ACCOUNT(
ACCOUNT_ID int NULL,
ACCOUNT_LOCATION varchar(10) NULL,
CUSTOMER_ID int NULL,
CHECK_IN_AMT int NULL,
SAVINGS_AMT int NULL
)

Step 1: Let's create a RULE

CREATE RULE Amount_rule
AS
(@CHECK_IN_AMT >= 1000)

Step 2: Bind this rule to a column.

EXEC sp_bindrule 'Amount_rule', 'account.CHECK_IN_AMT'

Lets try to insert data into this table

INSERT INTO ACCOUNT VALUES(1001,'NY',1,1000,20);
INSERT INTO ACCOUNT VALUES(1002,'NY',2,1010,20);
INSERT INTO ACCOUNT VALUES(1003,'NJ',3,1000,20);
INSERT INTO ACCOUNT VALUES(1004,'CT',4,1020,20);
INSERT INTO ACCOUNT VALUES(1005,'FL',5,1200,20);

I am able to insert all the above rows with out any issues/errors. This is because CHECK_IN_AMT column is always equal to or greater than 1000.

Now lets try to insert below record

INSERT INTO ACCOUNT VALUES(1006,'FL',6,800,20);

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 'testdemo', table 'dbo.ACCOUNT', column 'CHECK_IN_AMT'.
The statement has been terminated.


Now let’s try to reuse the same RULE on SAVINGS_AMT column.

EXEC sp_bindrule 'Amount_rule', 'account.SAVINGS_AMT'

Now lets try to insert below record
INSERT INTO ACCOUNT VALUES(1005,'FL',5,1200,20);

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 'testdemo', table 'dbo.ACCOUNT', column 'SAVINGS_AMT'.
The statement has been terminated.

DROP and Un-Bind:

Now let’s remove the rule on SAVINGS_AMT column.

EXEC sp_unbindrule 'account.SAVINGS_AMT'

DROP RULE Amount_rule;

Key Point:
Rules and Check constraints are like a brother and sister. Check constraint is specific to a column with in a table. Rules are not specific to a table or a column.

Rules are a backward compatibility feature and check constraints are preferable.