Using a UDF in a CHECK constraint

Question

Using a UDF in a CHECK constraint

Slabber

SSC Eights!

Points: 852
More actions
February 20, 2009 at 5:29 am

#201469

Hi Everyone
I have a table of schedules that shouldn’t overlap (date- and time-wise), and I’ve been trying to use the technique described in Tony Rogerson’s “Using a UDF in a CHECK constraint to check validity of History Windows (Start - End Date Windows)” blog post (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/02/07/using-a-udf-in-a-check-constraint-to-check-validity-of-history-windows-start-end-date-windows.aspx) to enforce this.
However I’m tearing my hair out because I can’t get my UDF to work – even with an empty table it always indicates that a matching row exists (using the debugger). I’m using SQL Server 2008 Dev build 1787 (CU3). I’ve created a slimmed down test version of my UDF solution checking against just one column (that still exhibits the same problem) as per the end of my post.
I’m clearly doing something wrong – could someone please put me out of my misery?
Many thanks - Graham
CREATE TABLE [dbo].[TestTable](
[TestTableId] [int] IDENTITY(1,1) NOT NULL,
[TestColumnId] [int] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[TestTableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[fn_CheckIsValid]
(
@TestColumnIdINT
)
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @status VARCHAR(3)
IF EXISTS
(
SELECT*
FROMdbo.TestTable
WHERETestColumnId = @TestColumnId
)
SET @status = 'BAD'
ELSE
SET @status = 'OK'
RETURN@status
END
GO
ALTER TABLE [dbo].[TestTable] WITH CHECK ADD CONSTRAINT [CK_CheckIsValid] CHECK (([dbo].[fn_CheckIsValid]([TestColumnId])='OK'))
GO
INSERT [dbo].[TestTable] VALUES(1) -- Fails!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 1

Your problem here is that the row that is causing it to fail is the row you are adding on the insert (it inserts, then checks so it will fail EVERY time).

The way to remedy this would be to add the ID column to the Function and in your exist statement check for

IF EXISTS (SELECT * FROM dbo.TestTable WHERE TestColumnId = @TestColumnId and TestTableID <> @TestTableID)

That way it will see if there are other rows outside of the one you are inserting that match the check constraint. If so it will fail.

By doing this if you run

INSERT [dbo].[TestTable](TestColumnId) VALUES(1)

One time, it will work.

Run it a second time and it will fail.

Slabber SSC Eights! Points: 852 More actions · Answer 2

Hi Mike

Thanks very much for your suggestion - it works a treat! One less thing to think about over the weekend.

I did wonder if the problem was that the row was being inserted and then checked, but dismissed it thinking it would happen the other way round. Just out of curiosity, is there a reason for adding the row and then checking afterwards?

Cheers - Graham

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 3

I honestly have no idea what the thought process was behind this.

However, I suppose it is logical that since the Constraint exists on the table that the data would have to actually be inserted into the table before it could be checked. For this case in particular, it seems like you could use a Before trigger and make it perform in essentially the same manner as this constraint would and that way the data would not be inserted into the table first. But really either way should work the same.