461

I've been told that if I foreign key two tables, that SQL Server will create something akin to an index in the child table. I have a hard time believing this to be true, but can't find much out there related specifically to this.

My real reason for asking this is because we're experiencing some very slow response time in a delete statement against a table that has probably 15 related tables. I've asked our database guy and he says that if there is a foreign key on the fields, then it acts like an index. What is your experience with this? Should I add indexes on all foreign key fields or are they just unnecessary overhead?

Braiam
  • 1
  • 11
  • 47
  • 78
Nick DeVore
  • 9,748
  • 3
  • 39
  • 41
  • 54
    not silly to ask this at all ! – marc_s May 07 '09 at 18:20
  • After seeing some of the answers, I guess you're right. There's a bit of confusion on this. Thanks all! – Nick DeVore May 07 '09 at 18:24
  • 6
    If you are getting slow deletes and the table you are deleting from referenced by other tables, you will probably get a performance boost by indexing the foreign keys in the *other* tables. This is because when SQL is deleting a row, it needs to check referential integrity on the row. To do this, it obviously needs to check that no other rows exist referencing the row you are deleting. – Noel Kennedy May 07 '09 at 18:26
  • * if there is no index on the foriegn key, it will need to table scan looking for references to the PK of the row you are trying to delete. Hope this makes sense. Maybe try to get your DB guy to look at the query execution plan for the delete. He may be able to determine that the slow down is because of this. – Noel Kennedy May 07 '09 at 18:28
  • @Noel - I'm headed that way as you read this ;) – Nick DeVore May 07 '09 at 18:30
  • 3
    I'd say a database guy who didn't know this is must be in serious need of training. Database people are responsible for performance, it is their job to know this sort of thing. This suggests gross incompetence. – HLGEM May 07 '09 at 20:57
  • I have the same understanding as your DB guy - that FKs do in fact create an index. – Vinnie May 07 '09 at 18:09
  • 12
    No - a FK does *NOT* automatically create an index. It makes sense to create one - but it is *NOT* done automatically by SQL Server. – marc_s May 07 '09 at 18:11
  • Wow. I always thought that adding an FK also added an implicit index to the child table. I know I've read that before. And I know that MySQL and a few other DBs I've worked with in the past do. This has rocked my world. – Josh Mouch Oct 10 '13 at 15:12
  • Yes, it seems MySQL does: https://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically – Bruno Medeiros Aug 02 '17 at 08:04

11 Answers11

415

A foreign key is a constraint, a relationship between two tables - that has nothing to do with an index per se.

However, it makes a lot of sense to index all the columns that are part of any foreign key relationship. An FK-relationship will often need to look up a relating table and extract certain rows based on a single value or a range of values.

So it makes good sense to index any columns involved in an FK, but an FK per se is not an index.

Check out Kimberly Tripp's excellent article "When did SQL Server stop putting indexes on Foreign Key columns?".

Updated 5/31/2023
For those reading this now, I (the original poster) wanted to update Marc's excellent answer to point out that Entity Framework Core now auto generates indexes for foreign keys discovered by convention. Most helpful. Not sure how far back that feature exists, I'm using EF Core 7.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Yep. I'm just about positive that PostgreSQL creates an index. I'm pretty sure MySQL does. Making the index makes a ton of sense, but IT'T NOT REQUIRED. After all, why reference something if every time the DB goes to look it up it has to do a tablescan? – MBCook May 07 '09 at 18:15
  • This article referred above is kind of confusing because SQL server or any other database never puts an index on FK. – vsingh Dec 09 '11 at 17:11
  • 7
    @vsingh: that's exactly what the article tries to convey - it's a common **misconception** that a FK automatically creates an index - it does **not** do that. – marc_s Dec 09 '11 at 17:12
  • 6
    @MBCook No, PostgreSQL does *not* (at least in 9.2 or any prior version) automatically create an index on the referencing side of a foreign key relationship defined with `REFERENCES`. It automatically creates a `UNIQUE` index for a `PRIMARY KEY` or `UNIQUE` constraint, and requires that a `UNIQUE` index be present for the *referenced* end of a foreign key relationship, but does nothing automatically for the *referencing* end, though it's often a good idea to make one yourself. See http://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys – Craig Ringer Aug 23 '12 at 04:56
  • 26
    The confusion may exist because MySQL InnoDB both requires and automatically creates an index when you add a foreign key - http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html – humbads Oct 18 '12 at 13:37
  • Does this also work if I have a **composite index** on the foreign key? Let's say I have a FK on `books_id` and a composite index on `(books_id,users_id)`. – Daan Jan 25 '21 at 13:22
  • 1
    @Daan: any composite index *can be used* if the n left-most columns are used in a select. So for `(books_id, users_id)`, that index *might* be used if you're searching for just `books_id`; or for `books_id` and `users_id`. It will ***NOT*** ever be able to be used however if you need to search for *only* `users_id` – marc_s Jan 25 '21 at 16:32
