How to ensure the uniqueness of the data in the table?

Question

SQL

Good afternoon!
I create a table:
CREATE TABLE dic
( dic_id BIGINT NOT NULL IDENTITY,
dic_value VARCHAR(MAX) NOT NULL )
CREATE UNIQUE INDEX PK_dic_id ON dic (dic_id) INCLUDE (dic_value)
GO
But, contrary to logic, there are many duplicates (not unique values) in the dic_value column ).
What am I doing wrong?

Answer 1

CREATE UNIQUE INDEX PK_dic_id ON dic (dic_id) INCLUDE (dic_value)

With this command, you create a unique index on the dic_id field. And uniqueness is provided only on this field. The dic_value field is included in the index additionally, in order to perform faster SELECT, in which only these fields participate.
Creates a unique index with two fields, that is, uniqueness is provided for a pair of fields in the aggregate. since your dic_id is unique, uniqueness is provided regardless of the value of dic_value.
Correctly create a separate unique index on dic_value:

CREATE UNIQUE INDEX IX_dic_value ON dic (dic_value)

Answer 2

The column identifiers property does not guarantee the following.
Value uniqueness - Value uniqueness should be enforced using a PRIMARY KEY or UNIQUE constraint, or a UNIQUE index .