October 6, 2009 at 3:49 pm
Hi,
This URL as caused me to think more about my indexs on a database I have to support.
I notice that on certain db tables there exists both a foreign key and index covering the same fileds, which caused me to think that the index is redundnant.
eg:
CREATE NONCLUSTERED INDEX [DEBTOR_TO_PATIENT_FK] ON [dbo].[SB_PATIENT_REGISTER]
(
[SB_DEBTOR_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
vs:
ALTER TABLE [dbo].[SB_PATIENT_REGISTER] WITH CHECK ADD CONSTRAINT [FK_SB_PATIE_DEBTOR_TO_SB_DEBTO] FOREIGN KEY([SB_DEBTOR_ID])
REFERENCES [dbo].[SB_DEBTOR] ([SB_DEBTOR_ID])
would you agree that the index is not required?
October 6, 2009 at 3:55 pm
A foreign key by itself does not create an index, so it is a good practice to create a matching index on all foreign keys.
October 6, 2009 at 5:06 pm
Michael is correct, Scott.
A foreign key and an index are two different objects designed to do two different jobs. A foreign key constraint is there to ensure data integrity while an index is there to speed up lookups where the column in question is referenced in a where clause.
When you add a foreign key to a column, you are implying that the query engine will be asked to return data from the table where certain values are found in the column; namely, where they match the primary key constraint it is matching in the 'parent' table. For this reason, under most circumstances you will want an index on the column to aid this lookup.
When you create a primary key constraint, an index is automatically created however this is not the case with a foreign key constraint; you have to put one on yourself.
HTH,
Martin
October 6, 2009 at 6:10 pm
Hi,
Thanks for your answers Michael and Martin, that explains it quite well for me.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply