RelationalDBDesign RelationalDBDesign

Managing Constraints   «Prev 

Why index foreign keys?

It is not necessary to have an index associated with a column that is a foreign key. One foundation of relational database design is that separate, but related, data can and should be stored in separate tables. A foreign key is an excellent way to make sure that there are data values in both tables that can be used to join them together, if requested.You should not only define a foreign key to allow the join, but also create an index so that the join performs properly.
A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. A foreign key is created by defining a FOREIGN KEY constraint when you create or modify a table.

Foreign keys may be part of a concatenated primary key

Foreign keys[1] may be part of a concatenated primary key, or they may not be part of their table's primary key at all. Consider, for example, a pair of simple DistributedNetworks customers and orders relations:
customers (customer_numb, first_name, last_name, phone)
orders (order_numb, customer_numb, order_date)

The customer number column in the orders table is a foreign key that matches the primary key of the customers table. It represents the one to-many relationship between customers and the orders they place. However, the customer number is not part of the primary key of its table but is a non-key attribute that is nonetheless a foreign key. Technically, foreign keys need not have values unless they are part of a concatenated primary key and can be null. However, in this particular database, DistributedNetworks would be in serious trouble if customer numbers were null. There would be no way to know which customer placed an order. A relational DBMS uses the relationships indicated by matching data between primary and foreign keys. For example, assume that an DistributedNetworks employee wanted to see what titles had been ordered on order number 11102. First, the DBMS identifies the rows in the order items table that contain an order number of 11102. Then, it takes the items number from those rows and matches them to the item numbers in the items table. In the rows where there are matches, the DBMS retrieves the associated data.

[1]A field (or combination of fields) used to link tables; a corresponding primary key field occurs in the same database.