How to bind rule to a column in SQL

In this blog I'm going to explain how we can bind some rule to a Column of a table in SQL. Many times we want that the column must contain the value in a particular format.

For Ex. Column Emp_ID should start with alphabet and after that it must contain 3 integers.

Like 'A123' or 'E456'.

This can be done in SQL by applying rules on the column.

1. Create a table

CREATE TABLE EMP

(

   EMP_ID NVARCHAR(20),

   EMP_NAME NVARCHAR(100)

)

2. Create a rule which you want for that column

Syntax is:
CREATE RULE RULE_EMP

AS@EMP_ID LIKE ('[A-Z][0-9][0-9][0-9]')

Now to bind the rule to a column, below is the syntax.

SP_BINDRULE  Rule_EMP,'EMP.EMP_ID' 

Now, try to insert a row in the column.

INSERT INTO EMP(EMP_ID,EMP_NAME) VALUES( '1234','VIJENDRA')

//ERROR
a column inserts or updates conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'TEST', table 'dbo.Emp', column 'EMP_ID'.

INSERT INTO EMP(EMP_ID,EMP_NAME) VALUES( 'A234','VIJENDRA')  //ROW INSERTED SUCCESSFULLY 

So there you are we have imposed restriction on the column EMP_ID that it can only take the value in a format mentioned above.

Do let me know your feedback or comments.