Error Message

Msg 3734, Level 16, State 1, Line 187
Could not drop the primary key constraint ‘PK_Person_BusinessEntityID’ because the table has an XML or spatial index.
Msg 3727, Level 16, State 0, Line 187
Could not drop constraint. See previous errors.

Description:
This error message appears when you try to remove a PRIMARY KEY constraint from a table which has an XML index.

 

Troubleshooting

—Error Statement

ALTER TABLE [Person].[Person] DROP CONSTRAINT [PK_Person_BusinessEntityID]

–Troubleshooting
go
select kc.name ConstraintName,object_name(kc.parent_object_id) tablename,kc.type,si.name indexname
from sys.key_constraints kc inner join sys.indexes si on kc.parent_object_id=si.object_id where
kc.name=’PK_Person_BusinessEntityID’ and si.type_desc=’XML’

–//Drop the indexes first then the constraint

Msg 3734, Level 16, State 1, Line 5
Could not drop the primary key constraint ‘PK_Person_BusinessEntityID’ because the table has an XML or spatial index.
Msg 3727, Level 16, State 0, Line 5
Could not drop constraint. See previous errors.
ConstraintName                 tablename    type    indexname
PK_Person_BusinessEntityID    Person    PK    PXML_Person_AddContact
PK_Person_BusinessEntityID    Person    PK    PXML_Person_Demographics
PK_Person_BusinessEntityID    Person    PK    XMLPATH_Person_Demographics
PK_Person_BusinessEntityID    Person    PK    XMLPROPERTY_Person_Demographics
PK_Person_BusinessEntityID    Person    PK    XMLVALUE_Person_Demographics

Resolution

Drop the XML Index First and then drop the Primary Key Constraint