Re: constraints and primary keys

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 in addition there is no 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.

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.

Composite primary keys are simply primary keys that consist of more than one attribute. Both unique constraints and primary keys can consist of more than one attribute. There is unfortunately not a simple set of rules that can tell you how to choose your primary key, but it is certainly not true that you should avoid composite primary keys at all costs. In fact, doing so can lead to very serious design errors.

That's it for now. Hope this helped.

• Jan Hidders