Don't sanity check - let the database do the sanity checks for you!
In a RDBMS, it's not uncommon to use specific queries to join results from two database tables.
A simple example in SQL should illustrate this: We have a table
person with columns
zipcode. We have another table
city that has, among others, columns
SELECT p.name, p.street, p.zipcode, c.name as city
FROM person AS p, city AS c
p.zipcode = c.zipcode;
Which would then return stuff like:
Esmerkki X. Henkilö; Maija Möttösen Katu 8; 12345; Melkoskylä
(This example is from Finland - here we have addresses in form "person, street address, zipcode city", and zipcodes always have 5 numbers. In real world database, we probably would have an additional table with zip codes that map to cities using some "internal use" city ID number (used as primary key), and no single "city" table, because some cities have several post offices...)
Here, we already have a pretty good list of towns in table
city, so there's no need to store both zipcode and the town in table
person - so we only store the zipcode to the
person table and take the name of the city from
So much for relation database theoretical background. A foreign key is a RDBMS constraint. It basically says that "this column refers to another column in another table - make sure they stay in synch!"
In our example, in table
zipcode field refers to table
zipcode. In SQL, this constraint would be expressed as:
FOREIGN KEY (zipcode) REFERENCES city (zipcode)
Now the database knows that there's a connection between the two tables.
Typically, this is used to describe what happens when the referenced row changes or is DELETEd. Here's how to describe what happens when the key is deleted:
FOREIGN KEY (zipcode) REFERENCES city (zipcode) ON DELETE NO ACTION or
- This says that you just can't delete the referenced row; in our case, deleting the
city row with
zipcode=12345 would not work at all because there's a record in
person that refers to it.
FOREIGN KEY (zipcode) REFERENCES city (zipcode) ON DELETE CASCADE
- This says that if the referenced row is deleted, the referencee must also be deleted. In our example, removing the city would also remove all persons living in that city, so this probably wouldn't be what we're looking for.
FOREIGN KEY (zipcode) REFERENCES city (zipcode) ON DELETE SET NULL or
- This says that if the referenced row is deleted, the referencing field is set to NULL - in other words, if the city is removed, the person still exists but would now no longer live in any city according to our database. In this example, this would be a tricky situation, and we'd probably not do that - if you remove the row, finding the people now homeless would not be fun, so this is probably not what we want...
There are also rules on what to do when the referenced row is UPDATEd.
Also, a foreign key doesn't need to refer to another table; For example, if we had an
article table with primary key column
id and a column
FOREIGN KEY (parent) REFERENCES article (id) ON DELETE SET NULL would say that some article on bulletin board would have a parent article that is referenced by article's ID number.
(Sources: PostgreSQL documentation; SQL-Ohjelmointi Pro Training, Ari Hovi, ISBN 951-762-756-4)