The table structure is created like the following code
Copy Code code as follows:
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.
Copy Code code as follows:
CREATE UNIQUE nonclustered INDEX UN_TEST_TB
On TEST_TB (Caption)
Go
Index creation Okay, let's test the effect.
Copy Code code 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:
Copy Code code 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
To reference a function in a constraint:
Copy Code code as follows:
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
Copy Code code as follows:
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.
Copy Code code 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 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)
An elegant solution in SQL 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.
Copy Code code as follows:
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)
