---
title: "Rules in SQL Server"  
description: "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 va"  
author: "Sumit Kesarwani"  
published: 2013-05-29  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/518/rules-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Rules in SQL Server

In this blog, I’m explaining the rules in [sql server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) and how to create it.

Rules enforce domain [integrity](https://www.mindstick.com/forum/159808/how-do-you-verify-the-integrity-of-a-restored-database) by providing sophisticated checking of the valid values. Rules are used to ensure that values either match a [pattern](https://www.mindstick.com/forum/2314/what-is-the-mvp-and-mvc-pattern), match a list of values or fall within range of values. Rules are also standalone [objects](https://www.mindstick.com/forum/145447/what-are-objects) that require special [permission](https://www.mindstick.com/forum/159434/linux-service-permission-denied-error-adjust-permissions) 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](https://www.mindstick.com/forum/12711/select-query-with-and-condition) for which the rule is being created.

##### Example

CREATE RULE dept AS @dept IN('IT','HR','[Testing](https://www.mindstick.com/articles/1849/role-of-testing-in-software-development)','[Finance](https://www.mindstick.com/articles/126323/7-ways-technology-is-changing-the-finance-world)')

##### Binding Rules

Rules are [independent](https://answers.mindstick.com/qa/98599/what-is-the-longest-river-in-the-commonwealth-of-independent-states) objects, which have to be bound to a data type or a column. Rules are binded using the sp_bindrule system [stored procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net).

##### 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](https://www.mindstick.com/blogs/fe41b668-7c9e-4191-b8de-3eccf8083c9c/images/f8da4a01-4bfe-48c9-9afb-4d3e302221d0.jpg)

##### Output

![Rules in SQL Server](https://www.mindstick.com/blogs/fe41b668-7c9e-4191-b8de-3eccf8083c9c/images/904ed629-81c3-49ae-95a6-2250181614a4.jpg)

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](https://www.mindstick.com/blogs/fe41b668-7c9e-4191-b8de-3eccf8083c9c/images/a7e17925-1bc1-4470-984c-e77572991c11.jpg)

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

\
![Rules in SQL Server](https://www.mindstick.com/blogs/fe41b668-7c9e-4191-b8de-3eccf8083c9c/images/7f9c1cac-fc8f-430d-b9a0-6a18de949f7a.jpg)

##### 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

---

Original Source: https://www.mindstick.com/blog/518/rules-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.