Constraint (FK)

Example: You want to create a relationship between the Employee table and the Address table. You would create a FOREIGN KEY (FK) in the Address table with the same data type (and often the same name) as the PK in the Employee table. Use to enforce referential integrity.

• Indexes are not automatically created on FOREIGN KEYs.

• Nonclustered indexes are commonly created on FKs to improve performance of queries.

• Referential integrity can also be used to enforce data entry.

  • For example, a single-column State table could be created with the 50 states listed by the two-letter abbreviation in the PK named State. We could create a relationship between the Employee table state column (by creating an FK) and the State table state column (the PK). Any entries into the Employee table state column would be checked against the State table state column; if the state doesn’t exist in the State table, the data entry is rejected.

• Constraint checking is done BEFORE the data is entered.