Cannot use index option ignore_dup_key to alter index ‘%.*ls’ as it enforces a primary or unique constraint.

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.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |