Applying CHECK constraint and creating rules in SQL server

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.

Create table

create table Employee

(

EmployeeId int,

Name varchar(50),

age int,

salary money

)

Alter table and add check constraint

ALTER TABLE Employee

ADD CONSTRAINT CK_emp_Age CHECK (Age > 0 AND Age < 60)

Alter table to drop constraint

ALTER TABLE Employee

DROP CONSTRAINT CK_emp_Age

Rule - Rules are constraints bound to columns from tables or to user defined data types

CREATE RULE cannot be combined with other Transact-SQL statements in a single batch. Rules do not apply to data already existing in the database at the time the rules are created, and rules cannot be bound to system data types.

A rule can be created only in the current database. After you create a rule, execute sp_bindrule to bind the rule to a column or to alias data type. A rule must be compatible with the column data type. For example, "@value LIKE A%" cannot be used as a rule for a numeric column. A rule cannot be bound to text, ntext, image, varchar (max), nvarchar(max), varbinary(max), xml, CLR user-defined type, or timestamp column. A rule cannot be bound to a computed column.

Create table

create table Employee

(

EmployeeId int,

age int

)

Create rule

CREATE RULE age_rule 

AS  

@age_rule>= 18 AND @age_rule <=60;

Bind rule

sp_bindrule age_rule, 'Employee.age'

Unbind rule

sp_unbindrule 'Employee.age'

Drop rule

DROP RULE age_rule

Difference in constraint and rule

We can bind rules to a datatypes whereas constraints are bound only to columns. This feature enable us to create our own data-type with the help of Rules and get the input according to that.

Rules are reusable so if you make rule one time you can use a rule multiple times to one or more columns.