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 name, street and zipcode. We have another table city that has, among others, columns name and zipcode.

SELECT p.name, p.street, p.zipcode, c.name as city
  FROM person AS p, city AS c
  WHERE
    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 city.

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 person the zipcode field refers to table city, column 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 RESTRICT
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 NULLIFY
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 parent, then 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)

Log in or register to write something here or to contact authors.