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