53

Wow, the answers are all over the map. So the Documentation says:

A FOREIGN KEY constraint is a candidate for an index because:

  • Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.

  • Foreign key columns are often used in join criteria when the data from related tables is combined in queries by matching the column(s) in the FOREIGN KEY constraint of one table with the primary or unique key column(s) in the other table. An index allows Microsoft® SQL Server™ 2000 to find related data in the foreign key table quickly. However, creating this index is not a requirement. Data from two related tables can be combined even if no PRIMARY KEY or FOREIGN KEY constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.

So it seems pretty clear (although the documentation is a bit muddled) that it does not in fact create an index.

Yishai
  • 90,445
  • 31
  • 189
  • 263
  • 6
    Exactly - it's a *CANDIDATE* for an index - but it's not automatically created as one! Quite clear actually, IMHO :-) – marc_s May 07 '09 at 18:21
  • 7
    I found this part muddled: "a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria." That should read "... two tables should be optimized ..." – Yishai May 07 '09 at 18:55
26

No, there is no implicit index on foreign key fields, otherwise why would Microsoft say "Creating an index on a foreign key is often useful". Your colleague may be confusing the foreign key field in the referring table with the primary key in the referred-to table - primary keys do create an implicit index.

Michael Borgwardt
  • 342,105
  • 78
  • 482
  • 720
  • 1
    what is "An implicit index"? does it just imply that there's a b*tree without creating it? – Stephanie Page Mar 30 '12 at 16:16
  • 1
    @Stephanie Page: It's an expression I just made up for this answer to mean an index that is automatically created. If you declare a primary key, SQL server automatically creates and index for it. But not ff you declare a foreign key (some other DB systems do). – Michael Borgwardt Mar 30 '12 at 16:41
15

Foreign keys do not create indexes. Only alternate key constraints(UNIQUE) and primary key constraints create indexes. This is true in Oracle and SQL Server.

Kirk Woll
  • 76,112
  • 22
  • 180
  • 195
Sandeep Kanuri
  • 151
  • 1
  • 2
10

In PostgeSql you can check for indexes yourself if you hit \d tablename

You will see that btree indexes have been automatically created on columns with primary key and unique constraints, but not on columns with foreign keys.

I think that answers your question at least for postgres.

Gregor
  • 592
  • 7
  • 20
  • Sorry, I didn't notice that the question concerns MS SQL Server but after having posted the answer. It could probably still help someone ... – Gregor Jul 07 '12 at 13:29
  • This likely has changed. I've just created a foreign key and it has an auto-generated btree index. – Akaisteph7 Aug 10 '23 at 21:00
8

Say you have a big table called orders, and a small table called customers. There is a foreign key from an order to a customer. Now if you delete a customer, Sql Server must check that there are no orphan orders; if there are, it raises an error.

To check if there are any orders, Sql Server has to search the big orders table. Now if there is an index, the search will be fast; if there is not, the search will be slow.

So in this case, the slow delete could be explained by the absence of an index. Especially if Sql Server would have to search 15 big tables without an index.

P.S. If the foreign key has ON DELETE CASCADE, Sql Server still has to search the order table, but then to remove any orders that reference the deleted customer.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Exactly - that's the reason an index on a FK does make a lot of sense (most of the time) – marc_s May 07 '09 at 18:25
  • 1
    Most of the time? Seems this is the case for a delete from a parent. If most of the time you delete from parents, I guess that's true. – Stephanie Page Mar 30 '12 at 16:18
7

SQL Server autocreates indices for Primary Keys, but not for Foreign Keys. Create the index for the Foreign Keys. It's probably worth the overhead.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Paul Sonier
  • 38,903
  • 3
  • 77
  • 117
5

It depends. On MySQL an index is created if you don't create it on your own:

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.

Source: https://dev.mysql.com/doc/refman/8.0/en/constraint-foreign-key.html

The same for MySQL 5.6 eh.

Lorenzo Franco Ranucci
  • 71
  • 1
  • 3
4

Strictly speaking, foreign keys have absolutely nothing to do with indexes, yes. But, as the speakers above me pointed out, it makes sense to create one to speed up the FK-lookups. In fact, in MySQL, if you don't specify an index in your FK declaration, the engine (InnoDB) creates it for you automatically.

shylent
  • 10,076
  • 6
  • 38
  • 55
3

Not to my knowledge. A foreign key only adds a constraint that the value in the child key also be represented somewhere in the parent column. It's not telling the database that the child key also needs to be indexed, only constrained.

Gandalf
  • 9,648
  • 8
  • 53
  • 88
2

I notice that Entity Framework 6.1 pointed at MSSQL does automatically add indexes on foreign keys.

Luke Puplett
  • 42,091
  • 47
  • 181
  • 266
  • I don't believe it does if you manually flag the column as part of an index (e.g. if you're creating a composite index over several members) – Rowland Shaw Aug 02 '19 at 10:39