Today, I was asked a question. I think it is quite interesting. now I will record it and discuss it with you. The problem is: in a table, there is a field that can be blank. null can be repeated, but the value that is not empty must be unique.
Today, I was asked a question. I think it is quite interesting. now I will record it and discuss it with you. The problem is: in a table, there is a field that can be blank. null can be repeated, but the value that is not empty must be unique.
The following code creates a table structure:
The code is as follows:
Create table test_tb
(
TestId int not null identity (1, 1) primary key,
Caption nvarchar (100) null
);
GO
Solution 1:
For this question, the first thought may be: can I add a unique key to the Caption field? Well, let's proceed with this idea and create a unique index first.
The code is as follows:
Create unique nonclustered index un_test_tb
ON test_tb (Caption)
GO
After the index is created, 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 will receive the following error message:
Reference content is as follows:
Message 2601, level 14, status 1, 1st rows
Rows with duplicate keys cannot be inserted in the 'dbo. test_tb 'object with the unique index 'un _ test_tb.
The statement has been terminated.
Therefore, this solution does not work.
Solution 2:
Add constraints so that SQL Server can first verify whether the value to be inserted exists in the existing data when inserting data. Because this constraint is not a simple operation, we first create a function and then call it in the constraint.
Create a verification 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
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
Reference a function in a constraint:
The code is as follows:
Alter table test_tb
Add constraint CK_test_tb_Caption CHECK (dbo. fn_CK_test_tb_Caption () = 1)
GO
Now let's test the effect. Test the NULL value first.
The code is as follows:
Insert into test_tb (Caption)
VALUES (null)
GO
Insert into test_tb (Caption)
VALUES (null)
GO
SELECT * FROM test_tb
GO
It can be run successfully, and multiple NULL behaviors occur. Now let's test the insertion condition that is not empty.
The code is as follows:
Insert into test_tb (Caption)
VALUES (N 'AAA ')
GO
Insert into test_tb (Caption)
VALUES (N 'BBB ')
GO
Insert into test_tb (Caption)
VALUES (N 'BBB ')
GO
SELECT * FROM test_tb
GO
The result is an error in the third statement. The Caption fields in the table also have 'AAA' and 'BBB', which is exactly the result we want.
So solution 2 is correct. But is it too cumbersome to write such a long paragraph for such a small function? Let's look at the solution below.
Solution 3: (only applicable to SQL Server 2008)
SQL Server 2008 has an elegant solution, that is, filtering indexes. A filtered index is an optimized non-clustered index. it is especially suitable for queries that cover the selection of data from a well-defined data subset. Filter indexes use filter predicates to index some rows in the table. With the screening index, we only need to write a statement to achieve the above effect.
The code is as follows:
Create unique nonclustered index un_test_tb
ON test_tb (Caption)
WHERE Caption is not null
GO
If we use some of the above test statements for testing, we will find that they fully meet our requirements. The only drawback of this solution is that this statement is only supported by SQL Server 2008. I don't know if you have any elegant and suitable solutions for SQL Server of various versions. (Source: Blog)
