February 10, 2016 at 7:32 am
Hi,
I have a table below :
IF OBJECT_ID('dbo.orders', 'U') is not null
DROP TABLE dbo.orders
CREATE TABLE dbo.Orders
(
orderidINTNOT NULL,
empidINTNOT NULL,
custidVARCHAR(10)NOT NULL,
ordertsDATETIME2NOT NULL,
qtyINTNOT NULL
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
CONSTRAINT FK_Orders FOREIGN KEY (empid) REFERENCES dbo.employees(empid)
)
Notice I've added primary & foreign key constraints.
I noticed that I get an index for the primary key but not one for the foreign key.
I wondered what are the cases when we should add an index on the foreign key? I imagine if it's being used in joins heavily or searched on it would make sense. Anything else that I'm missing?
Best
lee
February 10, 2016 at 10:11 am
You mostly got it right already. Indexes are automatically created for primary keys and for unique constraints. They are not automatically created for foreign key constraints.
It is often (bot not always) useful to add indexes to foreign key columns in the scenarios you describe: when they are used in many joins or in many filters. Another situation where they can be useful is if the referenced table is subject to a lot of deletes, or of updates that affect the referenced column. In those cases, SQL Server has to verify that the key value you delete or change does not exist in the referencing table, and without an index that incurs in index scan. However, I see a lot of foreign keys that reference tables that are hardly ever subject to deletes, and updates of referenced columns are also very rare, so this is only a consideration in some specific scenarios.
EDIT: Also note that when a foreign key column is used in joins but the queries are not restrictive and lots of rows are used, then an index on those columns will not be useful. But it can be exceptionally useful for joins in restrictive queries. (E.g. you have 1 million orders, 10 million orders, and a query that pinpoints just one product - the index on ProductID in the Orders table is likely to be used. Do the same query for 50% of your products, and it's not useful at all).
February 10, 2016 at 10:38 am
There is no need to index foreign keys by default. So long as employees(empid) is indexed, which it should be since the empid column is the primary key of employees table, then enforcement of the referential constraint is covered by that primary key index. Basically, indexing empid on the orders table would only be useful if you're querying the orders table and using empid as a predicate. It should be considered as a candidate for indexing as you would any other attribute column.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 10, 2016 at 10:39 am
Also, I would expect custid to be a foreign key as well.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 10, 2016 at 10:45 am
Your timing on this question is great. I wrote an article on indexing of foreign keys for SSC, and it just went live this morning.
http://www.sqlservercentral.com/articles/foreign+keys/136445/
February 10, 2016 at 11:21 am
Thanks for all your comments - very useful insights indeed.
This is a great community.
Best
Lee
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply