Re: Unique contraints vs. Unique indexes

"James Williams" <techsup_at_mindspring.com> wrote in message news:3b77bc45.622765_at_nntp.mindspring.com...
> On Fri, 10 Aug 2001 22:00:54 GMT, "Robert Fazio"
> <rfazio_at_home.com.nospam> wrote:
>
> >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.
> >
> >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?
> >
> >
>
> My point was I plan to create the tables and then create non_unique
> indexes over the column that will serve as the PK for each table. Then
> I would ALTER TABLE xxx ADD PRIMARY KEY and this should cause a PK
> where the uniqueness would be determined at commit.

I may be wrong (because I haven't checked), but the mere fact of altering the table to add a constraint afterwards does *not* ensure that non-unique indexes are built. The key is to make those constraints "deferrable" (initally whatever you want -it doesn't matter). You can do that at both create and alter time, and in either case you'll get non-unique indexes (and as I posted earlier, non-unique indexes are a bloody good idea). Similarly, without that keyword, regardless of when you add the constraint, you'll get unique indexes.

Regards
HJR The same would be
> the case with UNIQUE constraints.
>
> I am curious about issues others may experienced with this approach.
>
>
Received on Tue Aug 14 2001 - 05:21:02 CDT