Re: constraints and primary keys

On 11 jun, 14:16, "Peter" <ptdsmitch...@xxxxxxxxxxx> 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.

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

.

Relevant Pages

  • Re: Unique fields in table design
    ... Otherwise, the multi-column primary key ... The so-called 'surrogate key' ... Using UNIQUE or PRIMARY KEY constraints for ... INSERT INTO SalaryReviews (employee_nbr, salary_start_date, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Best Data storage practice?
    ... We don't care about that stuff; this is the database, ... A column is not a field; it can have constraints. ... PRIMARY KEY, ... Sample data is also a good idea, ...
    (microsoft.public.sqlserver.programming)
  • Re: Slow data entry form
    ... Create database constraints (PRIMARY KEY, UNIQUE, validation ...
    (microsoft.public.access.formscoding)
  • Re: Do all tables have to have a primary key?
    ... to look beyond Access/Jet's 'index' flavour of constraints (Jeff, ... required to ensure no duplicate data). ... Because PRIMARY KEY is implemented in Jet using an index, ... Appearing as bold text in the Relationships diagram; ...
    (microsoft.public.access.tablesdbdesign)
  • Naming conventions for special database objects
    ... I am currently doing a review of my personal database naming ... InsteadOfInsert_Orders for multi-purposed triggers. ... Default constraints ... How to name a primary key if not primary key? ...
    (comp.databases)