Re: constraints and primary keys

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


"Jan Hidders" <hidders_at_gmail.com> wrote in message news:1181576005.557859.67320_at_q66g2000hsg.googlegroups.com...
> On 11 jun, 14:16, "Peter" <ptdsmitch..._at_bigpond.com> wrote:
> >
> > I am after any feedback on the concepts of primary key, constraints,
unique
> > (and not null). Is a constraint a key? Or am I in the ballpark to
suggest
> > one constraint is a primary key, but there others, i.e. unique. Or does
one
> > 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 - 19:36:54 CEST

Original text of this message