Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: constraints and primary keys

Re: constraints and primary keys

From: David Cressey <>
Date: Mon, 11 Jun 2007 17:36:54 GMT
Message-ID: <WKfbi.3634$4t5.2099@trndny07>

"Jan Hidders" <> wrote in message
> On 11 jun, 14:16, "Peter" <> wrote:
> >
> > I am after any feedback on the concepts of primary key, constraints,
> > (and not null). Is a constraint a key? Or am I in the ballpark to
> > one constraint is a primary key, but there others, i.e. unique. Or does
> > call a unique constraint a key/primary key?
> This is rather basic terminology, so as Bob suggested correctly, you
> might want to pick up a book on basic database theory. Anyway, I'll
> give a few hints to get you started.
> The term "constraint" is just a general term that refers to any
> proposition about the the contents of the database that should (and
> hopefully can) be maintained true by the DBMS, i.e., any update that
> makes it false should be disallowed. There are many different types of
> constraints, varying in complexity and scope. There are for example
> domain constraints that restrict individual values in attributes (e.g.
> NOT NULL), relation constraints (aka table constraints) that restrict
> the possible contents of a single relation (e.g. key constraints) and
> database constraints that involve more than one relation (e.g. foreign
> key constriants). So you see that key constraints are only one of a
> wide range of possible constraints.
> The "unique constraint" that you mentioned is formally known as a
> "superkey" which is defined as a set of attributes that allows you to
> always uniquely identify each row in the relation. If your schema is
> well-designed then the unique constraints that you specify should in
> fact be "candidate keys" which means that there is proper subset of
> attributes that is also a superkey. So you may have seen a remark by
> others that the unique constraint corresponds with a candidate key,
> which is strictly speaking not necessarily correct.

In SQL, a unique constraint does not imply a "not null" constraint. A column or set of columns that satisfies a unique constraint will be a candidate key if and only if each column also satisfies a not null constraint.

> The "primary key constraint" is in principle nothing more than a
> "unique constraint" except that by declaring it as primary you
> indicate that it will be the main way of identifying rows in the
> relation. Depending on the DBMS you use and settings therein this may
> mean that (1) certain lookups on the involved attributes will be
> faster than on the other unique constraint attributes, (2) it cannot
> contain NULL values and (3) all foreign keys to the relation in
> question should point to the attributes in this key constraint.
> > And composite primary keys? not sure where this fits in.
Received on Mon Jun 11 2007 - 12:36:54 CDT

Original text of this message