Foreign keys are a must in any database to ensure that all references to values in other tables are valid in that they actually exist, but they can be used for more than mere existence checks; they can also be used to enforce business rules. Let's trot out the age-old example of Customers and Orders, and add another table that holds customer Addresses. Pardon me while I whip out a Visio diagram:

So we see here that we have a simplified Customer table, an Order table holding each Customer's many Orders, an Address table holding each Customer's multiple addresses, and finally a relationship between Order (AddressID) and Address (ID), ensuring that each Order contains a reference to an existing Address row. This is fine from the standpoint that you can count on there being an Address for every Order, but not so good if your business rules require that Orders only ship to Addresses belonging to the Customer.

Now, possible responses at this point include: "I'll enforce that in the client app", or better, "I better check that in a trigger or the insert/update stored procedures." In my opinion, handling this kind of rule in a client application is the worst decision, because it means that someone could develop another application that accesses your data but doesn't enforce this rule. Handling the rule within stored procedures is better, because at least now the logic is more easily sharable between applications, but there is still the possibility of bypassing the stored procedure layer, which makes the rule somewhat optional (although this might actually be desirable in some scenarios). The best wrong response is to implement the rule in a trigger, because this shares the logic and makes it next to impossible to unintentionally enter data that violates the rule. However, this is still not the optimal approach, because this still requires writing code, and there is a way to enforce this rule without doing anything more than changing the way the relationship between Order and Address is declared.

Consider what would happen if you simply added the CustomerID column to both sides of the relationship. Now the foreign key goes from Order (AddressID, CustomerID) to Address (ID, CustomerID). The simple inclusion of this column enforces the rule that the Order can only refer to Address belonging to the same Customer that the Order belongs to. This technique applies to a pretty large class of problems. Another example would be a scenario where something possessed you to consolidate all of the "lookup" (ID-Name pair) tables into a single giant lookup. This isn't a technique that I am fond of, but it's not all that uncommon, either:

In this scenario, you don't want AddressTypeID referencing Lookup rows that are meant to be OrderStatusIDs or vice versa. This can be prevented by introducing a LookupTypeID into all tables and including it in the foreign key definition, and then constraining the value of the column in Address to the one LookupTypeID allowed for Addresses, and likewise constraining the value of the column in Order to the one LookupTypeID allowed for Orders:

So for example, you would define a LookupType of AddressType with ID = 1, set up the list of allowed Address Types in Lookup using LookupTypeID = 1, and create the column in Address with a CHECK constraint that forces the value to only 1. By including the LookupTypeID column in the foreign key and constraining the value to 1, you ensure that AddressTypeID can only reference Lookup rows that are actually Address Types and not Order Statuses.

So, that's it. Think about including more than one column in your foreign key references to further constrain the values allowed in tables, and Get More Out of Your Foreign Keys.