Home > Debug > Could not drop the primary key constraint ‘constraint name’ because the table has an XML or spatial index.

Could not drop the primary key constraint ‘constraint name’ because the table has an XML or spatial index.

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

Categories: Debug
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: