<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML
><HEAD
><TITLE
>Foreign Keys</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 7.4.1 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Advanced Features"
HREF="tutorial-advanced.html"><LINK
REL="PREVIOUS"
TITLE="Views"
HREF="tutorial-views.html"><LINK
REL="NEXT"
TITLE="Transactions"
HREF="tutorial-transactions.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
NAME="creation"
CONTENT="2003-12-22T03:48:47"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 7.4.1 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="tutorial-views.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="tutorial-advanced.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 3. Advanced Features</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="tutorial-advanced.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="tutorial-transactions.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="TUTORIAL-FK"
>3.3. Foreign Keys</A
></H1
><A
NAME="AEN897"
></A
><A
NAME="AEN899"
></A
><P
> Recall the <CODE
CLASS="CLASSNAME"
>weather</CODE
> and
<CODE
CLASS="CLASSNAME"
>cities</CODE
> tables from <A
HREF="tutorial-sql.html"
>Chapter 2</A
>. Consider the following problem: You
want to make sure that no one can insert rows in the
<CODE
CLASS="CLASSNAME"
>weather</CODE
> table that do not have a matching
entry in the <CODE
CLASS="CLASSNAME"
>cities</CODE
> table. This is called
maintaining the <I
CLASS="FIRSTTERM"
>referential integrity</I
> of
your data. In simplistic database systems this would be
implemented (if at all) by first looking at the
<CODE
CLASS="CLASSNAME"
>cities</CODE
> table to check if a matching record
exists, and then inserting or rejecting the new
<CODE
CLASS="CLASSNAME"
>weather</CODE
> records. This approach has a
number of problems and is very inconvenient, so
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> can do this for you.
</P
><P
> The new declaration of the tables would look like this:
</P><PRE
CLASS="PROGRAMLISTING"
>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
);</PRE
><P>
Now try inserting an invalid record:
</P><PRE
CLASS="PROGRAMLISTING"
>INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');</PRE
><P>
</P><PRE
CLASS="SCREEN"
>ERROR: insert or update on table "weather" violates foreign key constraint "$1"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".</PRE
><P>
</P
><P
> 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 <A
HREF="ddl.html"
>Chapter 5</A
>
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.
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="tutorial-views.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="tutorial-transactions.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Views</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="tutorial-advanced.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Transactions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
