Error Message:
Msg 1979, Level 16, State 1, Line 2
Cannot use index option ignore_dup_key to alter index ‘%.*ls’ as it enforces a primary or unique constraint.
Severity level:
16.
Description:
This error message appears when you try to alter an index to allow duplicate keys, but this index is used to enforce a PRIMARY KEY or UNIQUE constraint, which does not allow duplicates by definition.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Error of the Severity level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be run this way. You can not alter an index that is used by SQL Server to enforce uniqueness to allow duplicates.
Versions:
All versions of SQL Server.
Example(s):
IF OBJECT_ID(‘dbo.t’) IS NOT NULL
DROP TABLE dbo.t;
GO
CREATE TABLE dbo.t
(
c1 int
CONSTRAINT PK_t PRIMARY KEY NONCLUSTERED
);
GO
ALTER INDEX PK_t ON dbo.t
SET
(IGNORE_DUP_KEY = ON);
Remarks:
In the above example we try to alter the index PK_t to allow duplicate keys. Since this index is used to enforce a PRIMARY KEY constraint the error is raised.