SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by James DiMauro / Holy Foreign Keys and Indexes / Latest Posts

RE: Holy Foreign Keys and Indexes

Dan Guzman-481633 — Fri, 21 Oct 2011 19:13:58 GMT

SQL Server allows a foreign key to reference any column(s) that is guaranteed to be unique. The Books Online states "FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table." When the foreign key is created, SQL Server will bind the foreign key to the index. There is no documented behavior on which constraint or index will be chosen when multiple candidate unique indexes exist. The rules SQL Server uses are apparently different between SQL 2005 and SQL 2008+. I don't think it's correct to classify a change in behavior as a defect unless it's contrary to documentation.With some experimentation, I learned that SQL 2005 chooses the clustered index on a table and the first non-clustered (lowest index_id) on a heap. However, SQL 2008 and SQL 2008 R2 choose the first non-clustered index on both tables and heaps; the clustered index is used only if no candidate non-clustered indexes exist.

RE: Holy Foreign Keys and Indexes

alen teplitsky — Thu, 13 Oct 2011 09:45:58 GMT

if i understand this correctly there are two unique indexes that were created on the same column or columns. one clustered unique index and on PK unclustered. any reason for that? why not just use one or the other. if this is the case it doesn't sound like a bug to me

RE: Holy Foreign Keys and Indexes

mishaluba — Tue, 11 Oct 2011 19:30:13 GMT

Thank you for sharing this obscure issue. I hope it saves me time someday.

RE: Holy Foreign Keys and Indexes

rstelma — Tue, 11 Oct 2011 16:33:30 GMT

Hi Jack,Damn database gremlins.Now when I run the query on the Master I get no results and 156 on the DB where the tables exist and where I expect to see the Foreign Keys. I swear I ran that on the Master and kept switching back and forth.Anyway... thank you. Very much appreciate your reply.Richard

RE: Holy Foreign Keys and Indexes

Jack Corbett — Tue, 11 Oct 2011 14:12:28 GMT

[quote][b]rstelma (10/11/2011)[/b][hr]Hi,I hope you guys can help me get something straightened out here. I ran this query on the Master DB:[code="sql"]select f.name , i.name , 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 join sys.indexes i on i.object_id = f.referenced_object_id and i.index_id = f.key_index_id[/code]With these results.name name tablename is_unique is_primary_key type_desc key_index_idFK_ProjectWorkOrders_Projects PK_Projects Projects 1 1 CLUSTERED 1FK_ProjectFacilities_Projects PK_Projects Projects 1 1 CLUSTERED 1FK_WorkOrderTypeEmployees_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1FK_ProjectWorkOrders_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1FK_ProjectWorkOrderEmployees_ProjectWorkOrders PK_ProjectWorkOrders ProjectWorkOrders 1 1 CLUSTERED 1I also ran this query on the DB where these tables are and got 156 results which is pretty much what I expected.Why are only some of the indexes showing up in the Master DB? These are the most recent table additions and foreign keys. Very sorry if this is too far off topic. If so I will repost as a new thread.Thank you,Richard[/quote]I wouldn't expect to see any results for user tables when running that query. Those DMV's are scoped to the database that you are in. If there are FK's showing in master that means the tables, indexes, and foreign keys were created in master instead of the user database or in addition to the user database.

RE: Holy Foreign Keys and Indexes

rstelma — Tue, 11 Oct 2011 12:36:23 GMT

Hi,I hope you guys can help me get something straightened out here. I ran this query on the Master DB:[code="sql"]select f.name , i.name , 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 join sys.indexes i on i.object_id = f.referenced_object_id and i.index_id = f.key_index_id[/code]With these results.name name tablename is_unique is_primary_key type_desc key_index_idFK_ProjectWorkOrders_Projects PK_Projects Projects 1 1 CLUSTERED 1FK_ProjectFacilities_Projects PK_Projects Projects 1 1 CLUSTERED 1FK_WorkOrderTypeEmployees_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1FK_ProjectWorkOrders_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1FK_ProjectWorkOrderEmployees_ProjectWorkOrders PK_ProjectWorkOrders ProjectWorkOrders 1 1 CLUSTERED 1I also ran this query on the DB where these tables are and got 156 results which is pretty much what I expected.Why are only some of the indexes showing up in the Master DB? These are the most recent table additions and foreign keys. Very sorry if this is too far off topic. If so I will repost as a new thread.Thank you,Richard

RE: Holy Foreign Keys and Indexes

Jack Corbett — Tue, 11 Oct 2011 06:46:47 GMT

