SQL Server allows duplicate null fields not empty field values unique

Today, my classmate asked me a question, I think it is very interesting, now recorded down to discuss. The problem is: in a table, there is a field that is allowed to be empty, and null can be repeated, but a value that is not NULL needs to be unique.

The table structure is created like the following code

The following are the referenced contents: CREATE TABLE TEST_TB ( TestID int NOT null identity (1,1) primary key, Caption nvarchar (MB) null ); Go

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 following are the referenced contents: CREATE UNIQUE nonclustered INDEX UN_TEST_TB On TEST_TB (Caption) Go

Index creation Okay, let's test the effect.

The following are the referenced contents: 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

The following is the referenced content: create function &NBSP;[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&NBSP;1&NBSP;AS&NBSP;EXPR1         FROM  TEST_TB         WHERE  (caption is not null)  AND  (caption = a.caption)  AND  (A.testid <> testid))       ))         return 0    &NBSP;&NBSP     return 1 End Go

To reference a function in a constraint:

The following are the referenced contents: ALTER TABLE TEST_TB ADD CONSTRAINT ck_test_tb_caption CHECK (dbo.fn_ck_test_tb_caption () = 1) Go

Now to test the effect. To test the null value first

The following are the referenced contents: INSERT into TEST_TB (Caption) VALUES (NULL) Go INSERT into TEST_TB (Caption) VALUES (NULL) Go SELECT * from TEST_TB Go

Can run successfully, and there is also a case of multiple behavior null. Now let's test for an empty insert.

The following are the referenced contents: 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 field in the table also has ' AAA ' and ' BBB ', which is exactly what we want.

So solution 2 is correct. But is it too tedious to write such a long piece of stuff for such a small function? Let's look at the following solutions.

Solution 3: (For SQL Server 2008 only)

SQL An elegant solution in Server 2008 is to filter the indexes. A filtered index is an optimized nonclustered index, especially for queries that cover selecting data from a well-defined data set. The filter index uses a filter verb to index some of the rows in the table. With the filtered index, we only need to write a statement to achieve the above effect.

The following are the referenced contents: CREATE UNIQUE nonclustered INDEX UN_TEST_TB On TEST_TB (Caption) WHERE Caption is not null Go

And then use some of the above test statements to test, we will find that is fully meet our requirements. The only drawback to this scenario is that the statement has only SQL Server 2008 support. I wonder if you have any elegant and applicable to each version of the SQL server solution, I hope you can enlighten me. (Source: Blog Park )