Re: constraints and primary keys

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? :-P But, yes, thank you for that correction. I actually didn't know that this is the case in 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.

• Jan Hidders