I don't think I'd call this a bug. SQL Server can use the Primary Key or a Unique Constraint/index to as the reference column(s) for a foreign key. In this case (or these cases for those who have experienced it), SQL Server is probably using a unique clustered index to enforce a foreign key because it believes that it will improve performance. I'd have to assume that the lead column in the unique clustered index is the same column as the primary key. I'd be checking to see if both indexes are needed and perhaps make the unique clustered index the PK.

RE: Holy Foreign Keys and Indexes

okbangas — Tue, 11 Oct 2011 06:14:40 GMT

I do get the same error message, yes:[quote]Msg 3723, Level 16, State 6, Line 2An explicit DROP INDEX is not allowed on index 't1.ix_t1'. It is being used for FOREIGN KEY constraint enforcement.[/quote]

RE: Holy Foreign Keys and Indexes

imSQrLy — Tue, 11 Oct 2011 06:08:48 GMT

[quote][b]okbangas (10/11/2011)[/b][hr]This does indeed happen on both SQL Server 2008 R2 (RTM) and SQL Server 2008 SP2:[code]use tempdb;gocreate table t1 ( id int identity primary key, val varchar(10));create table t2 ( id int identity primary key, val varchar(10));gocreate unique nonclustered index ix_t1 on t1 (id);goalter table t2 add constraint fk_t2_t1 foreign key (id) references t1 (id);goselect 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_idgo drop table t2;drop table t1;[/code]Actually, Kimberly Tripp has even a [url=http://sqlskills.com/BLOGS/KIMBERLY/post/Foreign-Keys-can-reference-UNIQUE-indexes-(without-constraints).aspx]Post[/url] 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.[/quote]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.

RE: Holy Foreign Keys and Indexes

okbangas — Tue, 11 Oct 2011 02:31:44 GMT

This does indeed happen on both SQL Server 2008 R2 (RTM) and SQL Server 2008 SP2:[code]use tempdb;gocreate table t1 ( id int identity primary key, val varchar(10));create table t2 ( id int identity primary key, val varchar(10));gocreate unique nonclustered index ix_t1 on t1 (id);goalter table t2 add constraint fk_t2_t1 foreign key (id) references t1 (id);goselect 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_idgo drop table t2;drop table t1;[/code]Actually, Kimberly Tripp has even a [url=http://sqlskills.com/BLOGS/KIMBERLY/post/Foreign-Keys-can-reference-UNIQUE-indexes-(without-constraints).aspx]Post[/url] 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.

RE: Holy Foreign Keys and Indexes

imSQrLy — Mon, 10 Oct 2011 11:54:47 GMT

[quote][b]Nadrek (10/10/2011)[/b][hr]"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.[/quote]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. :)

RE: Holy Foreign Keys and Indexes

Nadrek — Mon, 10 Oct 2011 11:36:15 GMT

"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.

RE: Holy Foreign Keys and Indexes

ShawnTherrien — Mon, 10 Oct 2011 09:25:56 GMT

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 ;-)

RE: Holy Foreign Keys and Indexes

Mike Good — Mon, 10 Oct 2011 08:25:23 GMT

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

RE: Holy Foreign Keys and Indexes

imSQrLy — Mon, 10 Oct 2011 08:18:13 GMT

[quote][b]serejka058 (10/10/2011)[/b][hr]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, column2The 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...[/quote]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.

RE: Holy Foreign Keys and Indexes

karl 93387 — Mon, 10 Oct 2011 08:17:02 GMT

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.

RE: Holy Foreign Keys and Indexes

serejka058 — Mon, 10 Oct 2011 03:43:22 GMT

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'.

RE: Holy Foreign Keys and Indexes

Merrick Chaffer — Mon, 10 Oct 2011 03:30:16 GMT

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]GOThis way we no longer received the DROP INDEX not allowed error message.

RE: Holy Foreign Keys and Indexes

serejka058 — Mon, 10 Oct 2011 03:27:13 GMT

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, column2The 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...

RE: Holy Foreign Keys and Indexes

Merrick Chaffer — Mon, 10 Oct 2011 03:07:25 GMT

I'm sorry to say that this issue still exists in SQL Server 2008 (10.0.4000.0) SP2We 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)

RE: Holy Foreign Keys and Indexes

BredonHill — Mon, 10 Oct 2011 03:04:03 GMT

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?

Holy Foreign Keys and Indexes

imSQrLy — Sun, 09 Oct 2011 06:15:48 GMT

Comments posted to this topic are about the item [B]Holy Foreign Keys and Indexes[/B]