A foreign key links a column in one table to key values in another table. The table that supplies the key is the parent; the table that stores references is the child. A foreign-key constraint enforces the rule: every referenced child value must already exist in the parent table. section 18.4 section 8.1.2
For example, rather than repeating a title name on every order, an orders table can store TitleID. TitleID points to the unique TitleID in the title table. This saves space and lets many order rows refer to the same title without repeating its name. section 8.1.2
Create a useful foreign-key relationship by checking three things:
- The parent column has unique values, so each reference identifies exactly one parent row. section 8.1.2
- The child column holds the values that refer to the parent column. section 18.4
- Existing rows obey the rule before adding the constraint, or the database returns an error. section 18.4.2
Adding a category reference
The CategoryID column in title is the child column, and category(CategoryID) is the parent key. ALTER TABLE adds a named constraint after the table exists. FK_title_category follows a practical convention: FK_, then child table, then parent table. Constraints are best created with tables before rows are inserted; otherwise invalid existing values can block the change. section 18.4.2
ALTER TABLE title ADD CONSTRAINT FK_title_category FOREIGN KEY (CategoryID) REFERENCES category(CategoryID);
This statement names the relationship, identifies the child column after FOREIGN KEY, and identifies its parent table and column after REFERENCES. section 18.4.2
Data diagrams show table relationships as lines: a line between two tables indicates a foreign key. They help locate related tables, though the diagram may not show the exact columns. Foreign-key columns are commonly indexed because relationships use them to join tables; MySQL may create such an index automatically, but other database systems may not. section 18.4.1 section 19.3.2
A foreign key supports data integrity, but it does not require child values to be unique. For instance, multiple shipment rows can refer to the same OrderID in a one-to-many relationship. Use NOT NULL as an additional constraint when every child row must contain a reference, such as a payment that must go to an author. section 19.3.2 section 23.1.2
