Wednesday, January 19, 2011

SQL Server: Function Based Check Constraint

Check constraints are used to apply business logic. These checks can easily and effectively be managed on application side. But if somehow you need to apply complex business logic as check constraints, you can use user defined functions for this purpose. Let’s create a function first to restrict any address entry from Afghanistan (apology to Taliban ;) )
USE AdventureWorks
CREATE FUNCTION dbo.fnc_RestrictedAddress
      @Address NVARCHAR(60)

 DECLARE @ResultBit BIT = 1

 IF @Address LIKE '%Afghanistan%'
      SELECT @ResultBit = 0

RETURN      @ResultBit


Open table in design view, right click anywhere on table in design view, click “CHECK CONSTRAINTS” and click "ADD" button. Move to expression part and edit it as given in screen shot.

Or you can edit desired table to apply check constraint with following t-sql.
ALTER TABLE [Person].[Address]  WITH NOCHECK ADD  CONSTRAINT [CK_Address] CHECK  (([dbo].[fnc_RestrictedAddress]([AddressLine1])=(1)))

Let’s check out constraint efficiency by inserting new record in “Address” table
USE AdventureWorks
INSERT INTO [AdventureWorks].[Person].[Address]
           ('Zahir Shah Road, Kabul, Afghanistan'

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Address". The conflict occurred in database "AdventureWorks", table "Person.Address", column 'AddressLine1'.
The statement has been terminated.

No comments:

Post a Comment

All suggestions are welcome