Rule Constraint

In this blog, we’ll discuss about RULE constraint.

  1. It is defined in the database level.
  2. It storage location is Database=>Programmability=>Rule.
  3. It provides a way of code re-usability.
  4. A single constraint can be attached with multiple tables and columns in the table.
  5. 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. 
  6. It is similar to CHECK constraint.
  7. Only 1 Rule can be applied at a time in a column.
  8. 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.
  9. It cannot be altered after defining. It need to be drop and re-create with the new definition.
  10. To apply the constraint, it need to be bind with the column.
  11. To drop the constraint, it need to unbind from all the column of the table.
  12. 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.

Example

–Define the RULE constraint
CREATE RULE Rule_Int_Positive AS @A > 0

create table test
(
Id int,
Gender varchar(10)
)

–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’)

–Successfully inserted
insert into test values(1,’Male’)

Select * from test

/* Output
Id Gender
1 Male
*/

–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
AS
(@a=’UNKNOWN’) OR
(
LEN(@a)=10
AND ISNUMERIC(LEFT(@a,3))=1
AND SUBSTRING(@a,4,1)=’ ‘
AND ISNUMERIC(SUBSTRING(@a,5,3))=1
AND SUBSTRING(@a,8,1)=’-‘
AND ISNUMERIC(RIGHT(@a,2))=1
)

To view all the RULES defined in the current database

SELECT
OBJECT_NAME(sc.rule_object_id) AS RuleName
,OBJECT_NAME(sc.object_id) AS TableName
,sc.name AS ColumnName
FROM
sys.objects so
INNER JOIN
sys.columns sc
ON
so.object_id = sc.rule_object_id
WHERE
so.type_desc = ‘rule’

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

Integrity Constraint in SQL

In this blog, we’ll discuss about Integrity constraint in SQL Server.

Integrity constraint are rules implemented in the tables to guarantee the accuracy, completeness, or consistency of data in tables and keep safe the business logic of the schema design.Constraints are part of a database schema definition

In SQL, all the constraints are mainly grouped together in 4 major types, they are:

  1. Primary integrity : which can be done by using
    1. PRIMARY KEY
    2. UNIQUE KEY
  2. Referential integrity : which can be done by using
    1. FOREIGN KEY
  3. Domain integrity : which can be done by using
    1. DEFAULT
    2. NOT NULL
    3. CHECK
  4. User Defined integrity : which can be done be using
    1. RULE

Please click on each constraints for detail explanation.

To get the details (like Constraint_Type,Constraint_Name,Constraint_Keys, enable / disable etc. ) of all the constraint define in a table execute the system procedure with table name.

EXEC sp_helpconstraint ‘dbo.Employee1’

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.