Contents
Do you need an index on a foreign key?
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. Information about foreign keys on InnoDB tables can also be found in the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables, in the INFORMATION_SCHEMA database.
Can you have a foreign key without a primary key?
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. So in your case if you make AnotherID unique, it will be allowed.
Do you need to index a foreign key?
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.
When to use foreign key columns in SQL Server?
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.
When to use foreign key in join criteria?
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.
Is there a foreign key from an order to a customer?
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.