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)

)    

RETURNS BIT

AS

 BEGIN

 DECLARE @ResultBit BIT = 1

 IF @Address LIKE '%Afghanistan%'

      SELECT @ResultBit = 0

RETURN      @ResultBit

 END

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]

           ([AddressLine1]

           ,[AddressLine2]

           ,[City]

           ,[StateProvinceID]

           ,[PostalCode]

           ,[rowguid]

           ,[ModifiedDate])

     VALUES

           ('Zahir Shah Road, Kabul, Afghanistan'

           ,'abc'

           ,'Kabul'

           ,'1'

           ,'51000'

           ,NEWID()

           ,GETDATE())

GO

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.