|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Tuesday, February 09, 2010 12:06 PM
Points: 147,
Visits: 321
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 17,125,
Visits: 12,225
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, February 09, 2010 12:06 PM
Points: 147,
Visits: 321
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 17,125,
Visits: 12,225
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, February 09, 2010 12:06 PM
Points: 147,
Visits: 321
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 17,125,
Visits: 12,225
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, February 09, 2010 12:06 PM
Points: 147,
Visits: 321
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 17,125,
Visits: 12,225
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, February 09, 2010 12:06 PM
Points: 147,
Visits: 321
|
|
| Okay sounds good. Is there a way to specify what the FKs reference explicitly?
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 17,125,
Visits: 12,225
|
|
No. Just try not to have multiple constraints/unique indexes on the column(s) referenced by the key.
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
