Re: Unique contraints vs. Unique indexes

"Robert Fazio" <rfazio_at_home.com.nospam> wrote in message news:qQYc7.91105$EP6.22113665_at_news1.rdc2.pa.home.com...
> Do you really mean non_unique? A PK is a PK. You either want all of the
> fields at creation and you want them to be unique or you don't.
>

Sorry, Robert... but although the Primary Key constraint of course implies uniqueness, the *INDEX* that is built to enforce the constraint can quite happily be a non-unique index (in which case, it's the table that makes the index unque by default, because the table kicks out violating records).

All that is required to ensure a non-unique index is created is the keyword 'deferrable' when defining the constraint, even if the thing is created initially immediate.

There are very good reasons for deferring a constraint, Primary Keys amongst them.

Regards
HJR
> The only difference between a UK and a PK is that UK's don't have to have
> all fields supplied (i.e. Some can be null).
>
> --
> Robert Fazio
> Senior Technical Analyst
> dbabob_at_yahoo.com
>
> "James Williams" <techsup_at_mindspring.com> wrote in message
> news:3b744034.170930734_at_nntp.mindspring.com...
> > I am about to build a data warehouse that will eventually be 1 TB.
> >
> >
> > A PK's are going to have non_unique indexes and so that they can be
> > deferrable.
> >
> >
> > What are the heavy hitters opinions on this?
>
>
Received on Tue Aug 14 2001 - 05:15:36 CDT