SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Holy Foreign Keys and Indexes


Holy Foreign Keys and Indexes

Author
Message
imSQrLy
imSQrLy
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 473
Comments posted to this topic are about the item Holy Foreign Keys and Indexes

Jimmy

"I'm still learning the things i thought i knew!"
BredonHill
BredonHill
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 206
A while ago, also in SQL Server 2005, I had a strange report of failure to insert a duplicate. The error message named the wrong index - very misleading. Never got to the bottom of why the wrong index was identified. Wonder if it was related to what you are describing here?
Merrick Chaffer
Merrick Chaffer
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 13
I'm sorry to say that this issue still exists in SQL Server 2008 (10.0.4000.0) SP2

We had it the other day on one of our system test databases that had through a similar scenario to you, with a PK field originally non-clustered, and an IX_BaseTable added as clustered separately.

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64>
(Build 6002: Service Pack 2)
serejka058
serejka058
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 64
I recently had this problem on SQL Server 2005 SP2 and SP3, and in my case the reason was that there were two identical indexes on a table:
-1st was a clustered unique index with column1, column2
-2nd was an index for primary key constraint also with column1, column2
The server raised the error about foreign key reference when I tried to Drop the 1st index. So the only way I found to trick it was to delete foreign key references and then recreate them...
Merrick Chaffer
Merrick Chaffer
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 13
In SQL Server 2008 SP2 the simple fix we used in the end was to add the DROP_EXISTING = ON option to the CREATE index clause of the new index we were adding...

-- START: [IX_BaseTable] recreation
-- NOTE: In order to recreate the [IX_BaseTable] index, it is necessary to utilise the
-- DROP_EXISTING = ON option in the create clause, otherwise it complains of
-- existing foreign key constraints that make use of it, and you have a complaint saying...
-- "An explicit DROP INDEX is not allowed on index 'dbo.BaseTable.IX_BaseTable'. It is being used for FOREIGN KEY constraint enforcement."
CREATE UNIQUE NONCLUSTERED INDEX [IX_BaseTable] ON [dbo].[BaseTable]
(
[ID] ASC
)
INCLUDE ( [Column1],
[Column2],
[Column3]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This way we no longer received the DROP INDEX not allowed error message.
serejka058
serejka058
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 64
In my case I used DROP_EXISTING to convert 2nd (primary key index) from nonclustered into clustered. So the whole sequence was:
1. Delete all foreign key references (ALTER TABLE Table1 DROP CONSTRAINT FK_Table1_Table2)
2. Drop 1st clustered index (DROP INDEX Table2.IX_Clustered_Column1_Column2)
3. Convert 2nd nonclustered index into clustered with Drop_Existing (CREATE UNIQUE CLUSTERED INDEX IX_PK_Column1_Column2 ON Table2 WITH DROP_EXISTING)
4. Create foreign key references again (ALTER TABLE Table1 ADD CONSTRAINT ...)

The problem was that foreign keys were referencing both indexes, I remember that I tried to drop 2nd PK index, but the server raised the same error with 'foreign key reference'.
karl 93387
karl 93387
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 155
Although I haven't done it, I thought that a foreign key constraint isn't limited to referencing a primary key in another table but also any column with a unique constraint.
imSQrLy
imSQrLy
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 473
serejka058 (10/10/2011)
I recently had this problem on SQL Server 2005 SP2 and SP3, and in my case the reason was that there were two identical indexes on a table:
-1st was a clustered unique index with column1, column2
-2nd was an index for primary key constraint also with column1, column2
The server raised the error about foreign key reference when I tried to Drop the 1st index. So the only way I found to trick it was to delete foreign key references and then recreate them...


Good to know. The script I attached reproduced the issue on 2005 but not 2008, perhaps there is some other way for this issue to happen.

Jimmy

"I'm still learning the things i thought i knew!"
Mike Good
Mike Good
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1231 Visits: 1038
I love articles like this. Great job, thank you!



ShawnTherrien
ShawnTherrien
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 765
I've had this exact issue come up on deployments.

Drop FKs, drop Indexes, recreate FKs.

It makes sense that this would use the clustered index for the FK, though I can't say I put forth the effort into finding out why it was happening. Then again I wasn't awoken at 2am ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search