>SQL Server: Function Based Check Constraint


>
<!–
/* Font Definitions */
@font-face
{font-family:"Cambria Math”;
panose-1:2 4 5 3 5 4 6 3 2 4;
mso-font-charset:0;
mso-generic-font-family:roman;
mso-font-pitch:variable;
mso-font-signature:-1610611985 1107304683 0 0 159 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;
mso-font-charset:0;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:-1610611985 1073750139 0 0 159 0;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;
mso-font-charset:0;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:536871559 0 0 0 415 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:””;
margin-top:0in;
margin-right:0in;
margin-bottom:10.0pt;
margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri“,”sans-serif”;
msoascii-font-family:Calibri;
msoascii-theme-font:minor-latin;
msofareast-font-family:Calibri;
msofareast-theme-font:minor-latin;
msohansi-font-family:Calibri;
msohansi-theme-font:minor-latin;
msobidi-font-family:Arial;
msobidi-theme-font:minor-bidi;}
.MsoChpDefault
{mso-style-type:export-only;
mso-default-props:yes;
msoascii-font-family:Calibri;
msoascii-theme-font:minor-latin;
msofareast-font-family:Calibri;
msofareast-theme-font:minor-latin;
msohansi-font-family:Calibri;
msohansi-theme-font:minor-latin;
msobidi-font-family:Arial;
msobidi-theme-font:minor-bidi;}
.MsoPapDefault
{mso-style-type:export-only;
margin-bottom:10.0pt;
line-height:115%;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;
mso-header-margin:.5in;
mso-footer-margin:.5in;
mso-paper-source:0;}
div.Section1
{page:Section1;}
–>

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.
Advertisements

Posted on January 19, 2011, in Sql Server 2005, Sql Server 2008, Tables, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: