SQL Server allows you to resolve the null value of a duplicate null field (1/2)

Solution 1:
The first idea for this question may be: Is it OK to add a unique key to the caption field? OK, let's follow this thread and create a unique index first.
The code is as follows:
Create unique nonclustered index UN_TEST_TB
On TEST_TB (caption)
Go

Index creation Okay, let's test the effect.
The code is as follows:
Insert into TEST_TB (caption)
VALUES (NULL)
Go
Insert into TEST_TB (caption)
VALUES (NULL)
Go

After running, we receive the following error message:
The following are the referenced contents:
Message 2601, Level 14, State 1, line 1th
You cannot insert a row of duplicate keys in an object ' DBO.TEST_TB ' with a unique index ' UN_TEST_TB '.
The statement was terminated.
So the solution is not going to work.
Solution 2:
Add a constraint so that SQL Server, when inserting data, verifies that there is a value in the existing data that is now being inserted. Since this constraint is not a simple operation, we first create a function and then call the function in the constraint.
To create a validation logic function:
The code is as follows:
Create function [dbo]. [Fn_ck_test_tb_caption] ()
Returns bit
As
Begin
if (Exists (
Select 1
From TEST_TB as a
Where (caption is not null) and exists
(select 1 as Expr1
From TEST_TB
Where (caption is not null) and (caption = A.caption) and (A.testid <> TestID))
))
return 0
Return 1
End
Go

Home 1 2 last page