Check Constraint Which Uses A Custom Function

Question

I want to let user to enter, only one entry of data with specific ReqNo, that it's Type is equal 'SS'. There are two issue:

I Receive following error:

Msg 4145, Level 15, State 1, Line 21 An expression of non-boolean type specified in a context where a condition is expected, near ')'.

This code should be normalized for update too though myself won't do update through my application, but maybe the future DB Admin which operate my application data, do something at some point.

List ENDS ( I add this line so the parser will start parsing the code, which otherwise it wouldn't )

CREATE FUNCTION dbo.ReqNoIsUniquePerReserve
(
    @ReqNo AS BIGINT,
    @Type AS CHAR
)
RETURNS BIT
AS
BEGIN
    IF UPPER(@Type) = 'SS'
    BEGIN
        IF EXISTS (SELECT TOP 1 Id FROM dbo.LiaQueue WHERE @ReqNo = ReqNo)
            RETURN 0; 
        ELSE
            RETURN 1;
    END
    ELSE IF @ReqNo IS NOT NULL
    BEGIN
        IF EXISTS (SELECT TOP 1 ID FROM dbo.LiaQueue WHERE @ReqNo = ReqNo)
            RETURN 1;
        ELSE
            RETURN 0;
    END
    RETURN 1;
END

ALTER TABLE dbo.LiaQueue 
ADD CONSTRAINT CK_LiaQueue_ReqNo_Unique 
CHECK (dbo.[ReqNoIsUniquePerReserve](ReqNo,[Type]))

UPDATE

USE [Liaison]
GO
/****** Object:  UserDefinedFunction [dbo].[ReqNoIsUniquePerReserve]    Script Date: 10/03/2016 12:48:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ReqNoIsUniquePerReserve]
(
    @Id AS BIGINT,
    @Command AS NVARCHAR,
    @ReqNo AS BIGINT
)
RETURNS BIT
AS
BEGIN
    IF UPPER(@Command) = 'SS'
    BEGIN
        IF EXISTS (SELECT TOP 1 Id FROM dbo.LiaQueue WHERE @ReqNo = ReqNo AND Id <> @Id)
            RETURN 0; 
        ELSE
            RETURN 1;
    END
    ELSE IF @ReqNo IS NOT NULL
    BEGIN
        IF EXISTS (SELECT TOP 1 ID FROM dbo.LiaQueue WHERE @ReqNo = ReqNo)
            RETURN 1;
        ELSE
            RETURN 0;
    END
    RETURN 1;
END



ALTER TABLE dbo.LiaQueue 
ADD CONSTRAINT CK_LiaQueue_ReqNo_Unique 
CHECK (dbo.[ReqNoIsUniquePerReserve](Id, Command, ReqNo) = 1)

score 1 · Accepted Answer · answered Oct 03 '16 at 08:15

1

For 1)

Have you tried:

ALTER TABLE dbo.LiaQueue 
ADD CONSTRAINT CK_LiaQueue_ReqNo_Unique 
CHECK (dbo.[ReqNoIsUniquePerReserve](ReqNo,[Type]) = 1)

For 2), I'm not so sure what you mean.

answered Oct 03 '16 at 08:15

DVT

3,014
1
13
19

isn't BIT itself 1(TRUE)/0(FALSE) value? that you need to re-conditionalize it? if user try to update, this record itself count as one (jsut now i think as we do the condition for record which don't have ID of this one, can that solve the issue?) – Hassan Faghihi Oct 03 '16 at 08:34
it bypass the error, but still doesn't work, i also update my function... – Hassan Faghihi Oct 03 '16 at 09:19
I don't think SQL Server treat BIT as boolean value. Also, I don't think using the CHECK CONSTRAINT is the best way to solve this (if I understand 2 correctly). Have you tried using TRIGGER? – DVT Oct 03 '16 at 11:47
can i then make EF migration to apply that trigger? it can be nice... BTW< check constrain doesn't work,... some how, you know... BTW, TSQL doesn't contain a definition for boolean, so the bit should be one, what else is there to return? there should be something if BIT isn't one of them. – Hassan Faghihi Oct 04 '16 at 13:18
If you put a trigger on the table and if you define the trigger to fire on UPDATE and on INSERT, then, any UPDATE and INSERT ran should obey the trigger. This should be true whether you do it through, SSMS or through other method (unless the one who run the command explicitly tell SQL Server to ignore the trigger, I remember there is a hint to do that, but that only happen if the user have the privilege, and in that case, the user can drop your trigger too). You can read more about trigger here: https://msdn.microsoft.com/en-us/library/ms189799.aspx – DVT Oct 04 '16 at 16:26
i know trigger, but whereever i look for solution, that EF is involved, they somehow point that i need to inject a check constraint to the field. no one talk about triggers.... if i can inject a trigger using EF it's done – Hassan Faghihi Oct 05 '16 at 05:59
Then try your problem with trigger and test to see if it works. There's no harm in trying, right? – DVT Oct 05 '16 at 13:01

Check Constraint Which Uses A Custom Function

UPDATE

1 Answers1