Transact SQL – Rules and Check Constraints

Background

A few months back I was looking at some code and noticed that it uses functionality known as “SQL Rule“.

It is use to scrutinize data that can be placed in a column.

Going forward, Microsoft has a container name for data integrity enforcement.  And, that name is constraints.

SQL

In the rest of this post, we will cover the difference in implementation of creating and attaching rules and/or constraints to Table Columns.

Rule

Create Rule – ruleIsNotEmpty


if exists
   (
     select *
     from   sys.objects tblSO
     where  tblSO.[name] = 'ruleIsNotEmpty'
   )  
begin
	set noexec on   
end
go

/*
	CREATE RULE (Transact-SQL)
		https://msdn.microsoft.com/en-us/library/ms188064.aspx
		
*/
CREATE RULE [dbo].[ruleIsNotEmpty]
AS   
  (
		(@ssn is not null)
	and (@ssn != '')
  )

go

set noexec off
go


Create Table – [ruleIsNotEmpty].[personClassic]


if SCHEMA_ID('ruleIsNotEmpty') is null
begin

	exec('create schema [ruleIsNotEmpty] authorization [dbo]')

end
go	

if exists
   (
     select *
     from   sys.objects tblSO
     where  tblSO.[type] = 'U'
     and    tblSO.[object_id] 
				= object_id('[ruleIsNotEmpty].[personClassic]')
   )  
begin
	set noexec on   
end
go

CREATE TABLE [ruleIsNotEmpty].[personClassic]
(
	  [ssn]			varchar(30) not null
	, [firstname]	varchar(60) not null
	, [lastname]	varchar(60) not null
	
	, [addedBy]		sysname not null
		constraint  [constraintPCAddedBy]
			default ORIGINAL_LOGIN()
				
	, [addedOn]		datetime not null
		constraint [constraintPCDefault]
			default getutcdate()
			
)

go

set noexec off
go

Apply Rule to Table


/*
	Check If Object/Column has rule binded to it
		If Not, bind it to Table
*/
if not exists
	(
		select tblSOR.*

		from   sys.columns tblSC
		
		inner join sys.objects tblSOR

				on tblSC.rule_object_id = tblSOR.object_id
				
		where  tblSOR.[type] = 'R'
		
		and    tblSOR.[object_id]
				= object_id('[dbo].[ruleIsNotEmpty]')
		
	)
begin

	print 'Bind Rule [dbo].[ruleIsNotEmpty] to [ruleIsNotEmpty].[personClassic].[ssn] ...'
		
	exec sp_bindrule 
			  '[dbo].[ruleIsNotEmpty]'
			, '[ruleIsNotEmpty].[personClassic].[ssn]'
			; 

	print 'Binded Rule [dbo].[ruleIsNotEmpty] to [ruleIsNotEmpty].[personClassic].[ssn]'
	
end
GO



Constraint / Check Constraint

Create Table


use [tempdb]
go

if SCHEMA_ID('ruleIsNotEmpty') is null
begin

	exec('create schema [ruleIsNotEmpty] authorization [dbo]')

end
go	

-- drop table [ruleIsNotEmpty].[personNew]

if exists
   (
     select *
     from   sys.objects tblSO
     where  tblSO.[type] = 'U'
     and    tblSO.[object_id] 
				= object_id('[ruleIsNotEmpty].[personNew]')
   )  
begin
	set noexec on   
end
go

CREATE TABLE [ruleIsNotEmpty].[personNew]
(
	  [ssn]			varchar(30) not null
	, [firstname]	varchar(60) not null
	, [lastname]	varchar(60) not null
	
	, [addedBy]		sysname not null
		constraint  [constraintAddedBy]
			default ORIGINAL_LOGIN()
				
	, [addedOn]		datetime not null
		constraint [constraintDefault]
			default getutcdate()
			
)

go

set noexec off
go


Attach Check Constraint to Table



if OBJECT_ID('[ruleIsNotEmpty].[personNew]') is not null
begin

	if not exists
	(
		select tblSCC.*

		from  sys.objects tblSO
		
		inner join sys.check_constraints tblSCC

				on tblSO.object_id = tblSCC.parent_object_id
				
		where  tblSO.object_id 
				= object_id('[ruleIsNotEmpty].[personNew]')
				
		and  tblSCC.object_id 
				= object_id('[ruleIsNotEmpty].[constraintCheckIsNotEmpty]')					
		
	)

	begin

		print 'Add Constraint [constraintCheckIsNotEmpty] to [ruleIsNotEmpty].[personNew] ...'
			
		ALTER TABLE [ruleIsNotEmpty].[personNew]
			WITH CHECK 
			ADD CONSTRAINT [constraintCheckIsNotEmpty]
				CHECK 
				  (
						([ssn] is not null)
					and ([ssn] != '')
				  )
				;
				
		print 'Added Constraint [constraintCheckIsNotEmpty] to [ruleIsNotEmpty].[personNew]'				

	end

end
go



 

Summary

So to me one of the key difference is that for SQL Rules, one uses “Create Rule” to creates the rule and uses “sp_bindrule” to bind the rule across related columns.

On the other hand, one institutes Check Constraints using “Alter table/Add Check Constraint”.

 

References

  1. Rules
  2. Constraint
    • Create
      • Create Check Constraints
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s