About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Friday, May 30, 2014

A backward compatibility feature ‘RULE’ in SQL Server


RULES are constraint that bound to either column or User defined data type, they restrict the data entry to follow certain condition. They are quite different from Constraint which we will discuss in next series of article. Lets see how we can create RULE on column

Creating RULE

create rule phone_no_constraint
as
len(@phone) = 13 and
substring(@phone,2,1) = '-' and
isnumeric( left(@phone,2)) =1 and
isnumeric(right(@phone,10)) =1
Command(s) completed successfully.

Let’s create a table with a phone number field and check how we can assign Rule to the Phone number field.

create table PersonContacts
(pid int identity(1,1),
name varchar(10),
phone_number varchar(13))
Command(s) completed successfully.

Binding Rule to the column

exec sp_bindrule 'phone_no_constraint', 'PersonContacts.phone_number'
Rule bound to table column.

Now since Rules are bind to the column, lets try to insert value to the table. Here we have rule for Phone number is ’XX-XXXXXXXXX’ and X is integer type. Lets try to insert record against to Rule defined to Phone number and see what will be the result of insert.

insert into PersonContacts values('Snehal','9820663556')
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 'MyTest', table 'dbo.PersonContacts', column 'phone_number'.
The statement has been terminated.

An attempt to insert phone number not following Rules does not allow to insert record to the table.
Lets insert a proper record in the table.

insert into PersonContacts values('Rinny','91-9099956156')
(1 row(s) affected)

Let’s query the table to conform insertion.

select * from PersonContacts
pid         name       phone_number
----------- ---------- -------------
3           Rinny      91-9099956156

Unbind RULE

exec sp_unbindrule 'PersonContacts.phone_number'
Rule unbound from table column.

Drop Rule

drop rule phone_no_constraint
Command(s) completed successfully.

Life without CODE is now happening…But oh my code, you are positive current to my love
 

Post Reference: Vikram Aristocratic Elfin Share

No comments:

Post a Comment