The following is an excerpt from The Relational Database Dictionary by C.J. Date, ISBN: 0-596-52798-5, Copyright © 2006 C.J. Date. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
Primary key: A candidate key that has been singled out for special syntactic treatment for some reason. While a given relvar can have any number of candidate keys, it can have at most one primary key. For a given relvar, however, whether some candidate key is to be chosen as primary, and if so, which one, are essentially psychological issues, outside the purview of the relational model. Note: The relational model originally insisted that base relvars, at least, should always have a primary key. It also insisted that foreign keys refer to primary keys specifically. However, there were never any good logical reasons for these rules, and rules that apply to base relvars but not to other kinds are more than a little suspect in any case (because they violate The Principle of Interchangeability); thus, the primary key notion could be dropped without serious loss. We mention it here mainly for historical reasons.
Relational model: The formal theory or foundation on which relational databases in particular and relational technology in general are based. The relational model is often loosely characterized as having three aspects: a structural aspect, which has to do with relations as such; an integrity aspect, which has to do with candidate and foreign keys; and a manipulative aspect, which has to do with operators such as join. More precisely, the relational model consists of the following components:
- an open-ended collection of scalar types, including in particular type BOOLEAN;
- a relation type generator and an intended interpretation for relations of types generated thereby;
- facilities for defining relation variables of such generated relation types;
- a relational assignment operator; and
- an open-ended collection of generic readonly operators (i.e., relational algebra or relational calculus) for deriving relations from relations.
Table: 1. SQL analog of either a relation or a relvar, as the context demands. Here are some of the major differences between tables in SQL and their relational counterparts:
- SQL tables can contain duplicate rows;
- SQL tables can contain nulls;
- SQL tables have a left-to-right column sequence;
- SQL tables can have two or more columns with the same name;
- SQL tables can have what are, in effect, columns with no name at all.
About the author
C.J. Date is an independent author, lecturer and researcher, specializing in relational database technology (a field he helped pioneer). He is best known for his books, especially An Introduction to Database Systems, which has sold nearly 750,000 copies and is used by universities worldwide.