Validation constraint does not work in SQL Server 2016
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 the 2nd and 3rd insert statements to cause a constraint violation. However, the server allows them.
Use a select statement to see that the constraint check value is violated for the 2nd and 3rd rows. See Result
check Id FCode FVersion
------------------------------
1 1 NULL NULL
0 2 NULL 0x6BE348
0 3 ASD NULL
1 4 ASD 0x6BE348
Any ideas?
Check limits only at final value false
.
Expressions that you expect to violate the constraint are evaluated as UNKNOWN
.
You can see this with
SELECT CASE
WHEN( ( [FCode] IS NULL
AND [FVersion] IS NULL )
OR ( LEN([FCode]) > 0
AND DATALENGTH([FVersion]) > 0 ) ) THEN 'True'
WHEN NOT ( ( [FCode] IS NULL
AND [FVersion] IS NULL )
OR ( LEN([FCode]) > 0
AND DATALENGTH([FVersion]) > 0 ) ) THEN 'False'
ELSE 'Unknown'
END AS [check],
LEN([FCode]) AS LenFCode,
DATALENGTH([FVersion]) AS DataLengthFVersion,
*
FROM [dbo].[filters]
What returns
+---------+----------+--------------------+----+-------+----------+
| check | LenFCode | DataLengthFVersion | Id | FCode | FVersion |
+---------+----------+--------------------+----+-------+----------+
| True | NULL | NULL | 1 | NULL | NULL |
| Unknown | NULL | 3 | 2 | NULL | 0x6BE348 |
| Unknown | 3 | NULL | 3 | ASD | NULL |
| True | 3 | 3 | 4 | ASD | 0x6BE348 |
+---------+----------+--------------------+----+-------+----------+
The reason they are UNKNOWN
, is that LEN
they DATALENGTH
return NULL
on transmissionNULL
Martin Smith has already included the reason why SQL Server allows given values without breaking the check constraint.
You can change your check constraint in the following way to make it work. Demo
CONSTRAINT [CK_Filters_FCode_FVersion]
CHECK (([FCode] IS NULL AND [FVersion] IS NULL)
OR (LEN(ISNULL([FCode],'')) > 0
AND ISNULL(DATALENGTH([FVersion]),-1) > 0))