SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

CHECK constraint with User Defined Function in SQL Server

Posted by Prashant on June 25, 2010


This post describes how to use result of an user defined function with CHECK constraint in SQL Server.  For demonstration, considered a situation where it is not  allowed to insert or update records where calculated age of a person is less than 18 years as per his/her Date of Birth.

So for this first we need to create the function before creating the CHECK constraint. Here is a function which will return age as per the date of birth provided.

/*
This function will take Date Of Birth as input parameter,
and returns Age in Years to the caller
*/
CREATE FUNCTION [dbo].[fnGetAge](@DateOfBirth DATETIME)
RETURNS SMALLINT
AS
BEGIN
DECLARE @Age SMALLINT
SET @Age =(DATEDIFF(YY, @DateOfBirth, GETDATE())-
(CASE
WHEN GETDATE() >= DATEADD(YY, DATEDIFF(YY, @DateOfBirth, GETDATE()), @DateOfBirth) THEN 0
ELSE 1
END))
RETURN @Age
END;
GO

Now create a table where CHECK constraint will refer to this function to check if the age of the person meets the required criteria or not (minimum 18 Years in this case).

--Create Customer table
CREATE TABLE Customers
(
CustID INT IDENTITY(1,1) NOT NULL,
CustName VARCHAR(100) NOT NULL,
DateOfBirth DATETIME NOT NULL,
Email VARCHAR(100),
CONSTRAINT pkCustomers PRIMARY KEY(CustID),
--Calculate & check if age of customer is atleast 18 Years
CONSTRAINT chkCheckAge CHECK(dbo.fnGetAge(DateOfBirth) >= 18)
)
GO

--Populate table with some sample data.
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('ABC','19810726','abc@cust.info'),
('XYZ','19840510','xyz@cust.info'),
('MNO','19720417','mno@cust.info')
GO
--Result Message
--(3 row(s) affected)

Now try to insert a record where calculated age is less than 18 years and see what happens.

--Try to insert a record where Age less than 18 Years(as per provided Date of Birth)
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('TEST','20010315','test@cust.info')
GO

/*
--Error Message
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "chkCheckAge".
The conflict occurred in database "SQLJourney", table "dbo.Customers", column 'DateOfBirth'.
The statement has been terminated.
*/

As the age does not meet the required criteria in defined CHECK constraint, it doesn’t allow to insert this record to the table.

Note:

CHECK constraint evaluates the provided expression while UPDATE operation as well.

You cannot DROP the function till the table (which refers to that function) exists in the database.

4 Responses to “CHECK constraint with User Defined Function in SQL Server”

  1. Pete Evans said

    Very nice and easy to understand example of this functionality. Thanks so much.

  2. Prashant said

    Reblogged this on SQL Journey.

  3. […] http://sqljourney.wordpress.com/2010/06/25/check-constraint-with-user-defined-function-in-sql-server… […]

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

 
Follow

Get every new post delivered to your Inbox.

Join 262 other followers

%d bloggers like this: