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 123»»»

Holy Foreign Keys and Indexes Expand / Collapse
Author
Message
Posted Sunday, October 9, 2011 6:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:20 PM
Points: 133, Visits: 472
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!"
Post #1187628
Posted Monday, October 10, 2011 3:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 5:06 AM
Points: 19, 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?
Post #1187768
Posted Monday, October 10, 2011 3:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 12:10 AM
Points: 2, 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)
Post #1187769
Posted Monday, October 10, 2011 3:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:38 AM
Points: 2, Visits: 60
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...
Post #1187779
Posted Monday, October 10, 2011 3:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 12:10 AM
Points: 2, 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.
Post #1187781
Posted Monday, October 10, 2011 3:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:38 AM
Points: 2, Visits: 60
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'.
Post #1187786
Posted Monday, October 10, 2011 8:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:18 PM
Points: 103, Visits: 122
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.
Post #1187940
Posted Monday, October 10, 2011 8:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:20 PM
Points: 133, Visits: 472
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!"
Post #1187941
Posted Monday, October 10, 2011 8:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:30 AM
Points: 317, Visits: 814
I love articles like this. Great job, thank you!


Post #1187948
Posted Monday, October 10, 2011 9:25 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 8:23 PM
Points: 73, Visits: 761
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
Post #1187977
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse