September 23, 2015 at 7:38 am
Hi all,
Lately, I faced the below strange situation in SQL Server 2008R2:
The parent table has the column OrderID which is the primary key. There is a unique non-clustered index in the same table with the same column and some included ones. A child table references the parent table on the OrderID column.
Unfortunatelly, in the sys.foreign_keys table the index that has been used to acomplish the referential integrity is the unique non-clustered one instead of the primary key (which is the clustered index as well).
This is causing issues in some maintenance tasks where we need to disable all the indexes of the table apart from the clustered index. Disabling these indexes leeds to disabling the not properly configured foreign key as well.
Has anyone faced something similar? Is there any solution? Maybe dropping the index create the constraint again and then recreate the index is a workarround but I wish to have something more elegant and future proof.
Thanks in advance.
September 23, 2015 at 8:34 am
acheras (9/23/2015)
Hi all,Lately, I faced the below strange situation in SQL Server 2008R2:
The parent table has the column OrderID which is the primary key. There is a unique non-clustered index in the same table with the same column and some included ones. A child table references the parent table on the OrderID column.
Unfortunatelly, in the sys.foreign_keys table the index that has been used to acomplish the referential integrity is the unique non-clustered one instead of the primary key (which is the clustered index as well).
This is causing issues in some maintenance tasks where we need to disable all the indexes of the table apart from the clustered index. Disabling these indexes leeds to disabling the not properly configured foreign key as well.
Has anyone faced something similar? Is there any solution? Maybe dropping the index create the constraint again and then recreate the index is a workarround but I wish to have something more elegant and future proof.
Thanks in advance.
After testing this out, I found a simple solution for you. You will need to drop the FK, disable the unique index, recreate the FK and then rebuild the index (which enables it). This process only needs to be done once to get the FK connected to the desired index, but this process should be performed during a maintenance operation so that you can ensure that you won't possibly get errant data while the FK is not present.
Sample that demonstrates this:
USE tempdb;
IF OBJECT_ID('dbo.ChildTable') IS NOT NULL DROP TABLE dbo.ChildTable;
IF OBJECT_ID('dbo.ParentTable') IS NOT NULL DROP TABLE dbo.ParentTable;
CREATE TABLE dbo.ParentTable
(
RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,
Col1 INTEGER,
Col2 INTEGER,
Col3 INTEGER,
Col4 INTEGER,
Col5 INTEGER
);
CREATE UNIQUE INDEX IX_RowID_Include ON dbo.ParentTable (RowID) INCLUDE (Col1, Col2, Col3, Col4, Col5);
CREATE TABLE dbo.ChildTable
(
RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,
ParentID INTEGER NOT NULL CONSTRAINT FK_ChildTable_ParentTable REFERENCES dbo.ParentTable (RowID)
);
GO
SELECT name, key_index_id
FROM sys.foreign_keys;
ALTER TABLE dbo.ChildTable DROP CONSTRAINT FK_ChildTable_ParentTable;
ALTER INDEX IX_RowID_Include ON dbo.ParentTable DISABLE;
SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id), name, is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.ParentTable');
ALTER TABLE dbo.ChildTable
ADD CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY (RowID) REFERENCES dbo.ParentTable (RowID);
ALTER INDEX IX_RowID_Include ON dbo.ParentTable REBUILD;
SELECT name, key_index_id
FROM sys.foreign_keys;
SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id), name, is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.ParentTable');
IF OBJECT_ID('dbo.ChildTable') IS NOT NULL DROP TABLE dbo.ChildTable;
IF OBJECT_ID('dbo.ParentTable') IS NOT NULL DROP TABLE dbo.ParentTable;
Edit: of course, this isn't much different from your proposed solution of dropping the index, but I don't see a way to specify which index the FK should use.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 23, 2015 at 8:56 am
Thanks for your quick reply.
Howevever, I still think that this should not happen and the clustered index should have had a priority to be used or you should have a way to define it.
Developers will never consider to check what index has been used and you cannot blame them for that. And definitely you need a future proof solution.
September 23, 2015 at 12:03 pm
I'm inquiring as to whether there are any other options that could be utilized to handle this, but I can't think of any.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 23, 2015 at 12:30 pm
SQL's priority is to use the index with fewer pages, because of less I/O which equal less overhead. Thus, to link FKs to the clustered index, you need to create the FK before the nonclustered index(es) exist.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply