RULE III: How RULE are different from Constraint

Read Prev Article: A backward compatibility feature ‘RULE’ in SQL Server

Read Prev Article: RULE II: Applying RULE on User Defined Data Type

These are few difference which I found..

1. CHECK are applied only to column, whereas RULE are applied to Column as well as User Defined Datatype.

2. CHECK Constraint are bound to Create Table statement, you cannot reuse CHECK Constraint, whereas RULE are created as separated objects and bound to any number of column, so RULE offer reusability.

3. RULE are backward-compatibility feature. So anytime in new version it can be removed.  

4. RULE are not ANSI SQL, so you may see its implementation in SQL Server and probably Sybase. 

                                  

We already covered first point in previous article, here we will check, “CHECK Constraint are bound to Create Table statement, you cannot reuse CHECK Constraint, whereas RULE are created as separated objects and bound to any number of column, so RULE offer reusability.”

We will create a Rule then we will try to bind the rule to many column and datatype. Lets start the demonstration.

Creating User Defined Data Type

create type phoone_number_type from varchar(13) not null

go

Creating Table using User Defined Data Type

create table PersonContacts

(pid int identity(1,1),

name varchar(10),

phone_number phoone_number_type)

Creating RULE

create rule phone_no_constraint

as

len(@phone) = 13 and

substring(@phone,3,1) = '-' and

isnumeric( left(@phone,2)) =1 and

isnumeric(right(@phone,10)) =1

Command(s) completed successfully.

Binding Rule to the column

exec sp_bindrule 'phone_no_constraint', 'phoone_number_type'

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 insert a proper record in the table.

insert into PersonContacts values('Lopa','91-9099956156')

(1 row(s) affected)

Let’s query the table to conform insertion.

select * from PersonContacts

pid         name       phone_number

----------- ---------- -------------

4           Lopa       91-9099956156

Lets Apply the same rule to a column (Re-Usability)

create table Contacts_Employee

(eid int identity(1,1),

ename 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.

Insert record

insert into Contacts_Employee values(Pragyan,'91-9099956156')

(1 row(s) affected)

Unbind RULE

exec sp_unbindrule 'phoone_number_type'

Rule unbound from table column.

exec sp_unbindrule 'Contacts_Employee.phone_number'

Rule unbound from table column.

Drop Rule

drop rule phone_no_constraint

Command(s) completed successfully.

So here we can see the same rule has been applied to two table. 

Deeply involvement in code gives strength and courage, we named it LOVE between coder and code!!!

 

Post Reference: Vikram Aristocratic Elfin Share

RULE II: Applying RULE on User Defined Data Type

Read Prev Article: 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.

In previous article we saw, how we can create rule on Table Column, in this post we will see how we can create RULE on User Defined Data Type

                                  

Creating User Defined Data Type

create type phoone_number_type from varchar(13) not null

go

Creating Table using User Defined Data Type

create table PersonContacts

(pid int identity(1,1),

name varchar(10),

phone_number phoone_number_type)

Creating RULE

create rule phone_no_constraint

as

len(@phone) = 13 and

substring(@phone,3,1) = '-' and

isnumeric( left(@phone,2)) =1 and

isnumeric(right(@phone,10)) =1

Command(s) completed successfully.

Binding Rule to the column

exec sp_bindrule 'phone_no_constraint', 'phoone_number_type'

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('Neha','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('Lopa','91-9099956156')

(1 row(s) affected)

Let’s query the table to conform insertion.

select * from PersonContacts

pid         name       phone_number

----------- ---------- -------------

4           Lopa       91-9099956156

Unbind RULE

exec sp_unbindrule 'phoone_number_type'

Rule unbound from table column.

Drop Rule

drop rule phone_no_constraint

Command(s) completed successfully.

My enjoyment is my code, but u r taking over my space J

 

Post Reference: Vikram Aristocratic Elfin Share

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