Re: constraints and primary keys

"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 - 10:56:05 CEST