Does a foreign key automatically create an index

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I notice that Entity Framework 6 1 pointed at MSSQL does automatically add indexes on foreign keys

User · Answer

A foreign key is a constraint  a relationship between two tables - that has nothing to do with an index per se   But it is a known fact that it makes a lot of sense to index all the columns that are part of any foreign key relationship  because through a FK-relationship  you ll often need to lookup 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 a FK  but a 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Does a foreign key automatically create an index?

Examples related to sql-server