PostgreSQL Advanced Features : Views and Foreign Keys

We will now discuss some more advanced features of SQL that simplify management and prevent loss or corruption of your data. Finally, we will look at some PostgreSQL extensions.

Views

Suppose the combined listing of weather records and city
location is of particular interest to your application, but you do not want to type the query each time
you need it. You can create a view over the query, which gives a name to the query that you can refer
to like an ordinary table:

CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location

SELECT * FROM weather, cities
WHERE city = name;
FROM myview;

Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate
the details of the structure of your tables, which might change as your application evolves,
behind consistent interfaces.
Views can be used in almost any place a real table can be used. Building views upon other views is
not uncommon.

Foreign Keys
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:

CREATE TABLE cities (
city varchar(80) primary key,
location point
);

CREATE TABLE weather (
city varchar(80) references cities(city),
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: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".

The behavior of foreign keys can be finely tuned to your application. Making correct use of
foreign keys will definitely improve the quality of your database applications, so you are strongly
encouraged to learn about them.


PostgreSQL has many features not touched upon in this tutorial introduction, which has been oriented
toward newer users of SQL. These features are discussed in more detail in the remainder of PostgreSQL 9.5.7 Documentation book.