Foreign Keys

Recall the weather and cities tables from Chapter 22. 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 is very inconvenient, so PostgreSQL can do everything for you if you specify primary and foreign key constraints in your table declarations:

CREATE TABLE cities (
	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 refer you to the Red Hat Database SQL Guide and Reference 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.