October 9, 2011 at 6:15 am
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!"October 10, 2011 at 3:04 am
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?
October 10, 2011 at 3:07 am
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)
October 10, 2011 at 3:27 am
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...
October 10, 2011 at 3:30 am
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.
October 10, 2011 at 3:43 am
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'.
October 10, 2011 at 8:17 am
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.
October 10, 2011 at 8:18 am
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!"October 10, 2011 at 8:25 am
I love articles like this. Great job, thank you!
October 10, 2011 at 9:25 am
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 đ
October 10, 2011 at 11:36 am
"I dropped all the foreign key constraints to this table on the handful of tables referencing it. I dropped the index, and then I added the foreign keys references back."
I would like to point out that if you didn't use WITH CHECK CHECK to fully rescan those tables and revalidate the constraint, those FK's are untrusted by SQL Server, and do not provide their full benefits.
October 10, 2011 at 11:54 am
Nadrek (10/10/2011)
"I dropped all the foreign key constraints to this table on the handful of tables referencing it. I dropped the index, and then I added the foreign keys references back."I would like to point out that if you didn't use WITH CHECK CHECK to fully rescan those tables and revalidate the constraint, those FK's are untrusted by SQL Server, and do not provide their full benefits.
I will keep this in mind for the future. Do you have any documentation on this. Luckily for me, the data only lives in the tables for a short while so its all gone by now. Its put in an archive db\table by a rather large archive routine. Hey i didn't design it, i just support it. đ
Jimmy
"I'm still learning the things i thought i knew!"October 11, 2011 at 2:31 am
This does indeed happen on both SQL Server 2008 R2 (RTM) and SQL Server 2008 SP2:
use tempdb;
go
create table t1 (
id int identity primary key,
val varchar(10)
);
create table t2 (
id int identity primary key,
val varchar(10)
);
go
create unique nonclustered index ix_t1 on t1 (id);
go
alter table t2
add constraint fk_t2_t1 foreign key (id) references t1 (id);
go
select
f.name as ConstraintName,
i.name as IndexName,
object_name(i.object_id) as TableName,
i.is_unique,
i.is_primary_key,
i.type_desc,
f.key_index_id
from
sys.foreign_keys f
inner join
sys.indexes i on
i.object_id = f.referenced_object_id and
i.index_id = f.key_index_id
go
drop table t2;
drop table t1;
Actually, Kimberly Tripp has even a Post on it. As far as I can see, this is a very nice feature optimizing the performance of foreign key constraint validation, and not even close to a bug.
October 11, 2011 at 6:08 am
okbangas (10/11/2011)
This does indeed happen on both SQL Server 2008 R2 (RTM) and SQL Server 2008 SP2:
use tempdb;go
create table t1 (
id int identity primary key,
val varchar(10)
);
create table t2 (
id int identity primary key,
val varchar(10)
);
go
create unique nonclustered index ix_t1 on t1 (id);
go
alter table t2
add constraint fk_t2_t1 foreign key (id) references t1 (id);
go
select
f.name as ConstraintName,
i.name as IndexName,
object_name(i.object_id) as TableName,
i.is_unique,
i.is_primary_key,
i.type_desc,
f.key_index_id
from
sys.foreign_keys f
inner join
sys.indexes i on
i.object_id = f.referenced_object_id and
i.index_id = f.key_index_id
go
drop table t2;
drop table t1;
Actually, Kimberly Tripp has even a Post on it. As far as I can see, this is a very nice feature optimizing the performance of foreign key constraint validation, and not even close to a bug.
Can you run my script that is attached on your environment? I would like to see if you get the error that i did on 2005 in your 2008 environment. A guess is that perhaps the association still happens, but you don't get the error when dropped due to some new code. Out of curiosity I will look into it that.
As far as being a feature, I don't like features that cause errors. Perhaps the implementation could be different. Like it should auto-switch to the best index available, and if no index create whatever object is needed to hold everything together. Based on my guess above, maybe that's why there is no error in 2008.
Jimmy
"I'm still learning the things i thought i knew!"October 11, 2011 at 6:14 am
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply