CODEWITHSUNDEEP
sql-servert-sqlduplicates
igelr
igelr

Reputation: 1751

ignore_dup_key = on in SQL Server does not ignore duplicates in a table

I want to create this index on the 'age' column where 2 NULLs exist 

create unique nonclustered index idx_ncl_forindexes_age on forindexes (age)
with(drop_existing=on, **ignore_dup_key=on**)

which means values are not unique. But I want to ignore that fact and create unique index for future records. While writing 'ignore_dup_key=on' I still get an error

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.forindexes' and the index name 'idx_ncl_forindexes_age'. The duplicate key value is (). The statement has been terminated.

Why while using ignore_dup_key I still get this error?

Thanks for your time.

Upvotes: 0

Views: 940

Answers (1)

HABO
HABO

Reputation: 15852

From the documentation for create index:

Arguments

UNIQUE

Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique.

The Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message.

Upvotes: 1

Related Questions