Archive
CHECK constraint with User Defined Function in SQL Server
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.
Categories
- AngularJs (13)
- ASP.NET (104)
- C Programming (1)
- Code Project (2)
- CodeProject (1)
- Entity Framework (6)
- Ext.NEt (1)
- HTML5 (1)
- Interview .NET (2)
- Introduction (1)
- JQuery (1)
- Linq (1)
- Microsoft office (2)
- MOSS-(Sharepoint 2010) (2)
- MVC (5)
- Outlook (1)
- Sql Server (30)
- SSRS (5)
- TFS (1)
- Tips & Tricks (16)
- Twitter bootstrap (1)
- VB.NET (1)
- Visual Studio 2012/2013 (1)
- Web Services (1)
- Windows Workflow (1)
Top Posts & Pages
- How to Collapse all #regions only(!) in C# (Visual Studio)
- Disable click outside of bootstrap model area to close modal
- MYSQL : How to skip first 10 records from a SELECT query and take next 10?
- Error :(System.Web.UI.HtmlControls.HtmlIframe) is not compatible with the type of control (System.Web.UI.HtmlControls.HtmlGenericControl) in VS2017
- Updatepanel triggers another updatepanel
- How to find control with in repeater on button click event
- Open FancyBox from Code behind on button click
- COALESCE with DISTINCT in SQLServer
- USAePay Token error: 23 Specified source key not found
- GridView: Findcontrol from HeaderTemplate/ItemTemplate
Recent Posts
- How to compile .NET Core Console Application as .exe File?
- Define Composite Key in Model.(Core, EF Code First)
- How to remove x from Internet Explorer ?
- The client and server cannot communicate, because they do not possess a common algorithm
- How to check if all items are the same in a list C#
- How to Collapse all #regions only(!) in C# (Visual Studio)
- Issue: Password field returned empty in edit mode
- Load XML from URL giving an error (The remote server returned an error: (401) Unauthorized.)
- How to use Variable in MySQL Like Clause ?
- Error :(System.Web.UI.HtmlControls.HtmlIframe) is not compatible with the type of control (System.Web.UI.HtmlControls.HtmlGenericControl) in VS2017
Blog Stats
- 157,096 hits
Rahul Bhatia
Profile
Blog Stats
- 157,096 hits
Comments