Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can't Delete Index b/c of Constraint Expand / Collapse
Author
Message
Posted Wednesday, November 26, 2008 12:59 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:47 AM
Points: 205, Visits: 643
Hi All,

I've got a table that has a primary key, an index on that primary key, and an additional clustered index. I need to drop the clustered index but can't because it's enforcing foreign key constraints. How can I force SQL server 2005 to use the primary key for that? I can't seem to find anything on the net. Any help is GREATLY appreciated.

Thanks,
Nate
Post #609397
Posted Wednesday, November 26, 2008 1:16 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
The primary key is enforced by an index and, I'd guess from your description that the primary key is the clustered index (use sp_helpindex to check). You can't drop the index that enforces a constraint without dropping the constraint itself and, with the primary key, that means dropping the foreign keys as well.

You should be able to drop the nonclustered index without problems. If you get problems, then post the output of helpindex on that table.

What are you trying to achieve?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #609405
Posted Wednesday, November 26, 2008 1:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:47 AM
Points: 205, Visits: 643
Gail, the quick response is much appreciated. Also, nice presentation at PASS 2008.

The primary key is actually non-clustered, and I'm trying to delete a clustered index that's on the same column as the PK. I tried dropping the foreign key constraints which I found using:

select *
from sys.sysobjects
where xtype = 'F'

But I apparently didn't get them all because I was still not able to drop either index. FKs aren't enforced across databases are they?

I created the clustered index for testing purposes but found that it causes the CPU usage to soar, so I'm trying to drop it.

Hope that helps.

Thanks again,
Nate
Post #609409
Posted Wednesday, November 26, 2008 1:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
nate (11/26/2008)
Gail, the quick response is much appreciated. Also, nice presentation at PASS 2008.


Oh, thanks.


The primary key is actually non-clustered, and I'm trying to delete a clustered index that's on the same column as the PK.


Foreign keys are enforced from the pk or from a unique constraint, not from plain indexes.

What's the output of sp_helpindex on that table?
What's the exact error you get if you try to run a drop index on that clustered index




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #609412
Posted Wednesday, November 26, 2008 1:32 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:47 AM
Points: 205, Visits: 643
sp_helpindex:

index_name index_description
_dta_index_NAMES_6_1110295015__K1_K4_2 nonclustered located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_2 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_6 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_K2 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_K2_3_4_5_6_7 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_K3 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_K4 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K2_1 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K2_1_3_4_5_6_7 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K2_K1 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K2_K1_3_4_5_6_7 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K3_K1 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K4 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K4_K1 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K4_K1_2 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K5 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K5_K1 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K5_K2 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K1 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K1_K2 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K1_K4 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K2 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K2_K1 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K3 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K4 nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K4_K1 nonclustered, hypothetical located on PRIMARY
CIX_NAMES_NAMEID clustered, unique located on PRIMARY
PK__NAMES__NAMEID nonclustered, unique, primary key located on PRIMARY

error:
Msg 3723, Level 16, State 6, Line 3
An explicit DROP INDEX is not allowed on index 'dbo.NAMES.CIX_NAMES_NAMEID'. It is being used for FOREIGN KEY constraint enforcement.

Thanks,
Nate
Post #609414
Posted Wednesday, November 26, 2008 2:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
Hmmm. Odd. Did you create that through the management studio GUI? If so, what options did you select?

What do the following return?

select name, type_desc from sys.objects
where object_id = OBJECT_ID('NAMES') OR parent_object_id = OBJECT_ID('NAMES')

select name, OBJECT_NAME(parent_object_id), OBJECT_NAME(referenced_object_id) from sys.foreign_keys
where referenced_object_id = OBJECT_ID('NAMES')




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #609432
Posted Wednesday, November 26, 2008 2:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:47 AM
Points: 205, Visits: 643
Query 1:

DF_NAMES_UPDATE_DATE DEFAULT_CONSTRAINT
PK__NAMES__NAMEID PRIMARY_KEY_CONSTRAINT
NAMES USER_TABLE

Query 2:
FK__STATUS_NAMES__NAMES STATUS_NAMES NAMES
FK_SOURCE_NAMES_NAMES SOURCE_NAMES NAMES
FK_LIST_NAMES_NAMES LIST_NAMES NAMES
FK__ADDRESS__NAME_1 ADDRESS NAMES
FK__DEMOGRAPH__NAME___2EA5EC27 DEMOGRAPHIC NAMES
FK_PHONE_NAMES PHONE NAMES
FK_hi_category_names_NAMES hi_category_names NAMES
FK_category_names_NAMES category_names NAMES
FK1_SOURCE_NAMES_NAMES temp_SOURCE_NAMES NAMES

Thanks,
Nate
Post #609444
Posted Wednesday, November 26, 2008 2:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
All of the foreign keys that the query return reference the Names table. Try dropping them.

It's odd, they should be referencing the pk, not the unique index.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #609453
Posted Wednesday, November 26, 2008 2:31 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:47 AM
Points: 205, Visits: 643
Okay sounds good. Is there a way to specify what the FKs reference explicitly?
Post #609458
Posted Wednesday, November 26, 2008 2:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
No. Just try not to have multiple constraints/unique indexes on the column(s) referenced by the key.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #609465
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse