November 26, 2008 at 12:59 pm
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. :w00t:
Thanks,
Nate
November 26, 2008 at 1:16 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 1:20 pm
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
November 26, 2008 at 1:26 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 1:32 pm
sp_helpindex:
index_name index_description
_dta_index_NAMES_6_1110295015__K1_K4_2nonclustered located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_2nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_6nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_K2nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_K2_3_4_5_6_7nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_K3nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K1_K4nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K2_1nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K2_1_3_4_5_6_7nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K2_K1nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K2_K1_3_4_5_6_7nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K3_K1nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K4nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K4_K1nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K4_K1_2nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K5nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K5_K1nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_8_1110295015__K5_K2nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K1nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K1_K2nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K1_K4nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K2nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K2_K1nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K3nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K4nonclustered, hypothetical located on PRIMARY
_dta_index_NAMES_c_8_1110295015__K4_K1nonclustered, hypothetical located on PRIMARY
CIX_NAMES_NAMEIDclustered, unique located on PRIMARY
PK__NAMES__NAMEIDnonclustered, 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
November 26, 2008 at 2:06 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 2:20 pm
Query 1:
DF_NAMES_UPDATE_DATEDEFAULT_CONSTRAINT
PK__NAMES__NAMEID PRIMARY_KEY_CONSTRAINT
NAMES USER_TABLE
Query 2:
FK__STATUS_NAMES__NAMES STATUS_NAMESNAMES
FK_SOURCE_NAMES_NAMES SOURCE_NAMESNAMES
FK_LIST_NAMES_NAMES LIST_NAMESNAMES
FK__ADDRESS__NAME_1 ADDRESSNAMES
FK__DEMOGRAPH__NAME___2EA5EC27DEMOGRAPHICNAMES
FK_PHONE_NAMES PHONENAMES
FK_hi_category_names_NAMES hi_category_namesNAMES
FK_category_names_NAMES category_namesNAMES
FK1_SOURCE_NAMES_NAMES temp_SOURCE_NAMESNAMES
Thanks,
Nate
November 26, 2008 at 2:28 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 2:31 pm
Okay sounds good. Is there a way to specify what the FKs reference explicitly?
November 26, 2008 at 2:39 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2008 at 8:09 am
Gail,
This did the trick nicely, thank you. However, I'm seeing that my processors are tracking extremely high now. Any idea why this would be happening?
Thanks,
Nate
November 28, 2008 at 12:24 pm
It could be anything. That's almost the database equivalent of asking "Why's my car making a strange noise?"
Probably you have some badly performing queries. Run profiler for a while and see what has high values for CPU and see if you can tune those queries. It may also be excessive compiles as by changing the indexes on that table, all query plans that use that table are now invalid and will have to be recompiled when the queries run. If that's the case, the cpu usage will drop off.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2008 at 12:27 pm
Makes sense, thanks!
July 13, 2011 at 1:09 pm
Gail,
I ran into this. I don't need help resolving, but I did want to know if you found where the behavior is documented. This is my scenario.
Create table called main with a primary key column called MainID with an identity 1,1 and other columns if you wish. Do not set the pk to clustered index but only as unique.
create a table called SubMain with an FK to PK on main.
Later create a unique clustered index on on MainID call it IDX_UQ_CL_MainID.
Just to test go an delete it. To prove you can delete it.
Then add it back. So it will be there for the next step.
Drop your FK in SubMain
add back your FK in SubMain
Try to delete the IDX_UQ_CL_MainID. You will get the error in this article.
It seems that SQL Server uses the best index at the time an FK is created. Best meaning if there is a unique cluster index on the PK field then use it, if not use the next best index.
Do you know of any documentation or setting that controls this. During the ALTER table ADD FK, you can only specify the column not the index that is used so i see no way to control this.
I can see how to detect it and write better schema change scripts, but not how to prevent it in the first place...Assuming you have to add the indexes in the order specified based on a preexisting schema, or if you just inherit the problem.
Ill work on getting the above in code if you have any trouble reproducing.
Jimmy
"I'm still learning the things i thought i knew!"Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply