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: Tue, 12 Jun 2007 08:56:05 GMT
Message-ID: <Fctbi.8002$Ar5.6790@trndny01>

"Jan Hidders" <> wrote in message
> On 11 jun, 19:36, "David Cressey" <> wrote:
> > "Jan Hidders" <> wrote in message
> >
> >
> >
> > > 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.
> You meant superkey, right?

Superkey, right.

> :-P But, yes, thank you for that
> correction. I actually didn't know that this is the case in ANSI SQL

Now that you mention it, I'm not sure about ANSI SQL.

> and can only bring to my defense that the default behavior in SQL
> Server and Oracle seems to be different (such that they don't allow
> multiple nulls in a unique column, so it behaves in fact as a superkey
> if you assume that the null value is a value) although they have
> parameters that you can set to make them in that respect ANSI
> compliant

My habit has been to never treat a missing value as a value, regardless of what the documentation of my DBMS tells me. Received on Tue Jun 12 2007 - 03:56:05 CDT

Original text of this message