Foreign keys (join columns)

A foreign key is a column or group of columns in one table that contains values that match the primary key in another table. Foreign keys are used to join tables. The following figure shows the primary and foreign keys of the customer and orders tables from the demonstration database.
Figure 1. Primary and foreign keys in the customer-order relationships
Portions of two database tables are shown graphically as collections of rectangular cells like a table or a matrix. Some of the columns are labeled with the names of the represented column in the database table. For the table named "customer", only the customer_num column is labeled. The customer_num column is shaded and marked as the primary key of the table. For the table named "orders" two columns are labeled: order_num, and customer_num. The customer_num column is shaded and marked as the foreign key. A gray arrow points from one of the cells in the customer_num column of the orders table to one of the cells in the customer_num column in the customer table. This shows that the value in foreign key column refers to an existing value in the primary key column.
Tip: For ease in maintaining and using your tables, it is important to use names for the primary and foreign keys so that the relationship is readily apparent. In Figure 1, both the primary and foreign key columns have the same name, customer_num. Alternatively, you might name the columns in Figure 1 customer_custnum and orders_custnum, so that each column has a distinct name.

Foreign keys are noted wherever they appear in the model because their presence can restrict your ability to delete rows from tables. Before you can delete a row safely, either you must delete all rows that refer to it through foreign keys, or you must define the relationship with special syntax that allows you to delete rows from primary-key and foreign-key columns with a single delete command. The database server does not allow deletions that violate referential integrity.

To preserve referential integrity, delete all foreign-key rows before you delete the primary key to which they refer. If you impose referential constraints on your database, the database server does not permit you to delete primary keys with matching foreign keys. It also does not permit you to add a foreign-key value that does not reference an existing primary-key value. For more information about referential integrity, see the IBM® Informix® Guide to SQL: Tutorial.