In this blog, we’ll discuss about RULE constraint.
- It is defined in the database level.
- It storage location is Database=>Programmability=>Rule.
- It provides a way of code re-usability.
- A single constraint can be attached with multiple tables and columns in the table.
- The datatype of the RULE & the column where it is going to be attach must be same or compatible. In case the datatype are different it won’t give any error until any insert / update statement is issued.
- It is similar to CHECK constraint.
- Only 1 Rule can be applied at a time in a column.
- Binding any RULE with a column will override any existing RULE attached with the column. It’ll never give any warning or error but simple override the existing RULE and applied the newer one.
- It cannot be altered after defining. It need to be drop and re-create with the new definition.
- To apply the constraint, it need to be bind with the column.
- To drop the constraint, it need to unbind from all the column of the table.
- As per Microsoft, it is not a recommended practice to use it, as it’ll be removed from the future version of MS SQL Server.Click here for MSDN detail.
–Define the RULE constraint
CREATE RULE Rule_Int_Positive AS @A > 0
create table test
–Bind the RULE constraint
EXEC sp_bindrule Rule_Int_Positive, ‘test.Id’
–Error on issuing the below insert statement
insert into test values(0,’Male’)
insert into test values(1,’Male’)
Select * from test
–UnBind the Rule or remove the constraint from the column
EXEC sp_unbindrule ‘test.Id’
–Delete the RULE constraint
Drop RULE Rule_Int_Positive
–Complex RULE to store the PinCode in the format : 123 456-78 or UNKNOWN
CREATE RULE Rule_PinCode
AND SUBSTRING(@a,4,1)=’ ‘
To view all the RULES defined in the current database
OBJECT_NAME(sc.rule_object_id) AS RuleName
,OBJECT_NAME(sc.object_id) AS TableName
,sc.name AS ColumnName
so.object_id = sc.rule_object_id
so.type_desc = ‘rule’
Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.