Foreign Keys

3.3. Foreign Keys

Recall the weather and cities tables from Chapter 2. Consider the following problem: You want to make sure that no one can insert rows in the weather table that do not have a matching entry in the cities table. This is called maintaining the referential integrity of your data. In simplistic database systems this would be implemented (if at all) by first looking at the cities table to check if a matching record exists, and then inserting or rejecting the new weather records. This approach has a number of problems and is very inconvenient, so PostgreSQL can do this for you.

The new declaration of the tables would look like this:

 	city     varchar(80) primary key,
	 location point

CREATE TABLE weather (
	city      varchar(80) references cities,
	temp_lo   int,
	temp_hi   int,
	prcp      real,
	date      date

Now try inserting an invalid record:

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

ERROR:  <unnamed> referential integrity violation - key referenced from weather not found in cities

The behavior of foreign keys can be finely tuned to your application. We will not go beyond this simple example in this tutorial, but just refer you to the PostgreSQL 7.3.4 User's Guide for more information. Making correct use of foreign keys will definitely improve the quality of your database applications, so you are strongly encouraged to learn about them.

Online Resources
Beginner Tutorials
Microsoft SQL
Contact Us
Random quote of the moment:
Europe Passes Pro-spam Law I though only Americans were that fucking stupid => apparently americans are quite naive :)
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!