Holy Foreign Keys and Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by James DiMauro » Holy Foreign Keys and Indexes

22 posts, Page 1 of 3

1 2 3 »»»

Holy Foreign Keys and Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

imSQrLy

Posted Sunday, October 09, 2011 6:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:01 AM
Points: 133, Visits: 436

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

BredonHill

Posted Monday, October 10, 2011 3:04 AM

Grasshopper

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

Merrick Chaffer

Posted Monday, October 10, 2011 3:07 AM

Forum 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

serejka058

Posted Monday, October 10, 2011 3:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:02 PM
Points: 2, Visits: 53

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

Merrick Chaffer

Posted Monday, October 10, 2011 3:30 AM

Forum 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

serejka058

Posted Monday, October 10, 2011 3:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:02 PM
Points: 2, Visits: 53

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

karl 93387

Posted Monday, October 10, 2011 8:17 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:37 AM
Points: 58, Visits: 67

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

imSQrLy

Posted Monday, October 10, 2011 8:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:01 AM
Points: 133, Visits: 436

serejka058 (10/10/2011)

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

Mike Good

Posted Monday, October 10, 2011 8:25 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:59 AM
Points: 264, Visits: 633

I love articles like this. Great job, thank you!

Post #1187948

ShawnTherrien

Posted Monday, October 10, 2011 9:25 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 PM
Points: 61, Visits: 616

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 Wink

Post #1187977

« Prev Topic | Next Topic »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions