Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: constraints and primary keys

From: David Cressey <cressey73_at_verizon.net>
Date: Tue, 12 Jun 2007 08:56:05 GMT
Message-ID: <Fctbi.8002$Ar5.6790@trndny01>

"Jan Hidders" <hidders_at_gmail.com> wrote in message news:1181597535.638033.174610_at_q69g2000hsb.googlegroups.com...
> On 11 jun, 19:36, "David Cressey" <cresse..._at_verizon.net> wrote:
> > "Jan Hidders" <hidd..._at_gmail.com> wrote in message
> >
> > news:1181576005.557859.67320_at_q66g2000hsg.googlegroups.com...
> >
> > > 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

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US