Check constraint doesn't work in SQL Server 2016

Viewed 267

I have a simple example below

begin tran

CREATE TABLE [dbo].[Filters]
(
    [Id]                    INT NOT NULL IDENTITY,
    [FCode]                 varchar(30) null,
    [FVersion]              varbinary(892) null,

    CONSTRAINT [PK_Filter] PRIMARY KEY CLUSTERED ([Id]),

    CONSTRAINT [CK_Filters_FCode_FVersion]  
        CHECK (([FCode] IS NULL AND [FVersion] IS NULL) 
               OR (LEN([FCode]) > 0 AND DATALENGTH([FVersion]) > 0)), 
)

INSERT INTO [dbo].[Filters] (FCode, FVersion)
VALUES  (NULL, NULL),
        (NULL, 0x6BE348),
        ('ASD', NULL),
        ('ASD', 0x6BE348)

SELECT
    IIF(([FCode] IS NULL AND [FVersion] IS NULL) 
         OR (LEN([FCode]) > 0 AND DATALENGTH([FVersion]) > 0) , 1, 0) AS [check], *
FROM
    [dbo].[filters]

rollback

I expect that the 2nd and 3rd insert statements will cause the constraint violation. However server allows them.

In use the select statement to see that the value of the constraint check is violated for 2nd and 3rd rows. See the result

check   Id  FCode   FVersion
------------------------------
1       1   NULL    NULL
0       2   NULL    0x6BE348
0       3   ASD     NULL
1       4   ASD     0x6BE348

Any ideas?

2 Answers
Related