Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: foreign vs primary key

Re: foreign vs primary key

From: Larry Coon <larry_at_assist.org>
Date: Thu, 20 Sep 2001 09:13:55 -0700
Message-ID: <3BAA15C3.2080@assist.org>


mikeg wrote:

> Thanks, I can see that but what is the significance? Why is it necessary to
> identify the foreign key? How are they used? Aren't primary keys enough?

Primary keys and foreign keys are both examples of constraints, which are mechanisms to avoid letting bad data into your database. For example, a primary key constraint ensures that the key uniquely identifies a specific row in the table. It does this by preventing the entry of a row whose primary key already exists in that table. So it prevents the anomalous condition of having two rows in the same table with the same key (among other things). Note that a relational database COULD exist without any declaration of primary keys -- they are just mechanisms to protect the quality of the data.

A foreign key is another one of these mechanisms, which protects a different aspect of data quality. Very few databases are built with just one table. There are likely dozens, if not hundreds of tables in a production database. Those tables do not live in isolation from each other -- they are related to each other in some way. A post earlier in this thread used an example of authors and books. Clearly they are related -- authors write books, and books are written by authors.

A foreign key is simply a mechanism to express one of these relationships between tables. It declares in the definition of the database that the two tables are related. With this declaration in place, the database, in turn, will protect the integrity of the data to ensure that data are not entered which run afoul of this expressed relationship. Want to enter a book for an author that doesn't exist? Can't. Want to delete an author from the database, without saying what happens to all the books written by that author? Can't. The database won't let you, because of the foreign key.

Can you have a relational database without foreign keys? Sure. But then what protects the quality of the data if I enter a book for author_id 123 when there is no author 123?

This aspect of protection is called referential integrity. You asked if primary keys are enough. The answer is no, because primary keys don't enforce referential integrity. They enforce another aspect of integrity, entity integrity.  

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Thu Sep 20 2001 - 11:13:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US